SEO

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!