SQL 2008 R2 UDF with OpenQuery that uses parameters
Posted on 2014-08-28
MS SQL 2008 R2 Std.
Linked Server to AS400 Db2
I have a Stored Procedure that does what I need and would like the equivalent User Defined Function.
My objective is to return a single value from a table within the Linked Server using a “WHERE” clause containing two criteria based on variables passed into the call.
I’ve been all over the WEB looking for a solution and believe that there is none based on the constraints inherent with User Defined Functions BUT……..
I thought I’d ask here none the less !
Thanks in advance..!
Here’s the Stored Procedure syntax that I would like an equivalent User Defined Function for :
/****** Object: StoredProcedure [dbo].[sp_GetPartRev] Script Date: 08/28/2014 12:34:24 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
CREATE PROCEDURE [dbo].[sp_GetPartRev] @CoNo varchar(3)
,@PartRev char(1) OUTPUT
declare @Command Nvarchar(MAX);
declare @tick char(1);
set @tick = '''';
set @Command = N'SELECT @revOUT = IMPTRV FROM OpenQuery(IBMDA_SQL, '
+ 'SELECT PARTREV FROM LIB01.ITEMMSTR WHERE CoNo = '
+ ' AND ItemNumber = '
execute sp_executesql @Command, N'@revOUT char(1) OUTPUT', @revOUT = @PartRev OUTPUT;