Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why dynamic sql @sql is divided into different par

Author  Topic 

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-11 : 02:58:43
[code]

Create Procedure GenericTableSelect
@TableName VarChar(100)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT * FROM '
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

GO
[/code]

The above code is from the link - http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1

If 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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)
AS

Declare @SQL VarChar(1000)

SELECT @SQL = 'SELECT + @columnname + FROM + @TABLENAME'

Exec ( @SQL)

GO
Go to Top of Page

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 executing
SELECT + @columnname + FROM + @TABLENAME
To 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.
Go to Top of Page

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)
AS

Declare @SQL NVarChar(1000)

SELECT @SQL = 'SELECT '+ @columnname + ' FROM ' + QUOTENAME(@TableName)
-- print @SQL

Exec ( @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

Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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 2003
i think you meant sql 2005

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 like

IF @Debug=1
PRINT @SQL

While 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-13 : 11:52:19
@visakh

Do you mean like this?


Declare @SQL VarChar(max)
DECLARE @DEBUG BIT
set @DEBUG = 0

SELECT @SQL = 'SELECT ' + @columnname + ' from ' + @tablename

IF @debug = 1
print @SQL


Exec ( @SQL)

This doesn't show any sql strings I'm constructing. I'm not sure where I'm wrong.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-02-13 : 12:14:42
Declare @SQL VarChar(max)
DECLARE @DEBUG BIT
set @DEBUG = 1

SELECT @SQL = 'SELECT ' + @columnname + ' from ' + @tablename

IF @debug = 1
print @SQL


Exec ( @SQL)


As it is an SP make the debug flag an optional parameter

create Procedure GenericTableSelect
@TableName VarChar(100),
@columnname varchar(max) ,
@debug int = 0
AS

Declare @SQL NVarChar(1000)

SELECT @SQL = 'SELECT '+ @columnname + ' FROM ' + QUOTENAME(@TableName)
if @debug = 1
begin
@SQL
end
else
begin
Exec ( @SQL)
end
go

Then 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 in
http://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.
Go to Top of Page

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 BIT
set @DEBUG = 1

SELECT @SQL = 'SELECT ' + @columnname + ' from ' + @tablename

IF @debug = 1
print @SQL


Exec ( @SQL)


As it is an SP make the debug flag an optional parameter

create Procedure GenericTableSelect
@TableName VarChar(100),
@columnname varchar(max) ,
@debug int = 0
AS

Declare @SQL NVarChar(1000)

SELECT @SQL = 'SELECT '+ @columnname + ' FROM ' + QUOTENAME(@TableName)
if @debug = 1
begin
PRINT @SQL
end
else
begin
Exec ( @SQL)
end
go

Then 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 in
http://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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 2013-02-13 : 14:03:40
Thanks Visakh16. I got it now
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 14:32:57
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -