How can I create a user-defined function in SQL Server?

A. Basically you can't (at the moment) - these were supposed to have been in SQL 7.0 but will now only be in the next version (due in 2000).

There are a couple of options that will get part of the way there for now :-

1. You can write your own stored-proc or extended-stored-procedure (XP). However, you can only pass an SP/XP a set of parameters so to "apply" it to a set of rows you would have to cursor round the rows yourself and call the SP/XP 'n' times. An example below

Create StoredProcedure SPAdd
(@num1 int, @num2 int, @Result int OUTPUT)
Select @Result = @num1 + @num2

Declare @Total int
exec SPAdd @num1 = 5, @num2 = 6, @Result = @Total
Select @Total

2. As long as you can write the function in a simple 1 line TSQL statement then you can simulate the function with a case statement - look up CASE in the books online for more information.

select finalvalue = CASE
  WHEN <exp>
    THEN <exp>
  WHEN <exp>
    THEN <exp>
  ELSE <exp>

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.