<philipdm@msn.com> wrote in message
news:1107271847.396749.170840@z14g2000cwz.googlegr oups.com...
>I am building a SQL statement that returns a number.
> when I execute the Built SQL statment EXEC(@Build). What I need to do
> now is take that number that comes back and store it in another
> variable so I can do some conditional logic. Any ideas? See SQL below.
>
> Something like @Count=Exec(@Build) which I know doesnt work.
>
> Thanks,
> Phil
>
>
>
>
> DECLARE @PullDate varchar(12)
>
> SET @PullDate=''+CAST(DATEPART(mm,getdate()-31) AS varchar(2))
> +'/'+CAST(DATEPART(dd,getdate()-31)AS varchar(2))
> +'/'+CAST(DATEPART(yyyy,getdate()-31) AS varchar(4))+''
>
> PRINT(@PullDate)
>
> DECLARE @COUNTER BIGINT
>
> DECLARE @SELECT VARCHAR(500)
> DECLARE @SELECT2 VARCHAR(1000)
> DECLARE @BUILD VARCHAR(5000)
>
>
> SET @SELECT='
>
> SELECT COUNTER FROM
> OPENQUERY(PROD,'
>
> SET @SELECT2='''
> SELECT
> COUNT(WMB.COLLECTOR_RESULTS.ACCT_NUM) AS COUNTER
> FROM
> COLLECTOR_RESULTS,
> WHERE
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE =
> to_date('''''+@PullDate+''''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCOUNT.END_DATE ) =
> to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACCT_SYS_DATA.END_DATE =
> to_date('''''+@PullDate+''''',''''mm/dd/yyyy'''')
> )
> GROUP BY
> WMB.COLLECTOR_RESULTS.ACTIVITY_DATE '')'
>
>
> SET @BUILD=@SELECT+@SELECT2
> PRINT(@BUILD)
> EXEC(@BUILD)
>
> --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @BUILD STORED
> INTO @COUNTER so I can do a conditional statement.)
> if @COUNTER>=1
> begin
> print('yes')
> end
>

Instead of EXEC(), you can use sp_executesql with an output parameter:

declare @sql ntext, @counter int
set @sql = 'select @counter = counter from openquery(...)'
exec sp_executesql @sql, N'@counter int', @counter OUTPUT
select @counter

See here for an example:

http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Simon