locked
Stored Procedure using output variable to run a query made with dynamic SQL RRS feed

  • Question

  • I'm trying to create understand the output parameter syntax of SQL Server but I find it very confusing, and reviewing some of the examples that I've seen online haven't done a whole lot to ameliorate that situation, so instead of banging my head against the wall I've decided to ask the experts.  

    The dynamic SQL in my query does a variable number of joins on the same table to see if the number of person IDs that have code a, b, c and d etc.  Because the number of joins is based on a variable I built the string using dynamic SQL. Eventually I would like to have each of the where conditions variable too, but that's a little much for the moment. 

    I would like to create a stored procedure that runs the query that it produced by the dynamic SQL.  I've tested the dynamic part and it works, but what I can't get it the query to run.  In playing around I made the stored procedure print out the query, but not run it.  Am I anywhere in the ballpark?


    alter proc GetMultipleComorbidQuery
    (
     @sqlquery nvarchar(1000) output
    )
    as

    declare 
    @x int
    ,@y int,
    @sql nvarchar(1000) 

    select 
     @x=1
    ,@y=5
    ,@sql =
    'select distinct x.patid
    from
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like ''123%''
    ) as  x'+CAST(@x as CHAR(1))
    while @x < @y
    begin;
    set @sql=@sql+ 

    inner join 
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like ''456%''
    ) x'+CAST(@x+1 as CHAR(1))+' on x'+CAST(@x+1 as CHAR(1))+'.patid=x1.patid'
    set @x=@x+1
    end;
    set @sql = @sqlquery 
    print @sql
    go
    exec sp_executesql GetMultipleComorbidQuery @sqlquery out

    The error that I get is: 

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near '@sqlquery'.

    I got frustrated at the end and started trying everything if syntax looks like it's nowhere in the same universe
    Tuesday, December 4, 2012 8:14 PM

Answers

  • You need few changes:

    1. Change

    set @sql = @sqlquery

    to

    set @sqlquery  = @sql

    Secondly, in the call you need

    declare @sqlquery nvarchar(1000)

    set @sqlquery = ''

    execute dbo.GetMultipleComorbidQuery @sqlquery output

    select @sqlquery


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, December 4, 2012 8:42 PM
    Tuesday, December 4, 2012 8:38 PM
  • >> The dynamic SQL in my query does a variable number of joins on the same table to see if the number of person IDs that have code a, b, c and d etc. Because the number of joins is based on a variable I built the string using dynamic SQL. <<

    Dynamic SQL is like cannibalism. You do not it unless there is no other choice. We also hate local variables and flow control logic. SQL is a compiled declarative language. You are treating it as a interpreted procedural language. 

    You did not bother to follow ISO-11179 rules, minimal Netiquette or post DDL or specs. Now we have to guess at everything. I am going to guess that you want a list of patient ids who have multiple diseases in a list of International Classification of Diseases code families. This is a relational division; it was one of Dr. Codd's eight original operators 

    CREATE TABLE ICD_clm
    (patient_id INTEGER NOT NULL,
     icd CHAR(10) NOT NULL,
     PRIMARY KEY (patient_id, icd));

    INSERT INTO ICD_clm
    VALUES (1, '123.000'), (1, '234.000'), (1, '456.123'), 
           (2, '999.100'), (2, '234.011'), (2, '789.000'), 
           (3, '123.000'), (3, '234.000'), (3, '456.999');

    CREATE PROCEDURE Get_Really_Sick_Patients 
    (@d1 CHAR(10) = NULL, @d2 CHAR(10) = NULL, 
     @d3 CHAR(10) = NULL, @d4 CHAR(10) = NULL, 
     @d5 CHAR(10) = NULL)
    AS BEGIN
    WITH Patient_Diagnosis (patient_id, icd)
    AS (SELECT patient_id, icd FROM ICD_clm),

    Disease_List (icd_pattern)
    AS
    (SELECT X.icd_pattern 
      FROM (VALUES (@d1), (@d2), (@d3), (@d4), (@d5))
            AS X(icd_pattern)
     WHERE X.icd_pattern IS NOT NULL)

    SELECT DISTINCT patient_id 
     FROM Patient_Diagnosis AS PD1
     WHERE NOT EXISTS 
           (SELECT *
              FROM Disease_List
             WHERE NOT EXISTS 
                  (SELECT *
                     FROM Patient_Diagnosis AS PD2
                    WHERE PD1.patient_id = PD2.patient_id
                      AND PD2.icd LIKE Disease_List.icd_pattern));
    END; 

    This can be extned to 2K diseases if you really need it. When you call it, use the underscore wild card. I have no idea why % does not work

    EXEC Get_Really_Sick_Patients '123_______', '234_______', '456_______'
    This gives {1,3}
    EXEC Get_Really_Sick_Patients '123_______', '234_______', '456.999'
    This return {3} 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Wednesday, December 5, 2012 12:42 AM
    • Marked as answer by sqlservernoob Wednesday, December 5, 2012 1:47 PM
    Wednesday, December 5, 2012 12:37 AM

