How to swap in a parameter for a specific VARCHAR value being used for a linked server with openquery

Question

When I execute this query(it's a scalar function) I get the result I want. It returns a shipping number.

select ShipmentNo from openquery([SERVER_IP], 'SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo = ''G015246''')

However, when I swap out G015246 with the parameter @PO:

select @ShipmentLabels = ShipmentNo from openquery([SERVER_IP], 'SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo = ''@PO''')

For some reason the return value becomes null with this change.

My return statement for both is:

RETURN ISNULL(@ShipmentLabels, @PO)

where @ShipmentLabels is the desired return value. Since it is null it returns @PO instead, which ends up being G015246 which seems right.

Anyone notice anything wrong with how I've added the @PO parameter to the sql statement?

Stored Procedure that calls the Function(fnGetTheShippingLabels): I guess the best way to describe this SP is the starting point.

ALTER PROCEDURE [dbo].[StartingSP]
    @JobId INT = 1

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ErrorMsg VARCHAR(256)


    SELECT  OC.PoNo,
          V.FirstName,
          V.LastName,
          dbo.fnGetTradeAppFacilityName(NULL,OC.PoNo) TradeAppFacilityName,
          OC.ReportFileName, dbo.fnGetTheShippingLabels(NULL,OC.PoNo) ShippingLabels

    FROM     PoHdr P
         INNER JOIN OrderConfirmation OC ON
            OC.PoNo = P.PoNo
         INNER JOIN CpVendor V ON 
            V.VendNo = P.VendNo 
    WHERE OC.JobId = @JobId

    SELECT @ErrorMsg ErrorMsg

END

As you can see from above, the function is in the SELECT statement of the SP.

Complete Scalar Function:

ALTER FUNCTION [dbo].[fnGetTheShippingLabels]
(
    @PoHdrRecId INT,
    @PoNo VARCHAR(20)
)
RETURNS VARCHAR(220)
AS
BEGIN
 DECLARE @PO VARCHAR(20)= @PoNo
 DECLARE @ShipmentLabels VARCHAR(220)

    select @ShipmentLabels = ShipmentNo from openquery([SERVER_IP], 'SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo = ''G015246''')
--FROM dbo.PoHdr WHERE PoNo = @PoNo

    -- Return the result of the functionRETURN ISNULL(@ShipmentLabels, @PO)

END

Trying a different way of writing query now, this doesn't work fyi:

 Declare @TSQL VarChar(8000)

    set @TSQL = N'select @ShipmentLabels = ShipmentNo from openquery([SERVER_IP],SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo =' + @PO +''
    exec sp_executesql @TSQL, N'@ShipmentLabels output', @ShipmentLabels = @ShipmentLabels out

I've never tried this--but have you tried it without the quotes? `@CpPoNo = '@PO''` SQL Server is reading your parameter as s a string literal (VARCHAR) the way you have it, i'm pretty sure. — S3S, Jul 19 '16 at 20:03
I may not doing this right, but `@CpPoNo = '@PO''` and `@CpPoNo = @PO'` will not execute. The latter gives error returned message ..."Deferred prepare could not be completed.". Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@PO". — user1869407, Jul 19 '16 at 20:10
I just noticed this Is in your SP. you just need to Concat with +. `@CpPoNo = ' + @PO` — S3S, Jul 19 '16 at 20:14
@scsimon My apologies for not understanding you, but do you mean like `@CpPoNo = ' + @PO) ` because that doesn't seem to work. — user1869407, Jul 19 '16 at 20:18
I guess OPENQUERY doesn't ACCEPT variables. http://stackoverflow.com/questions/3378496/including-parameters-in-openquery — S3S, Jul 19 '16 at 20:23
Thanks for the article. Let me see if I can pass whole query string — user1869407, Jul 19 '16 at 20:25
No sweat. Was just trying to figure it out with you. I've never tried that before. Be sure to post an answer if you get it working! — S3S, Jul 19 '16 at 20:33
@scsimon I'm struggling here. I think I have to redo the entire way I've implemented openquery. — user1869407, Jul 19 '16 at 20:34
@scsimon I just realized that you can't used `EXEC` in a function lol — user1869407, Jul 19 '16 at 20:55
May want to change that proc to a table value function or try dynamic sql with sp_executesql — S3S, Jul 19 '16 at 21:01
@scsimon This is ridiculously complicated. Using a linked server on a scalar function using OpenQuery AND passing a parameter seems like something not designed for. — user1869407, Jul 20 '16 at 18:28
Can anyone help me find a similar example. I am struggling here. — user1869407, Jul 20 '16 at 18:30
I'd open a new question since you have narrowed down that you need a work around for PARAMETERS in OPENQUERY, via a SP. A new question will get more eyes on it. — S3S, Jul 20 '16 at 18:32
@scsimon Thank you! Makes sense. I appreciate your assistance getting to this point. — user1869407, Jul 20 '16 at 18:35

Surendra · Answer 1 · 2016-07-26T19:34:59.533

I see that it can be done in two ways

Option 1 Creating a dynamic query and Passing the output parameter

Declare @TSQL VarChar(8000)
DECLARE @SQ VARCHAR(4) = ''''
DECLARE @paramDef VARCHAR(100)

set @TSQL = N'select @ShipmentLabels = ShipmentNo from openquery([SERVER_IP],SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo =' + @SQ + @PO + @SQ + ')'
SET @paramDef = N'@ShipmentLabels VARCHAR(1000) OUTPUT'
exec sp_executesql @TSQL, @paramDef, @ShipmentLabels = @ShipmentLabels OUTPUT

Option 2 Creating a dynamic query, getting the value into a temp table and the read the value from temporary table.

Declare @TSQL VarChar(8000)
DECLARE @SQ VARCHAR(4) = ''''
DECLARE @ShipmentNo VARCHAR(1000)
set @TSQL = N'select ShipmentNo from openquery([SERVER_IP],SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo =' + @SQ + @PO + @SQ + ')'

CREATE TABLE #T 
(
    temp VARCHAR(max)
)
INSERT INTO #T
EXEC(@TSQL)

SELECT @ShipmentNo = temp from #t
SELECT @ShipmentNo
drop table #t

The complete solution with your stored procedure will look like, I have written it with a cursor and all, it can still be improved for performance, but with our limited on your DB design this is best solution we can provide for you.

ALTER PROCEDURE [dbo].[StartingSP]
    @JobId INT = 1

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ErrorMsg VARCHAR(256)
    DECLARE @PoNO VARCHAR(1000)

    Declare @TSQL VarChar(8000)
    DECLARE @SQ VARCHAR(4) = ''''
    DECLARE @ShipmentNo VARCHAR(1000)
    set @TSQL = N'select ShipmentNo,' + @SQ + @PO + @SQ +' as PoNo from openquery([SERVER_IP],SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo =' + @SQ + @PO + @SQ + ')'

    CREATE TABLE #T1 
    (
        temp VARCHAR(max)
    )
    INSERT INTO #T1
    EXEC(@TSQL)

    SELECT  OC.PoNo,
          V.FirstName,
          V.LastName,
          dbo.fnGetTradeAppFacilityName(NULL,OC.PoNo) TradeAppFacilityName,
          OC.ReportFileName
          ,'' as 
          --, dbo.fnGetTheShippingLabels(NULL,OC.PoNo) ShippingLabels
    INTO #T
    FROM     PoHdr P
         INNER JOIN OrderConfirmation OC ON
            OC.PoNo = P.PoNo
         INNER JOIN CpVendor V ON 
            V.VendNo = P.VendNo 
    WHERE OC.JobId = @JobId



    Declare @TSQL VarChar(8000)
    DECLARE @SQ VARCHAR(4) = ''''
    DECLARE @ShipmentNo VARCHAR(1000)
    CREATE TABLE #T1 
    (
        PoNo VARCHAR(1000),ShipmentNo VARCHAR(max)
    )

    DECLARE vend_cursor CURSOR  
        FOR SELECT PoNO FROM #T
    OPEN vend_cursor  

    FETCH NEXT FROM vendor_cursor   
    INTO @PoNO

    WHILE @@FETCH_STATUS = 0  
    BEGIN 

        set @TSQL = N'select ShipmentNo,' + @SQ + @PO + @SQ +' as PoNo from openquery([SERVER_IP],SET FMTONLY OFF EXEC GsvStaging.dbo.Paul_GetPoShippingLabels @CartId = NULL, @CpPoNo =' + @SQ + @PO + @SQ + ')'

        INSERT INTO #T1
        EXEC(@TSQL)     

        FETCH NEXT FROM vendor_cursor   
        INTO @PoNO
    END
    CLOSE vendor_cursor   
    DEALLOCATE vendor_cursor   

    UPDATE T
    SET  T.ShippingLabels = T1.ShipmentNo
    FROM #T  T
    JOIN #T1 T1
    ON T.PoNo = T1.PoNo

    SELECT @ErrorMsg ErrorMsg

END

I get the following error: Only functions and some extended stored procedures can be executed from within a function. System.Data.SqlClient.SqlException (0x80131904): Only functions and some extended stored procedures can be executed from within a function. Using dynamic sql. — user1869407, Jul 22 '16 at 15:04
Neither of the options is working. The error for option 2 is "Invalid use of a side-effecting operator 'SELECT' within a function" — user1869407, Jul 22 '16 at 20:18
You cannot use a dynamic sql within a function, all you can do is to create this as stored procedure and execute it. — Surendra, Jul 22 '16 at 20:58
Updating code above to show you how my SP works in how it calls that function — user1869407, Jul 25 '16 at 16:18
I am a bit confused where to put the dynamic query code. In the original SP that calls the function? Do I do away with the scalar function altogether? — user1869407, Jul 25 '16 at 16:23

How to swap in a parameter for a specific VARCHAR value being used for a linked server with openquery

1 Answers1