SQL Server 2008 stored procedure result as column default value

Question

First of all, thank you guys. You always know how to direct me when I can't even find the words to explain what the heck I'm trying to do.

The default values of the columns on a couple of my tables need to be equal the result of some complicated calculations on other columns in other tables. My first thought is to simply have the column default value equal the result of a stored procedure. I would also have one or more of the parameters pulled from the columns in the calling table.

I don't know the syntax of how to do it though, and any time the word "stored" and "procedure" land next to each other in google I'm flooded with info on Parameter default values and nothing relating to what I actually want.

Half of that was more of a vent than a question...any ideas though? And plz plz don't say "Well, you could use an On-Insert Trigger to..."

Answer 1

You can't have the default be the result of a stored procedure, it has to be a function. If you can convert the procedure into a function, then you can use that function. If you cannot, then you must use a trigger.

Answer 2

You would have to convert that stored procedure to a user-defined function. There are different types of UDF's - the one you're looking at would be the scalar UDF - returning a single value.

So for instance you could create a function like this:

CREATE FUNCTION dbo.YourDefaultFunction(@Input1 INT, @Input2 VARCHAR(10))
RETURNS VARCHAR(100)
AS BEGIN
   DECLARE @Result VARCHAR(100)

   SET @Result = @Input2 + ' - giving: ' + CAST(@Input1 AS VARCHAR(5))

   RETURN @Result
END

Of course, yours would be a lot more complicated :-)

Once you've done that, you can define this to be the default for a column of type VARCHAR(100) - either directly when declaring the table, or later on via an ALTER TABLE statement:

CREATE TABLE dbo.YourTable(.......
   SomeColumn VARCHAR(100)    
      CONSTRAINT DF_YourTable_SomeColumn 
      DEFAULT (dbo.YourDefaultFunction(417, 'Test')),
   .....)

or :

ALTER TABLE dbo.YourTable
  ADD CONSTRAINT DF_YourTable_SomeColumn 
  DEFAULT (dbo.YourDefaultFunction(4711, 'Test2')) 
  FOR SomeColumn

Unfortunately, you cannot pass other columns as parameters to your function when defining it as a default value for a column.

Does that help??