Contents
Can we pass table name as parameter in function?
No you cannot pass a table name as variable to a User defined function as you will need dynamic sql to build the query and execute and this is something you cannot do inside a function, you will need to use a stored procedure for this ..
How to pass table name as input parameter in stored procedure?
Finally, using the sp_executesql command the dynamic SQL string is executed.
- SET ANSI_NULLS ON. GO.
- SET QUOTED_IDENTIFIER ON. GO.
- CREATE PROCEDURE Dynamic_SP. @Table_Name SYSNAME.
- AS. BEGIN.
- SET NOCOUNT ON; DECLARE @DynamicSQL NVARCHAR(4000)
- SET @DynamicSQL = N’SELECT * FROM ‘ + @Table_Name. EXECUTE sp_executesql @DynamicSQL.
- END.
Why does table name Treat table name as column name?
Msg 207, Level 16, State 1, Line 2 Invalid column name ‘NameOfSomeTableWhichHasIdAndRevisionColumn’. Why it treats the table name I pass as a column name? Are there any other ways to implement the behavior I want? Table-Valued Parameters (TVPs), and T-SQL in general, does not work like that.
Do you pass table variable or table name in T SQL?
Stick with the pure T-SQL option unless you need more flexibility, such as dynamically defining the queries, reading from temporary tables, etc. you have to pass table variable instead of a table name to get a result from this function. even if you want to provide table name you have to pass it as an NVARCHAR data type.
Can you pass table name into a TVF?
SQLCLR allows for Dynamic SQL in a TVF, so you can pass in the table name, concatenate it into the query (after you verify that it is, in fact, a table name!), and that’s pretty much it. This option allows for a little more flexibility than the pure T-SQL option, but is also a bit more complicated to implement.
Is the table name the same as the sub-query?
Each sub-query is the same except for the table name (well, in my example the second column name, which is also used in the ORDER BY clause, is different, but in your situation it will always be Revision ).