CODEWITHSUNDEEP
sql-server-2005user-defined-functionsdefaults
B.M
B.M

Reputation: 563

Managing Defaults in Stored Procedures' Parameters

I am working in a DB which has about 10 DEFAULT constraints scattered by two or three different tables, and I need to write a stored procedure which receives as parameters values for all these attributes. However, if no values are received, I want these parameters to assume the default values in the corresponding table. Knowing that there is no easy way to do it, I thought about writing custom UDFs returning the default values, adding the constraints as DEFAULT (dbo.SomeFunction()) FOR [Attribute] and adding = dbo.SomeFunction() as the default value for each parameter. I would, however, like to ask what is the best way to accomplish this:

1) Write an UDF for every attribute, simplifying execution but ending up with 10 different functions; or

2) Write a single dbo.GetDefault(TableName NVARCHAR(32), AttributeName NVARCHAR(32) and use IF ... ELSE ... or SWITCH on the different table/attribute pairs.

What do you think?

Upvotes: 0

Views: 64

Answers (1)

gbn
gbn

Reputation: 432561

You should have one udf per default.

  • datatypes won't be the same
  • as your code evolves you'll end up with dependencies in computed columns or schemabound views: it you need another it'll be messy to change

Upvotes: 1

Related Questions