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 |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2010-04-09 : 11:26:46
|
Hello all,In a procedure, I am building a, quite literally, dynamic sql statement that will create a query, based on the inputs selected by the user. Or, I should say a stored procedure which will create a customizable sql statement, based on user input, and I happen to be using dynamic sql to accomplish the task. But I am running into a problem with the select clause.Please take a look at the code below:SET @S = 'SELECT ' + @SSize + '=COUNT(Savings) ' @S is a varchar which will be supplied to a dynamic sql statement. @SSize is an int.Now I run the procedure and I get this error:Syntax error converting the varchar value 'SELECT ' to a column of data type int.So, I changed the string to this:SET @S = 'SELECT ' + cast(@SSize as varchar(10)) + '=COUNT(Savings) ' And I added this to the end of the proc:SELECT @SSize I ran the proc again and nothing shows up. What I mean is I get no errors or warnings and the result is simply NULL. I would appreciate any help.Thank you.I believe I have to use sp_executesql but I would appreciate some assistance. Thanks again. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-09 : 11:58:40
|
| You should use sp_executeSql and pass the parameters to it.all your questions about dynamic sql can be answered here:http://www.sommarskog.se/dynamic_sql.htmlIt's everything you need to know in one handy resource.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
|
|
|
|
|
dynamic sql with variables in select clause