Skip to content

OpenQuery with input & output parameter

July 27, 2010
tags: ,
--OpenQuery with input (@Partnumber) & output (@Itemgroupdescription) parameter
DECLARE @Param nvarchar(50);
DECLARE @SQL nvarchar(MAX);
	SET @SQL='SELECT g.T$DSCA FROM Baan.ttiitm001201 i LEFT JOIN Baan.ttcmcs023201 g
						ON i.T$CITG=g.T$CITG WHERE i.T$ITEM='''+@PartNumber+'''';
SET @SQL='SELECT @ItemGroupDescription=T$DSCA FROM OpenQuery(Oracle1,'''+REPLACE(@SQL, '''', '''''') + ''')';
SET @Param = '@ItemGroupDescription varchar(50) OUTPUT';
	PRINT @SQL;
	EXEC sp_executesql @SQL,@Param,@ItemGroupDescription OUTPUT;

--With Stored procedure in Oracle side
DECLARE @result varchar(255)
EXEC ('BEGIN Vision.SELECT_PRO_CURRENT_OP(?,?); END;',714322,@result OUTPUT) at Oracle1;
SELECT @result;
Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: