Returning results from dynamic query 2

[johnscott76]

Hopefully this is a simple question and I'm just not seeing the answer sitting in front of my face...

I'm trying to return the results of a dynamic query to the batch, and so far I've had trouble.

Here's an excerpt:

Declare @QueryPending nvarchar(4000)

Set @QueryPending = 
 N' Select *
      From STop_' + @dbDataStream + '.dbo.AGTXREF
      Where CUST# = ' + Convert(varchar(8), @CUST#) + '
        And AGTSTS = ''C''
        And AGSSTS = ''NA''
 '

-- This next line is what I'd like to accomplish, in some form or another
If Exists ( Execute sp_executesql @QueryPending ) Set @Status = 'P'

Any ideas?

 

[SQLSister]

Insert the results into a temp table (define it first, don;t use select into) and then select from the temp table in the If Exists.

Questions about posting. See faq183-874

 

[SQLDenis]

Use an output parameter, example in pubs db

USE pubs
GO


DECLARE @tbl    nvarchar(50),
        @sql    nvarchar(4000),
        @params nvarchar(4000),
        @count  int
SELECT @tbl =N'authors'

   SELECT @sql =
   N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) 
    SELECT @params =  N'@cnt      int      OUTPUT'

   EXEC sp_executesql @sql, @params, @cnt = @count OUTPUT

SELECT @count

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

Other way:

SET NOCOUNT ON
Declare @QueryPending nvarchar(4000)

Set @QueryPending =
 N' Select SmallestFieldInTheTable
      From STop_' + @dbDataStream + '.dbo.AGTXREF
      Where CUST# = ' + Convert(varchar(8), @CUST#) + '
        And AGTSTS = ''C''
        And AGSSTS = ''NA''
 '
DECLARE @temp TABLE (SmallestFieldInTheTable TypeOfThatField)
INSERT INTO @Temp
EXEC (@test)
IF @@ROWCOUNT <> 0
   Set @Status = 'P'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[johnscott76]

Thanks to both, I like the solution SQLDenis came up with and I'm going to go that route, but SQLSister's proposal was a good idea as well.

Thanks again!

 

[rac2]

Why not

Execute sp_executesql @QueryPending
If @@ROWCOUNT > 0  Set @Status = 'P'

 

[SQLDenis]

yep you can do that for this query however if you do a count result will always be 1


exec works also for this purpose

example

Execute sp_executesql N'select 1 union all select 2'
select  @@ROWCOUNT 


exec ('select 1 union all select 2')
select  @@ROWCOUNT

but this won't work

use pubs

Execute sp_executesql N'select count(*) from authors'
select  @@ROWCOUNT 

Execute sp_executesql N'select count(*) from authors where 1 =2'
select  @@ROWCOUNT

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[bborissov]

The side effect of direct using of the query is that you will get a resultset. But if you want only to check if the record exists I thing that is not the way In the original post the desire was:

IF EXISTS(....)
   do something

(I prefer Denis's solution)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP