Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Build dynamic sql statement and save result in variables

Posted on 2002-03-25
5
Medium Priority
?
1,799 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

670 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