Link to home
Start Free TrialLog in
Avatar of Swamp_Thing
Swamp_ThingFlag for United States of America

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
Avatar of Randy Knight, MCM
Randy Knight, MCM
Flag of United States of America image

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
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial