T-SQL reflection on parameters

is it possible to use some kind of reflection on parameters in T-SQL module (function,procedure)?

procedure x(@foo nvarchar(max),@bar nvarchar(max)) ... 
set @foo = isnull(@foo,0); 
set @bar = isnull(@b开发者_如何学运维ar,0);

would it be possible to iterate over my parameters and set their values in a loop? Or do I need to use SQLCLR for this?

If you've so many parameters that you need to programatically enumerate them then you probably have too many parameters! Maybe switching to an alternative data structure such as a table valued parameter or an XML document would give you a cleaner way to get such complex data into your procedures/functions?

However, if you have some very special need for this then take a look at sys.parameters (assuming you're using SQL Server 2005 or later).

I don't know of any direct way, but you might find procedure

sp_executesql

useful, its like the exec function in most interpretted languages (run code from 'string'). Probably not the safest or most popular answer, but it could be powerful. What exactly are you trying to do? I can't really be too much more specific (or even sure this will help!) with what you've said so far.

Link