TSQL Dynamic Variable Substitution

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

SQL Server 2000 Forums

SQL Server Development (2000)

TSQL Dynamic Variable Substitution

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-11 : 07:17:19

Tom writes "I have a SQL variable name stored within a column or other variable and I want to retrieve the value into a string. I will be using this code to build a dynamic SQL string

Heres is what I have came up with
______________________________________________
DECLARE @wherevalue int
SET @wherevalue = '1'

DECLARE @sqlvariable varchar(100), @SQL varchar(1000)
SET @sqlvariable = '@wherevalue'
SELECT @SQL = 'SELECT '+@sqlvariable
PRINT @SQL
______________________________________________
This print
SELECT @wherevalue

It should be
SELECT '1'

This will be plugged code
SET @vari='tablename.fieldname.keyfield.@keyfieldvariable'
that makes the SQL string
SELECT fieldname FROM tablename WHERE keyfield = @keyfieldvariable
@keyfieldvariable is @wherevalue

I would like to do this within a Stored Procedure since this is going to be used in batches of 100 or more statments and my ASP application which will be calling it would be ineffecient to make 100 or more SQL queryies instead of 1 Stored Procedure call

Software
Windows 2000 SP4
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Thanks for any help
Tom Thompson
tom@tsdp.com"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-11 : 08:05:39

quote:

I would like to do this within a Stored Procedure since this is going to be used in batches of 100 or more statments and my ASP application which will be calling it would be ineffecient to make 100 or more SQL queryies instead of 1 Stored Procedure call

you are better of just doing the SELECT's without a stored procedure. There is no point in a stored proc that replaces:

exec storedproc, @somefield, @sometable, @otherfield, @somevalue

with

select @somefield from @sometable where @otherfield = @somevalue

because:

a) the point of a stored proc is to hide the physical layer from the logical. there is clearly not being done

and

b) the stored proc will need dynamic SQL, which causes permission and peformance problem. Just because you are using a stored proc doesn't autmatically make it more faster than doing select's ... probably will be slower in this case, because arguments need to be passed, the string needs to be built, and then the SQL needs to be executed, and then values need to be returned back -- all instead of just doing a SELECT.

- Jeff

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-07-11 : 10:52:11

Declare @myval int
set @myval = 5
DECLARE @sqlvariable varchar(100), @SQL varchar(1000)
SET @SQL = 'DECLARE @wherevalue int
SET @wherevalue = ' + cast( @myval as varchar(20) ) + ' Select @wherevalue'
execute ( @SQL)

Try this
Ganesh.V
Net Asset Management
vganesh76@rediffmail.com

Enjoy working

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 10:58:46

quote:

probably will be slower in this case, because arguments need to be passed, the string needs to be built, and then the SQL needs to be executed, and then values need to be returned back -- all instead of just doing a SELECT.

Jeff, I thought a sproc was more effecient because the sql is compiled, and doesn't have to any extra work to determine the best path to the data....

Isn't that akin to dynamic sql?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-11 : 17:11:41

dynamic SQL = dynamic SQL. not compiled.

Doesn't matter if you execute it from within a proc or not.

Why have a proc do this when you can just do it yourself with a SELECT statement?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources