Geek Answers Handbook

T-SQL reflection by parameters

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

procedure x(@foo nvarchar(max),@bar nvarchar(max)) ... set @foo = isnull(@foo,0); set @bar = isnull(@bar,0);

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

+4
source share
2 answers

If you have so many parameters that you need to list programmatically, you probably have too many parameters! Maybe switching to an alternative data structure, such as a table parameter or an XML document, will give you a cleaner way to get such complex data in your procedures / functions?

However, if you have any special need for this, take a look at sys.parameters (assuming you are using SQL Server 2005 or later).

+5
source

I don’t know any direct way, but you can find the procedure

 sp_executesql

useful as the exec function in most interpreted languages ​​(run-time code from a string). This is probably not the safest or most popular answer, but it can be powerful. What exactly are you trying to do? I cannot be more specific (or even sure that this will help!) With what you have said so far.

http://blogs.msdn.com/b/pnayak/archive/2007/05/03/how-to-use-parameter-in-tsql-to-actually-use-it-as-identifier-rather-than- the-value.aspx

0
source

More articles:


All Articles