Solved

Build dynamic sql statement and save result in variables

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

Technology Partners: 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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

630 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