CODEWITHSUNDEEP
sql-server
Paul S Chapman
Paul S Chapman

Reputation: 832

Retrieving a table of results from query sent to linked server and returned by a stored procedure

I have a linked server which contains data. Calling each table separately takes too long so the only choice is to use OpenQuery. The wrinkle being that I need to pass a parameter to the query.

OpenQuery does not support parameters meaning it has to be done via Exec and a string built up with the full command in it. I also need to client application written in C#.

I could build the string in C# and pass that but I am trying to make the application agnostic when it comes to the data source so want to avoid having SQL embedded in the application.

Code to the stored procedure is 

ALTER PROCEDURE [dbo].[Get_Patient_By_MRN] ( @MRN VarChar(10) ) 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @Patient_Sql  VarChar(768) = 
'
SELECT * FROM OPENQUERY([CERNER_APP_DB_PROD],'' 
Select  P.PERSON_ID
        , PA1.ALIAS AS MRN
        , PA2.ALIAS AS NHS_Number
        , P.NAME_LAST_KEY
        , P.NAME_FIRST_KEY
        , P.NAME_FULL_FORMATTED
        , P.BIRTH_DT_TM
From   V500.PERSON P    LEFT JOIN V500.PERSON_ALIAS PA1 
                        ON      PA1.PERSON_ID = P.PERSON_ID                     AND 
                                PA1.PERSON_ALIAS_TYPE_CD = 10
                        LEFT JOIN V500.PERSON_ALIAS PA2
                        ON      PA2.PERSON_ID = P.PERSON_ID                     AND 
                                PA2.PERSON_ALIAS_TYPE_CD = 18

Where  PA1.ALIAS = ''''' + @MRN + '''''
'') 
'

Return Exec @Patient_Sql

END

Upvotes: 0

Views: 371

Answers (1)

Thom A
Thom A

Reputation: 95689

You were close with your SQL. You don't RETURN a dataset with a Stored procedure. RETURN is normally used to return the success result of an SP; 0 means succes, anything else means it didn't (this is how the Microsoft SP's work). What you need to do here is simply execute your dynamic statement.

I've changed the SQL a little bit, and stopped injection (although very hard with a varchar(10)) by using QUOTENAME, and changed the datatype to be correct for sp_executesql:

ALTER PROCEDURE [dbo].[Get_Patient_By_MRN] (@MRN varchar(10))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Patient_Sql nvarchar(MAX) = '
SELECT * FROM OPENQUERY([CERNER_APP_DB_PROD],'' 
Select  P.PERSON_ID
        , PA1.ALIAS AS MRN
        , PA2.ALIAS AS NHS_Number
        , P.NAME_LAST_KEY
        , P.NAME_FIRST_KEY
        , P.NAME_FULL_FORMATTED
        , P.BIRTH_DT_TM
From   V500.PERSON P    LEFT JOIN V500.PERSON_ALIAS PA1 
                        ON      PA1.PERSON_ID = P.PERSON_ID                     AND 
                                PA1.PERSON_ALIAS_TYPE_CD = 10
                        LEFT JOIN V500.PERSON_ALIAS PA2
                        ON      PA2.PERSON_ID = P.PERSON_ID                     AND 
                                PA2.PERSON_ALIAS_TYPE_CD = 18

Where  PA1.ALIAS = ' + QUOTENAME(@MRN,'''') + ');';

    EXEC sp_executesql @Patient_Sql;

END;

Upvotes: 1

Related Questions