Swamp_Thing
asked on
SQL 2008 R2 UDF with OpenQuery that uses parameters
Environment:
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 :
USE [MyDb]
GO
/****** Object: StoredProcedure [dbo].[sp_GetPartRev] Script Date: 08/28/2014 12:34:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================== ========== =========
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- ========================== ========== =========
CREATE PROCEDURE [dbo].[sp_GetPartRev] @CoNo varchar(3)
,@ItemNumber varchar(15)
,@PartRev char(1) OUTPUT
AS
BEGIN
declare @Command Nvarchar(MAX);
declare @tick char(1);
set @tick = '''';
set @Command = N'SELECT @revOUT = IMPTRV FROM OpenQuery(IBMDA_SQL, '
+ @tick
+ 'SELECT PARTREV FROM LIB01.ITEMMSTR WHERE CoNo = '
+ @CoNo
+ ' AND ItemNumber = '
+ @tick
+ @tick
+ @ItemNumber
+ @tick
+ @tick
+ @tick
+ ')';
execute sp_executesql @Command, N'@revOUT char(1) OUTPUT', @revOUT = @PartRev OUTPUT;
END
GO
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 :
USE [MyDb]
GO
/****** Object: StoredProcedure [dbo].[sp_GetPartRev] Script Date: 08/28/2014 12:34:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- ==========================
CREATE PROCEDURE [dbo].[sp_GetPartRev] @CoNo varchar(3)
,@ItemNumber varchar(15)
,@PartRev char(1) OUTPUT
AS
BEGIN
declare @Command Nvarchar(MAX);
declare @tick char(1);
set @tick = '''';
set @Command = N'SELECT @revOUT = IMPTRV FROM OpenQuery(IBMDA_SQL, '
+ @tick
+ 'SELECT PARTREV FROM LIB01.ITEMMSTR WHERE CoNo = '
+ @CoNo
+ ' AND ItemNumber = '
+ @tick
+ @tick
+ @ItemNumber
+ @tick
+ @tick
+ @tick
+ ')';
execute sp_executesql @Command, N'@revOUT char(1) OUTPUT', @revOUT = @PartRev OUTPUT;
END
GO
You shouldn't use a UDF for this anyway. If you do, you will turn anything you use it in into a loop because it has to execute for each value.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.