0

I have a stored procedure that outputs a select statement as you see below:

select case when count(*) > 10 then ''A1'' else ''B1'' end as id, name, address from database.dbo.student

Now I want to write a stored procedure that takes such a select statement as string input, and return all the above outputs. I'm not sure where to assign the output variables. 

ALTER PROCEDURE dbo.selectAttributes
    @select_statement Nvarchar(MAX),
    @id Nvarchar(255) OUT,
    @name nvarchar(255) OUT,
    @address nvarchar(255) OUT
AS 
BEGIN 
    DECLARE @query nvarchar(MAX)

    SET @query = @select_statement

    EXEC sp_executesql @query, 
                       N'@select_statement Nvarchar(MAX), 
                       @idOUT Nvarchar(255) OUTPUT,
                       @nameOUT nvarchar(255) OUTPUT,
                       @addressOUT nvarchar(255) OUTPUT',
                       @select_statement, @id OUTPUT, @name OUTPUT, @address OUTPUT
END
Ayanabh Chakraborty
  • 31
  • 4

1 Answers1

1

I think you are close:

ALTER PROCEDURE dbo.selectAttributes (
    @select_statement Nvarchar(MAX),
    @id Nvarchar(255) OUTPUT,
    @name nvarchar(255) OUTPUT,
    @address nvarchar(255) OUTPUT
) AS 
BEGIN 
    EXEC sp_executesql @select_statement,
         N'@id Nvarchar(255) OUTPUT, @name nvarchar(255) OUTPUT, @address nvarchar(255) OUTPUT',
           @id = @id OUTPUT,
           @name = @name OUTPUT,
           @address = @address OUTPUT
END;

In other words:

  • You have to put the OUTPUT keyword everywhere.
  • The query string does not go in as a parameter.
  • I don't rename the parameters, but if you do, they have to be declared somewhere.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • What if the input `select` statement has alias. For ex., `select case when count(*) > 10 then ''A1'' else ''B1'' end as id, name, address from database.dbo.student`. In this case, the output parameter is returning null – Ayanabh Chakraborty Mar 26 '17 at 20:20
  • @AyanabhChakraborty . . . I don't think this type of dynamic SQL on a passed in query is a good idea in general. But that is not the subject of your question. The subject is how to get the stored procedure to do what you want and that is what this answers. The arguments have to be compatible with each other. – Gordon Linoff Mar 26 '17 at 21:56
  • Yes, I agree with you. In my implementation I'm not passing it manually. It's actually a complicated Stored proc with dynamic table, schema and database name as inputs doing calculations and throwing a select statement which I have to handle and get the columns out of it. I just modified the first stored proc to output me something like `select @id = case when count(*) > 10 then ''A1'' else ''B1'' end, ....` and it worked. Thank you! – Ayanabh Chakraborty Mar 27 '17 at 00:20