Menu
I have a UDF that queries data out of a table. The table, however, needs to be definable as a parameter. For example I can't have:
Select * From [dbo].[TableA]
I need something like:
Select * From [dbo].[@TableName]
The above line doesn't work, and also the UDF prohibits me from setting the query as a string and calling exec(). I can do this in a procedure, but I can't call the procedure from the UDF either.
Does anyone know how I can accomplish this within the UDF without having some kind of massive switch statement?
334
Table variable is a type of local variable that used to store data temporarily, similar to the temp table in SQL Server. Tempdb database is used to store table variables. To declare a table variable, start the DECLARE statement. The name of table variable must start with at(@) sign.
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
You can't use a variable in an IN clause - you need to use dynamic SQL, or use a function (TSQL or CLR) to convert the list of values into a table.
SET @SQL = 'SELECT * FROM ' + @table
EXEC (@SQL) -- parentheses are required
This can't be done, dynamic SQL is not supported in functions.
See this SO question: Executing dynamic SQL in a SQLServer 2005 function
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
