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