Run a query from a stored procedure's OUTPUT parameter

Question

The following script is intended to take a dynamically-produced SQL statement and then run it. The script will run, but it doesn't return the results that are intended, but the text of the dynamic SQL string. The string produced is the string that I need to run, but it's called 32 times (strange number, I don't know why this would be the case). Does anyone see why this isn't just returning a result set of the dynamically-produced SQL statement? SQL Server 2008 R2

For the record, SQL Server interpreted the query as 32 different select statements, each on their own line.

String that's returned

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

My code which produces above string instead of executing that SQL:

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 @sqlquery = @sql 

    SELECT @sql 
code i used to call
    DECLARE @sqlquery NVARCHAR(1000) 

    SET @sqlquery='' 

    EXECUTE dbo.Getmultiplecomorbidquery 
      @sqlquery output 

    SELECT @sqlquery

Your SP runs itself recursively, but never attempts to execute the query that it has built. 32 is the recursion limit. (See [`@@NestLevel`](http://msdn.microsoft.com/en-us/library/ms187371(v=sql.100).aspx).) — HABO, Dec 04 '12 at 21:22
@habo I was thinking that. I remember reading that the nested stored procedure limit it 32 and I figured that had something to do with it. — wootscootinboogie, Dec 04 '12 at 21:23

score 0 · Answer 1 · answered Dec 04 '12 at 21:27

0

You never execute the query, just return it, you need an EXECUTE @sql in there (or execute the results).

answered Dec 04 '12 at 21:27

EvilBob22

732
5
12

Run a query from a stored procedure's OUTPUT parameter

1 Answers1