Solved

Build dynamic sql statement and save result in variables

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to reinstall SCVMM 2012 R2 - SQL errors. 5 36
SQL convert varchar to INT 17 31
Find unused columns in a table 12 68
Need age at date of document 5 14
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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