Dynaminic SQL help 1

[Mdavis123]

We have a rather lengthly quartly report that requires 85 queries aggregating demographics.

I built a dynaminic query where the variables change for the quarter but I can't get the assignment @QtrCnt to work.
Any Ideas?? Thanks in advance. This code is shortened to focus on the problem.

Declare @Str varchar(1000),@QtrCnt int

Set @Str='Select @QtrCnt=Count(*) from Fd_clients'
Exec (@Str)

Generates this error:
Must declare the variable '@QtrCnt'.

 

[mkal]

Try this
Set @Str = ('Select @QtrCnt = Count(*) from Fd_clients')

 

[sunila7]

Try this

Declare @SQL nvarchar(1000)
declare @QtrCnt int

SELECT @SQL = 'Select @QtrCnt=Count(*) from authors'


EXECUTE sp_executesql @SQL, N'@QtrCnt INT OUTPUT', @QtrCnt OUTPUT

print @qtrcnt

Sunil

 

[psprague]

The problem is that the @QtrCnt is out of context in the dynamic sql.

Dynamic sql is best suited for non resultset queries but you can get scalar and resultsets back by inserting them into tables (regular tables and temp tables, table variables do not work)


DECLARE @Str VARCHAR(1000)

CREATE TABLE #t(i INT)

Set @Str = ('Select Count(*) from sysobjects')

INSERT INTO #t exec(@str)

SELECT * FROM #t

DROP TABLE #t

 

[Mdavis123]

Sunila7,

Great reply, works like a champ.

For others following the thread, I have used this in the past for dynaminic cursors

declare @SQLTmpCsr nvarchar(5000)
Set SQLTmpCsr ='Select or Lots of dynamnic code here like insert into and select into'

exec ('Declare tmpCsr cursor for '+@SQLTmpCsr

Open tmpCsr
...
Mike