Capture Returned Value From Exec(@Build) into another variable

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<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|||I am able to get this to return a value but I cant get this to work.
Any ideas?

IF @.Counter>1
Print('Yes')

Simon Hayes wrote:
> <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|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. All I need to do is set a
@.variable=@.Output Variable.
Thanks for your help Simon!
Phil|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!|||Never mind I figured it out. I just needed to set a variable =to
outputvariable that can be used in the rest of the code for the
conditional statement.
Thanks a bunch Simon!