How to assign results of an EXEC to a variable ?

[saustin]

Hi,
Have a query which works perfectly and now need to assign it to a variable. How do you do that ? Am getting errors, is it not allowed with openquery ?

For example I need to store the results of the exec below to a variable.

Thanks, Steve

Declare @query varchar(8000)
Declare @pID varchar(20)
Declare @pLINE varchar(5)
Declare @oDESIREDRCVDATE varchar(10)
Declare @pSTATUSX varchar(1)
Declare @pSTATUSC varchar(1)

Set @pID='NYDELLTEST'
Set @pLINE='1'
Set @oDESIREDRCVDATE='2006-03-25 00:00:00.000'
Set @pSTATUSX='X'
Set @pSTATUSC='C'

set @query = 'UPDATE OPENQUERY(VMFG, ''SELECT DESIRED_RECV_DATE from PURC_ORDER_LINE WHERE PURC_ORDER_ID=''''' + @pID + ''''' AND LINE_NO=''''' + @pLINE + ''''''')'
set @query = @query + 'SET DESIRED_RECV_DATE = '''+@oDESIREDRCVDATE + ''''

exec(@query)

 

[FlashMerlot]

Sorry, I do not believe EXECute can be used to return a value. There are limits to what dynamically compiled SQL queries can do.

The only solution I might suggest is a UDF (User Defined Function) Start by taking the inner action and make a UDF function like this.
======================================
CREATE FUNCTION dbo.get_DESIRED_RECV_DATE(
@pID varchar(20),
@pLINE varchar(5))
RETURNS varchar(10)
AS
BEGIN
DECLARE @ReturnVal varchar(10)
SELECT @ReturnVal = DESIRED_RECV_DATE
from PURC_ORDER_LINE
WHERE
PURC_ORDER_ID=@pID AND LINE_NO=@pLINE
RETURN @ReturnVal
END
======================================
the above creates a UDF.

... and that's as far as I can help you because I have no idea what "VMFG" refers to in your query????

 

[saustin]

Hi Flashmerlot,
Many thanks for your reply. Have done some research on this question and the trick is to use sp_executesql instead of exec. Here is the example....

declare @anumber money
declare @anumber1 money

set @query = 'SELECT * FROM OPENQUERY(VMFG, ''SELECT NVL(Sum(a.DEBIT_AMOUNT - a.CREDIT_AMOUNT),0 )
FROM ACCOUNT_BALANCE a WHERE a.ACCT_PERIOD = ''''' + @aPERIOD1 + ''''' AND a.ACCT_YEAR = ''''' + @acyear + ''''' AND a.ACCOUNT_ID=''''' + @gCODEw + ''''''')'

exec sp_executesql @query, N'@anumber money OUTPUT', @anumber OUT

SELECT @anumber

Also, VMFG was the data source name used in the openquery.