How to store a dynamic SQL result in a variable in T-SQL?

Question

I have a stored procedure which takes 'table name' as parameter. I want to store my 'exec' results to a variable and display using that variable. Here is my T-SQL stored procedure..

create procedure  DisplayTable( @tab varchar(30))
as
begin
   Declare @Query VARCHAR(30)
   set @Query='select * from ' +@tab
   EXEC (@Query)
END

I want to do something like this..

SET @QueryResult = EXEC (@Query)
select @QueryResult

How do i achieve this.. Please help.. I am a beginner..

There is no simple\practical way to do this. That is because what you are trying to do does not fit ideally within the SQL paradigm. If you think a little further, you will see that you will need a dynamically structured "tabular variable" and because its schema is not known, you will need to query it with dynamic SQL too! - PS. Not saying it is not possible, just that it is neither practical nor elegant. — MarkD, May 13 '14 at 05:01
What you're asking seems to be "How do I create a procedure that queries some undefined data and returns an undefined result in a limited string?" I agree that it might be possible; but I can't think of anything close to getting it done. — user2338816, May 13 '14 at 05:24

score 1 · Answer 1 · answered May 13 '14 at 06:23

You can use XML for that. Just add e.g. "FOR XML AUTO" at the end of your SELECT. It's not tabular format, but at least it fulfills your requirement, and allows you to query and even update the result. XML support in SQL Server is very strong, just make yourself acquainted with the topic. You can start here: http://technet.microsoft.com/en-us/library/ms178107.aspx

score 0 · Answer 2 · answered Oct 08 '14 at 14:24

alter procedure  DisplayTable( 
    @tab varchar(30)
    ,@query varchar(max) output
)
as
BEGIN
  Declare @execution varchar(max) = 'select * from ' +@tab

    declare @tempStructure as table (
        pk_id int identity
        ,ColumnName varchar(max)
        ,ColumnDataType varchar(max)
    )

    insert into
        @tempStructure
    select
        COLUMN_NAME
        ,DATA_TYPE
    from 
        INFORMATION_SCHEMA.columns
    where TABLE_NAME= @tab

  EXEC(@execution)

  declare @ColumnCount int = (SELECT count(*) from @tempStructure)
  declare @counter int = 1
  while @counter <= @ColumnCount
  BEGIN
    IF @counter = 1
    BEGIN
        set @query = (SELECT ColumnName + ' ' + ColumnDataType FROM @tempStructure where pk_id= @counter)
    END
    IF @counter <> 1
    BEGIN
        set @query = @query +  (SELECT ',' + ColumnName + ' ' + ColumnDataType FROM @tempStructure where @counter = pk_id)
    END
    set @counter = @counter + 1
  END
END

When you execute the SP, you'll now get a return of the structure of the table you want.

This should hopefully get you moving.

If you want the table CONTENTS included, create yourself a loop for the entries, and append them to the @query parameter.

Remember to delimit the @query, else when you read it later on, you will not be able to restructure your table.

score 0 · Answer 3 · answered Oct 08 '14 at 17:25

First of all you have to understand that you can't just store the value of a SELECTon a table in simple variable. It has to be TABLE variable which can store the value of a SELECTquery.

Try the below:

select 'name1' name, 12 age
into MyTable
union select 'name2', 15 union
select 'name3', 19

--declaring the table variable and selecting out of it..

declare @QueryResult table(name varchar(30), age int)
insert @QueryResult exec DisplayTable 'MyTable'
select * from @QueryResult

Hope this helps!

How to store a dynamic SQL result in a variable in T-SQL?

3 Answers3