Dynamic SQL - SQL Server Forums

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)

Dynamic SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-23 : 08:16:16

rn writes "Hello,
i Saw the articles on Dynamic Sql;i have to include variable table name & field name & also collect the output of query in a variable (it returns only single value)
i did using cursor & got the output but as they say cursor slows down the performance ,is there another way of doing it?
for eg :
--just testing procedure
declare @vSQL varchar(1000), @strCol varchar(25), @strTab varchar(25),@strVal varchar(25)
select @strCol='EmpID'
select @strTab='Employee_Master'
--@strVal is where i need to collect the output
select @vSQL = 'select ' + @strVal + '=' + @strCol + ' from ' + @strTab + ' where ' + @strCol + '= ''H6'' '
print @vSql
Execute (@vSQL)
print @strVal

it doesn't work,what should i do?"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-12-23 : 08:28:50

you are not assigning a value to @strval, which is causing the problem I think, with your specific statement - with @strval being null, the exec does not execute, since you can't assign the value that way.

Will think on how it should be done ... posting now to correct an error I made by reading too fast :-)

Hmm - got my self lost here :

so far, if you take out the " + @strval + '=' " section, then the query should execute fine, return a sql output. Embarrassingly, I am struggling to get that into your variable ... mind is mush atm
*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-12-23 : 08:45:35

Hmm ... problem with your version is that the @strval should be a literal - that is why it fails (since @strval is null).

This would generate your SQL statement :

select @vSQL = 'select @strVal = ' + @strCol + ' from ' + @strTab ' where ' + @strCol + '= 6 '

However, since declaration for @strval is outside the exec, when you exec the sqlstatement, it fails for the @strval no being declared.

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-12-23 : 08:46:35

declare @vSQL nvarchar(1000), @strCol varchar(25), @strTab varchar(25),@strVal varchar(25)
select @strCol='EmpID'
select @strTab='Employee_Master'
select @vSQL = 'select @strVal = ' + @strCol + ' from ' + @strTab + ' where ' + @strCol + '= ''H6'' '
print @vSql
Execute sp_executesql @vSQL, N'@vSQL varchar(1000) out', @vSQL out
print @strVal

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-12-23 : 09:09:18

Thanx Nr ... got the @strval bit, but totally confused myself with the execute ...
*sigh* ... so much still to learn

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources