Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Build dynamic sql statement and save result in variables

Posted on 2002-03-25
5
Medium Priority
?
1,804 Views
Last Modified: 2008-02-26
Hi,

I need to build a dynamic sql statement and return the
result into a variable.
I'm able to build the sql statement, but I don't know how
to keep the result into a variable...
Example :
-------------------------------------------------
DECLARE @SQLString NVARCHAR(500)
DECLARE @iCount        INT
 
SELECT @SQLString = N'SELECT count(Make) FROM ' + @TableName  

EXEC sp_executesql @SQLString
-------------------------------------------------

It returns me the good information, but how can I keep this information into my variable @iCount ?? :-(

Than you.

Marie
0
Comment
Question by:msl22
  • 3
  • 2
5 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 6894811
Change your sql string as follows:
SELECT @SQLString = N'SELECT @iCount = COUNT(make) FROM ' + @TableName  

And the sp_executesql as follows:
EXEC sp_executesql @SQLString,N'@iCount INT OUTPUT',@icount OUTPUT
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 6894818
By the way, you can use multiple parameters as either input or output in sp_executesql.  For example:

SELECT @SQLString = N'SELECT @iCount = COUNT(make) FROM @TableName'

EXEC sp_executesql @SQLString,N'@iCount INT OUTPUT, @tablename VARCHAR(...)', @icount OUTPUT, @TableName

You just list the variables in order in parameter 2 and then input them in order in parameters 3, 4, etc..

0
 

Author Comment

by:msl22
ID: 6894835
Hi Scott,

I have a little question about your solution...
If my select statement retreive more than on result... what
would happen ?

Thank you.

Marie.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 6894875
In SQL 2000, I presume you could use a table variable.  In SQL 7.0, you would not be able to return multiple values, you would have to create a global temp table or use some other method.
0
 

Author Comment

by:msl22
ID: 6894881
Thank you very munch for your your help Scott !

Have a nice day !

;)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question