Solved

Build dynamic sql statement and save result in variables

Posted on 2002-03-25
5
1,782 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 69

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 69

Accepted Solution

by:
Scott Pletcher earned 50 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 69

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

829 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