By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

<ph******@msn.com> wrote in message
news:11**********************@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
Jul 23 '05 #2

P: n/a
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:
<ph******@msn.com> wrote in message
news:11**********************@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


Jul 23 '05 #3

P: n/a
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!

Jul 23 '05 #4

P: n/a
Never mind I figured it out. All I need to do is set a
@variable=@Output Variable.
Thanks for your help Simon!
Phil

Jul 23 '05 #5

P: n/a
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!

Jul 23 '05 #6

P: n/a
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!

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.