results of a dynamic query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

results of a dynamic query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-04 : 09:51:31

Antonio writes "Hi,
I have been able to build a dynamic query and execute it using
the stored procedure sp_executesql. My query only returns a value which is the number of records found.

However, I would like to put this value into a variable so that I can apply some logic. Anybody knows how to do it?

This is part of the code:

DECLARE @SQLString NVARCHAR(500)
DECLARE @tableName varchar(25)

SET @tableName= 'EMPLOYEE'

/* Set column list. CHAR(13) is a carriage return, line feed.*/
--SET @SQLString = N'SELECT first_name' + CHAR(13)
SET @SQLString = N'SELECT COUNT (*)' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
--SET @SQLString = @SQLString + N'FROM hays_employee' + CHAR(13)
SET @SQLString = @SQLString + N'FROM ' + @tableName + CHAR(13)

/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE first_name LIKE ''Anto%'''

EXEC sp_executesql @SQLString"

nrafiq
Starting Member

9 Posts

Posted - 2002-02-04 : 10:01:37

Hi friend,

I tryed like this:

Begin
DECLARE @SQLString NVARCHAR(500)
DECLARE @tableName varchar(25)
Declare @TotalRows Int
SET @tableName= 'Authors'

Set @TotalRows = 0
/* Set column list. CHAR(13) is a carriage return, line feed.*/
SET @SQLString = N'SELECT @TotalRows = COUNT (*)' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
SET @SQLString = @SQLString + N'FROM ' + @tableName + CHAR(13)

EXEC sp_executesql @SQLString
Select @TotalRows
End

But SQL-Server raised a error message like "Must declare the variable '@TotalRows'."

Why this error comes?

Reason
-----
The sp_executesql batch cannot reference variables declared in the batch calling sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch calling sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

Anybody will be give a better answer. Please wait.

Rafi

Nazim
A custom title

1408 Posts

Posted - 2002-02-04 : 10:18:57

if you are using Sql 2000.

you can use temp table and insert the value

eg:
insert into #t(cnt) exec @sqlString

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

nrafiq
Starting Member

9 Posts

Posted - 2002-02-04 : 10:25:27

Hi Nazim,

I want only variable oriented process.Using temp table it is possible.If you know like this.please send me.

Rafi

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-04 : 12:22:21

You can use Output parameters with sp_executesql. I learned this from this site, but I can't find the article/Post right now so I went ahead and changed your code:

DECLARE @SQLString NVARCHAR(500)
DECLARE @tableName varchar(25)
Declare @Var1 INT

SET @tableName= 'EMPLOYEE'

/* Set column list. CHAR(13) is a carriage return, line feed.*/
--SET @SQLString = N'SELECT first_name' + CHAR(13)
SET @SQLString = N'SELECT @Var1 = COUNT (*)' + CHAR(13)

/* Set FROM clause with carriage return, line feed. */
--SET @SQLString = @SQLString + N'FROM hays_employee' + CHAR(13)
SET @SQLString = @SQLString + N'FROM ' + @tableName + CHAR(13)

/* Set WHERE clause. */
SET @SQLString = @SQLString + N'WHERE first_name LIKE ''Anto%'''

EXEC sp_executesql @SQLString,N'@Var1 INT OUTPUT',@Var1 Out

SELECT @Var1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources