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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-22 : 08:35:48
|
| Doe writes "We are trying to build a stored procedure and we need to use a dynamically built select statement. All the documentation that we find doesn't tell us how to return the value of that select statement into a variable that we can use in the rest of the stored procedure.For example: set @sSQL = N'select count(*) as '+@sColumnName+N' from TempAuthorTable t left join Author a on t.AuthorID=a.AuthorID where t.'+@sColumnName+N'=a.'+@sColumnName+N' and t.AuthorID = '+convert(varchar(10),@iAuthorID)how can I catch the count and use it later in the procedure?If we hard code the column names instead of using the variable @sColumnName it works, but the column names of the table may change so we don't want to hard code the column names. We also tried creating another stored procedure to execute and return the value, however that did not work, either.Any help would be very appreciatedThanks" |
|
|
rksingh024
Yak Posting Veteran
56 Posts |
Posted - 2002-07-22 : 08:41:09
|
| Look for sp_executesql in BOL.Ramesh |
 |
|
|
sterobhun
Starting Member
12 Posts |
Posted - 2002-07-22 : 12:48:18
|
You could try something like this:exec sp_executesql N'select count(*) rc into ##t from stock'declare @rowcount intset @rowcount = (select rc from ##t)drop table ##t That is, put the count(*) results into another table and put that into the variable instead. |
|
|
|
|
|
|
Catching a value from a dynamic SQL statement