All replies

  • "GO" is a batch separator.  Your stored procedure ends at that line.  Therefore, the next line - "exec sp_executesql ..." is a different batch that has no local variables.  Therefore, @sqlquery is an undefined variable.  In addition, you are attempting to use that procedure incorrectly.  What is the first argument to sp_executesql?  A string.  What do you try to use?  Hint - it isn't a string in the tsql sense.
    • Proposed as answer by Naomi N Tuesday, December 4, 2012 8:33 PM
    • Marked as answer by sqlservernoob Thursday, December 13, 2012 6:29 PM
    • Unmarked as answer by sqlservernoob Thursday, December 13, 2012 6:29 PM
    Tuesday, December 4, 2012 8:32 PM
  • You need few changes:

    1. Change

    set @sql = @sqlquery

    to

    set @sqlquery  = @sql

    Secondly, in the call you need

    declare @sqlquery nvarchar(1000)

    set @sqlquery = ''

    execute dbo.GetMultipleComorbidQuery @sqlquery output

    select @sqlquery


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, December 4, 2012 8:42 PM
    Tuesday, December 4, 2012 8:38 PM
  • Excuse me, my noob is showing.  So I took to the books and got

     exec sp_executesql N'GetMultipleComorbidQuery','@sqlquery nvarchar(2000)',@sqlquery (as well as removed the go)

    as a more reasonable attempt at calling the stored procedure.  Now she tells me 

    'exec sp_executesql N'GetMultipleComorbidQuery','@sqlquery nvarchar(2000)',@sqlquery'

    The stored procedure creates successfully, if that means anything. 

    Tuesday, December 4, 2012 8:39 PM
  • Something is wrong here.  It is unusual for a stored procedure to attempt to execute itself in a recursive manner.  Is that your goal?  If not, then I don't really understand what you are trying to do.  It seems that you are building a query in @sql.  The first thing to do is verify that your query is syntactically correct - which is what the print statement is used for.  Once that looks correct, what do you intend to do with it?  Simply return it as the output argument from the procedure?  Do you want to execute it within the procedure - and if so, do what with the output? 
    Tuesday, December 4, 2012 9:09 PM
  • Back to the drawing board!

    Write the query you need in static SQL and make sure that it runs correctly. Once you have done that, you can ask yourself whether you really should deal with dynamic SQL. Maybe you find that you don't need it at that point.

    I was trying to understand what your generated code would be, but it's not entirely simple.

    But from what you say, it seems that you need this query:

    select ic.patid
    from icdClm as ic
    where (ic.icd like '123%' OR
           ic.icd LIKE '456%' OR
           ...)

    If you don't know the number of codes to test for, put these in a table-valued parameter:

    SELECT ic.patid
    FROM   icdClm AS ic
    WHERE  EXISTS (SELECT *
                   FROM   @codes c
                   WHERE  ic.icd LIKE c.code + '%')

    There are cases when dynamic SQL is called for, but you should always ask twice before you go there, because the stakes raises steeply.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Tuesday, December 4, 2012 11:09 PM
    Tuesday, December 4, 2012 11:04 PM
  • >> The dynamic SQL in my query does a variable number of joins on the same table to see if the number of person IDs that have code a, b, c and d etc. Because the number of joins is based on a variable I built the string using dynamic SQL. <<

    Dynamic SQL is like cannibalism. You do not it unless there is no other choice. We also hate local variables and flow control logic. SQL is a compiled declarative language. You are treating it as a interpreted procedural language. 

    You did not bother to follow ISO-11179 rules, minimal Netiquette or post DDL or specs. Now we have to guess at everything. I am going to guess that you want a list of patient ids who have multiple diseases in a list of International Classification of Diseases code families. This is a relational division; it was one of Dr. Codd's eight original operators 

    CREATE TABLE ICD_clm
    (patient_id INTEGER NOT NULL,
     icd CHAR(10) NOT NULL,
     PRIMARY KEY (patient_id, icd));

    INSERT INTO ICD_clm
    VALUES (1, '123.000'), (1, '234.000'), (1, '456.123'), 
           (2, '999.100'), (2, '234.011'), (2, '789.000'), 
           (3, '123.000'), (3, '234.000'), (3, '456.999');

    CREATE PROCEDURE Get_Really_Sick_Patients 
    (@d1 CHAR(10) = NULL, @d2 CHAR(10) = NULL, 
     @d3 CHAR(10) = NULL, @d4 CHAR(10) = NULL, 
     @d5 CHAR(10) = NULL)
    AS BEGIN
    WITH Patient_Diagnosis (patient_id, icd)
    AS (SELECT patient_id, icd FROM ICD_clm),

    Disease_List (icd_pattern)
    AS
    (SELECT X.icd_pattern 
      FROM (VALUES (@d1), (@d2), (@d3), (@d4), (@d5))
            AS X(icd_pattern)
     WHERE X.icd_pattern IS NOT NULL)

    SELECT DISTINCT patient_id 
     FROM Patient_Diagnosis AS PD1
     WHERE NOT EXISTS 
           (SELECT *
              FROM Disease_List
             WHERE NOT EXISTS 
                  (SELECT *
                     FROM Patient_Diagnosis AS PD2
                    WHERE PD1.patient_id = PD2.patient_id
                      AND PD2.icd LIKE Disease_List.icd_pattern));
    END; 

    This can be extned to 2K diseases if you really need it. When you call it, use the underscore wild card. I have no idea why % does not work

    EXEC Get_Really_Sick_Patients '123_______', '234_______', '456_______'
    This gives {1,3}
    EXEC Get_Really_Sick_Patients '123_______', '234_______', '456.999'
    This return {3} 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Naomi N Wednesday, December 5, 2012 12:42 AM
    • Marked as answer by sqlservernoob Wednesday, December 5, 2012 1:47 PM
    Wednesday, December 5, 2012 12:37 AM
  • I'll be sure to post a sample table next time to include data types and try to be more explicit with what I intend the query to do.  I've just started learning SQL and I'm sure my nooby methods aren't always the best way to do things, but I'm trying my best and I come to forums like this to get schooled :).   Thanks for the lesson
    Wednesday, December 5, 2012 2:00 PM
  • The dynamic query generated is:

    select distinct x.patid
    from
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like '123%'
    ) as  x1 
    inner join 
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like '456%'
    ) x2 on x2.patid=x1.patid 
    inner join 
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like '456%'
    ) x3 on x3.patid=x1.patid 
    inner join 
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like '456%'
    ) x4 on x4.patid=x1.patid 
    inner join 
    (
    select ic.patid
    from icdClm as ic
    where ic.icd like '456%'
    ) x5 on x5.patid=x1.patid

    (notice how the alias for the derived table changes every time)

    Eventually I wanted the number of table joins to be variable and based upon input from the user.  I then wanted all of the WHERE clauses in the derived tables to be parameters in a stored procedure, but I figured that was too much to ask with the rudimentary code that I had.  I'm not a programmer by trade or school so learning everything and trying to apply it yourself in your own situations is difficult and that's why I come to forums like these: to get berated on my bad practices.  It's hard to teach yourself everything without the input of someone who knows what they're doing so, like I said, I come to forums and get blasted on this or that to help learn what's best practice/most extensible. It's a marathon, for sure, but I'm slowly but surely getting there.

    Wednesday, December 5, 2012 2:28 PM
  • I think you're looking into the relational division problem as Celko figured out. Try to google this term and you may also find my blog post on a similar problem useful

    How to search for all words inclusive without using Full Text search


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, December 5, 2012 4:02 PM
  • Hello,

             Following example show @Cnt & @YearMonth are output variable in store procedure . We are used result of this two variable in other query.

    ALTER procedure [dbo].[sp_UpdLoanData3]
    ( 
    @_DealName varchar(4000),
    
    @_yearmo int
    )
    as 
    begin
    DECLARE @Cnt int
    declare @temptbl table ([deal Name] varchar(50), [Trustee Loan Id] varchar(50),[collateral Balance] varchar(50),[year mo] int)
    
    
    --Split , Separated Value & Insert Into table @temptable
    declare @temptable table (DealName varchar(20),YMonth int)
    Declare @Xml AS XML 
    SET @Xml = cast(('<A>'+replace(@_DealName,',','</A><A>')+'</A>') AS XML)
    INSERT INTO @temptable SELECT A.value('.', 'varchar(max)') as [Column],@_yearmo FROM @Xml.nodes('A') AS FN(A) 
    --End Of Separated Value
    
    DECLARE @DealName Varchar(20)
    DECLARE @YearMonth Varchar(6)
    DECLARE CursorName CURSOR FAST_FORWARD FOR SELECT DealName,YMonth FROM @temptable
    OPEN CursorName
    FETCH NEXT FROM CursorName INTO @DealName, @YearMonth
    WHILE @@FETCH_STATUS = 0
    BEGIN
    --sp_UpdLoanData3 '''BACM 2000-1'',''BACM 2007-3''',201210
    declare @sql nvarchar(max)
    select @sql = N'SELECT  @Cnt=Count([YEAR mo]) FROM UpdateLoanData WHERE [Year Mo] = ''' + @YearMonth + ''' AND [Deal Name] = ' + @DealName + ''
    exec sp_executesql @sql, N'@Cnt sysname OUTPUT', @Cnt OUTPUT
    if(@Cnt=0)
    
    BEGIN 
    select @sql = N'SELECT  @YearMonth=MAX([YEAR mo])  FROM UpdateLoanData WHERE [Deal Name] = ' + @DealName + ''
    exec sp_executesql @sql, N'@YearMonth sysname OUTPUT', @YearMonth OUTPUT
    insert into @temptbl
    EXEC(N'select [deal Name], [Trustee Loan Id],[collateral Balance],[year mo] from [Shobhit].[dbo].[UpdateLoanData] where [Deal Name] = ' + @DealName + ' and [Year Mo] = ' +
    @YearMonth + '')
    END
    else
    BEGIN
    insert into @temptbl
    EXEC(N'select [deal Name], [Trustee Loan Id],[collateral Balance],[year mo] from [Shobhit].[dbo].[UpdateLoanData] where [Deal Name] = ' + @DealName + ' and [Year Mo] = ' + @YearMonth + '')
    END
    FETCH NEXT FROM CursorName
    INTO @DealName,@YearMonth
    END
    CLOSE CursorName
    DEALLOCATE CursorName
    SELECT * FROM @temptbl
    
    end




    Wednesday, December 26, 2012 12:13 PM