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

[philipdm@msn.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(@Bu ild) which I know doesnt work.

Thanks,
Phil




DECLARE @PullDate varchar(12)

SET @PullDate=''+CA ST(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.COLLE CTOR_RESULTS.AC CT_NUM) AS COUNTER
FROM
COLLECTOR_RESUL TS,
WHERE
WMB.COLLECTOR_R ESULTS.ACTIVITY _DATE =
to_date('''''+@ PullDate+''''', ''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACC OUNT.END_DATE ) =
to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
AND WMB.COLLECT_ACC T_SYS_DATA.END_ DATE =
to_date('''''+@ PullDate+''''', ''''mm/dd/yyyy'''')
)
GROUP BY
WMB.COLLECTOR_R ESULTS.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

[Simon Hayes]

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


<philipdm@msn.c om> wrote in message
news:1107271847 .396749.170840@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>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(@Bu ild) which I know doesnt work.
>
> Thanks,
> Phil
>
>
>
>
> DECLARE @PullDate varchar(12)
>
> SET @PullDate=''+CA ST(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.COLLE CTOR_RESULTS.AC CT_NUM) AS COUNTER
> FROM
> COLLECTOR_RESUL TS,
> WHERE
> WMB.COLLECTOR_R ESULTS.ACTIVITY _DATE =
> to_date('''''+@ PullDate+''''', ''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACC OUNT.END_DATE ) =
> to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> AND WMB.COLLECT_ACC T_SYS_DATA.END_ DATE =
> to_date('''''+@ PullDate+''''', ''''mm/dd/yyyy'''')
> )
> GROUP BY
> WMB.COLLECTOR_R ESULTS.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
>[/color]

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

[philipdm@msn.com]

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

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:[color=blue]
> <philipdm@msn.c om> wrote in message
> news:1107271847 .396749.170840@ z14g2000cwz.goo glegroups.com.. .[color=green]
> >I am building a SQL statement that returns a number.
> > when I execute the Built SQL statment EXEC(@Build). What I need to[/color][/color]
do[color=blue][color=green]
> > 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[/color][/color]
below.[color=blue][color=green]
> >
> > Something like @Count=Exec(@Bu ild) which I know doesnt work.
> >
> > Thanks,
> > Phil
> >
> >
> >
> >
> > DECLARE @PullDate varchar(12)
> >
> > SET @PullDate=''+CA ST(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.COLLE CTOR_RESULTS.AC CT_NUM) AS COUNTER
> > FROM
> > COLLECTOR_RESUL TS,
> > WHERE
> > WMB.COLLECTOR_R ESULTS.ACTIVITY _DATE =
> > to_date('''''+@ PullDate+''''', ''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACC OUNT.END_DATE ) =
> > to_date(''''12/31/9999'''',''''mm/dd/yyyy'''')
> > AND WMB.COLLECT_ACC T_SYS_DATA.END_ DATE =
> > to_date('''''+@ PullDate+''''', ''''mm/dd/yyyy'''')
> > )
> > GROUP BY
> > WMB.COLLECTOR_R ESULTS.ACTIVITY _DATE '')'
> >
> >
> > SET @BUILD=@SELECT+ @SELECT2
> > PRINT(@BUILD)
> > EXEC(@BUILD)
> >
> > --THIS IS WHERE IM UNSURE I NEED THE COUNT RETURNED FROM @BUILD[/color][/color]
STORED[color=blue][color=green]
> > INTO @COUNTER so I can do a conditional statement.)
> > if @COUNTER>=1
> > begin
> > print('yes')
> > end
> >[/color]
>
> Instead of EXEC(), you can use sp_executesql with an output[/color]
parameter:[color=blue]
>
> 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[/color]

[philipdm@msn.com]

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

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!

[philipdm@msn.com]

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

Never mind I figured it out. All I need to do is set a
@variable=@Outp ut Variable.
Thanks for your help Simon!
Phil

[philipdm@msn.com]

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

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!

[philipdm@msn.com]

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

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!