Author |
Topic |
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-02-11 : 02:58:43
|
[code]Create Procedure GenericTableSelect @TableName VarChar(100)ASDeclare @SQL VarChar(1000)SELECT @SQL = 'SELECT * FROM ' SELECT @SQL = @SQL + @TableNameExec ( @SQL)GO[/code]The above code is from the link - http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1If I change the below parts extracted from the above code to SELECT @SQL = 'SELECT * FROM + @TABLENAME' , still gets the correct result.[code]SELECT @SQL = 'SELECT * FROM ' SELECT @SQL = @SQL + @TableName[/code] I wonder then why it is split into two parts in the original code. And I have seen such things are done in many other examples of dynamic sql. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 03:16:45
|
just for clarity. though this illustration was simple in actual case you might have much larger and completed query in SELECT part. In that case it makes sense to keep it separate in a variable to check intermediate results in case there are mutiple concatenations involved based on parameters etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-02-11 : 12:09:49
|
Thanks Visakh16. Can you tell me why the code below doesn't work?create Procedure GenericTableSelect @TableName VarChar(100),@columnname varchar(max)ASDeclare @SQL VarChar(1000)SELECT @SQL = 'SELECT + @columnname + FROM + @TABLENAME'Exec ( @SQL)GO |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-02-11 : 12:19:03
|
Because the string is executed as a batch.You are actually executingSELECT + @columnname + FROM + @TABLENAMETo go back to your original question - the reason for concatenating into a single string is so that you can replace the exec with a select and see what you are executing - if you dod select @sql with your example you will see why it doesn't work.SELECT @SQL = 'SELECT [' + @columnname + '] FROM [' + @TABLENAME + ']'You don't need the [] if you never have spaces or such in your identifiers but it's safer to allow for it (means you can't pass in a schema with the tablename though).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-11 : 12:20:23
|
Change it to this:create Procedure GenericTableSelect @TableName VarChar(100),@columnname varchar(max)ASDeclare @SQL NVarChar(1000)SELECT @SQL = 'SELECT '+ @columnname + ' FROM ' + QUOTENAME(@TableName)-- print @SQLExec ( @SQL)GO You can uncomment the print statement to see the SQL string you are constructing. If you were to copy and paste that SQL string into a query window and execute it, it should execute without error. Run it with the print statements with and without the changes I suggested and you will see the difference.I also added a QUOTENAME function to the table name. This allows table names that have special characters or reserved words to work correctly. I would have liked to do the same for @columnname, but I suspect you may be thinking of sending more than one column name as a comma-separated string in the @columname parameter. If that is the case, you cannot do QUOTENAME on @columnname |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-02-11 : 14:08:56
|
Thanks to all of you@James..print @SQL doesn't work in my case. Maybe because mine is sql server 2003?Even after I uncomment it, I don't see the strings I'm constructing. However, it works with "select @sql" given by nigelrivett |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-11 : 14:33:26
|
If you are using the Results To Grid Option (which you probably are), the results of the select statements are shown in one tab (Results tab) and the results of the Print statements are shown on another tab (Messages tab). So if you look in the Messages tab in the results pane, the result of the print statement should be there. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-11 : 22:46:52
|
quote: Originally posted by learning_grsql Thanks to all of you@James..print @SQL doesn't work in my case. Maybe because mine is sql server 2003?Even after I uncomment it, I don't see the strings I'm constructing. However, it works with "select @sql" given by nigelrivett
there's no sql 2003i think you meant sql 2005------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-02-12 : 00:01:57
|
Thanks.@James..I got it now. It was under message tab. @visakh16..sorry ..it is sql server 2005 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-12 : 00:48:23
|
quote: Originally posted by learning_grsql Thanks.@James..I got it now. It was under message tab. @visakh16..sorry ..it is sql server 2005
make sure you remove those debug statements before you realize the code.my preferred way of doing this is in procedures where we use dynamic sql we create an additional parameter called @Debug of bit type with default 0. Inside code we add statements to print intermediate queries by giving an IF condition likeIF @Debug=1PRINT @SQLWhile debugging we will set bit as 1 which will display the intermediate queries it creates on the fly. Copy and paste this onto new query window in SSMS and you'll be able to compile and execute it to see if query created is correct.In actual execution ignore the parameter @Debug and it will assume default value of 0 and will not display any of those intermediate results------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-02-12 : 05:38:51
|
Before you start, make sure to read this post www.sommarskog.se/dynamic_sql.htmlMadhivananFailing to plan is Planning to fail |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-02-13 : 11:52:19
|
@visakhDo you mean like this?Declare @SQL VarChar(max)DECLARE @DEBUG BITset @DEBUG = 0SELECT @SQL = 'SELECT ' + @columnname + ' from ' + @tablenameIF @debug = 1print @SQLExec ( @SQL) This doesn't show any sql strings I'm constructing. I'm not sure where I'm wrong. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-02-13 : 12:14:42
|
Declare @SQL VarChar(max)DECLARE @DEBUG BITset @DEBUG = 1SELECT @SQL = 'SELECT ' + @columnname + ' from ' + @tablenameIF @debug = 1print @SQLExec ( @SQL)As it is an SP make the debug flag an optional parametercreate Procedure GenericTableSelect @TableName VarChar(100),@columnname varchar(max) ,@debug int = 0ASDeclare @SQL NVarChar(1000)SELECT @SQL = 'SELECT '+ @columnname + ' FROM ' + QUOTENAME(@TableName)if @debug = 1begin@SQLendelsebeginExec ( @SQL)endgoThen you can set the flag = 1 on a call if you wish to just view the string that will be executed without executing it.You can use another value if you want to display and execute.I create a table called trace to log info from everything that is not run too often.I this situation I would log the string before the call and the completion to record what had happened and how long it took.As inhttp://www.nigelrivett.net/Products/DWBuilder/TraceTable.html==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 12:46:18
|
quote: Originally posted by nigelrivett Declare @SQL VarChar(max)DECLARE @DEBUG BITset @DEBUG = 1SELECT @SQL = 'SELECT ' + @columnname + ' from ' + @tablenameIF @debug = 1print @SQLExec ( @SQL)As it is an SP make the debug flag an optional parametercreate Procedure GenericTableSelect @TableName VarChar(100),@columnname varchar(max) ,@debug int = 0ASDeclare @SQL NVarChar(1000)SELECT @SQL = 'SELECT '+ @columnname + ' FROM ' + QUOTENAME(@TableName)if @debug = 1begin PRINT @SQLendelsebeginExec ( @SQL)endgoThen you can set the flag = 1 on a call if you wish to just view the string that will be executed without executing it.You can use another value if you want to display and execute.I create a table called trace to log info from everything that is not run too often.I this situation I would log the string before the call and the completion to record what had happened and how long it took.As inhttp://www.nigelrivett.net/Products/DWBuilder/TraceTable.html==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
learning_grsql
Posting Yak Master
230 Posts |
Posted - 2013-02-13 : 14:03:40
|
Thanks Visakh16. I got it now |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 14:32:57
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|