Return an output value from Openquery

[stephenk1973]

Quite new to SQLServer and am trying to get a value back from our Oracle server.How do i pull back a value from OpenQuery. currently my code looks like....

ALTER PROCEDURE uspWChannelStk3
(@OrdRef nvarchar(6),@QTY1 int OUTPUT)
AS
Declare @someSql nvarchar(1000)
Declare @someSql2 nvarchar(1000)

set @someSQL ='SELECT OrdRef,
NVL(QTY1,0) AS W1,
FROM PRO.TMP_GARL_STK
WHERE SEASON='''''+ @Ordref +'''''
AND C_CODE=''''W''''
ORDER BY SEASON'

set @someSql2 = 'SELECT
@QTY1=W1
FROM OPENQUERY(SGK,'''+ @someSQL +''' )'
RETURN
EXEC (@someSQL2)


Declare @QTY1 int
EXEC uspWChannelStk3 'Ab1234',@QTY1 OUTPUT
SELECT @QTY1

All i get back are nulls

Thanks

Stephen

 

[NoCoolHandle]

Stephen,

The RETURN statemnt tells sql to send back a 0 and to stop processing the batch. Also I dont't think you need to worry about the paramter you are trying to set.

THerefore nothing will be done after defining the query..

Try

ALTER PROCEDURE uspWChannelStk3
        (@OrdRef nvarchar(6),@QTY1 int OUTPUT) 
        AS 
Declare @someSql nvarchar(1000)
Declare @someSql2 nvarchar(1000)

set @someSQL ='SELECT OrdRef,
    NVL(QTY1,0) AS W1,
    FROM PRO.TMP_GARL_STK 
WHERE SEASON='''+ @Ordref +''' 
    AND C_CODE=''W''
ORDER BY SEASON'

SELECT    
        @QTY1=W1
        FROM OPENQUERY(SGK, @someSQL)

EXEC uspWChannelStk3 'Ab1234',@QTY1 OUTPUT
SELECT     @QTY1

I think the concept is sound.


HTH

Rob

 

[NoCoolHandle]

Also..
FROM PRO.TMP_GARL_STK
WHERE SEASON='''+ @Ordref +'''

I am guessing that is saying you have an oracle server setup called pro. the issue i am seeing now is that you seem to be missing the Owner argument before your object (table) name.

It should look more like
FROM PRO[red].SomeOwner.[/red]TMP_GARL_STK
WHERE SEASON='''+ @Ordref +'''

 

[stephenk1973]

PRO is actually the oracle table owner, it's not really needed. @someSQL is just a Oracle SQL friendly bit of code.

When i hardcoded the string i build into "SELECT @QTY1=W1
FROM OPENQUERY(SGK, @someSQL)" it would work, let it populate dynamically and always a MSDAORA a column error.

Thanks