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