Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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
0
Swamp_Thing
Asked:
Swamp_Thing
1 Solution
 
Randy Knight, MCMPrincipal ConsultantCommented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
You cant use dynamic sql within UDF
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now