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.html
It's everything you need to know in one handy resource.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
|
|
|
dynamic sql with variables in select clause