CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Kishanu Bhattacharya
Kishanu Bhattacharya

Reputation: 97

Using row values as columns in the select statement

I am trying to use the rows retrieved by a SQL query in an another SQL query as a column and a table name

For Example:

declare @var1 varchar(max)
declare @var2 varchar(max)
declare @result varchar(max)

set @var1 = 'cctl_country'
set @var2 = 'typecode'
set @result = 'select '+@var2+' from '+@var1
print @result

In the above example the output would be select typecode from cctl_country. It should also result like select cctl_country,typecode from cctl_country. The table_name should follow with the typecode.

But, the twist is that the @var1 is dynamic and keeps changing and this needs to be retrieved from Information Schema. So, for every change in the @var1 value, I need to execute the select query and insert the result set into another table. I was trying the same thing in the below code but I was getting stuck. A little help would be appreciated

CREATE PROCEDURE usp_try
AS
BEGIN
    DECLARE @StartLoop INT
    DECLARE @EndLoop INT
    DECLARE @esal INT
    DECLARE @sqlquery varchar(max)

    DECLARE @Result TABLE (table_name nvarchar(100),typecodes nvarchar(100))
    DECLARE @InitResult TABLE (id INT IDENTITY(1, 1),
                         table_name NVARCHAR(50),
                         typecode NVARCHAR(50))

   INSERT INTO @InitResult
    select  col.TABLE_NAME,'typecodes' as typecodes from [NFUM_Studio].INFORMATION_SCHEMA.COLUMNS col
inner join [NFUM_Studio].INFORMATION_SCHEMA.TABLES tab
on col.TABLE_NAME = tab.TABLE_NAME
where col.COLUMN_NAME = 'typecode' 

    SELECT @StartLoop = MIN(ID),
         @EndLoop = MAX(ID)
    FROM @InitResult

    WHILE @StartLoop <= @EndLoop
       BEGIN

          SET @sqlquery = 'insert into @Result
                          select table_name,typecode from @InitResult'


          SET @StartLoop = @StartLoop + 1
       END

    SELECT *
    FROM @Result
END

Upvotes: 3

Views: 162

Answers (2)

Kishanu Bhattacharya
Kishanu Bhattacharya

Reputation: 97

Finally I was able to get the output, but with the help of cursor

DECLARE @@CC_TABLENAME VARCHAR(255)
declare @cc_exec varchar(255)
declare @cc_table varchar(255)
create table #temp(table_name varchar(max),typecode varchar(max))
DECLARE @CSR_TL AS CURSOR;

SET @CSR_TL = CURSOR FOR 

select  col.TABLE_NAME from [NFUM_Studio].INFORMATION_SCHEMA.COLUMNS col
where col.COLUMN_NAME = 'typecode' 


OPEN @CSR_TL

FETCH NEXT FROM @CSR_TL INTO @@CC_TABLENAME
WHILE @@FETCH_STATUS = 0
BEGIN

    set @cc_exec = 'insert into #temp(typecode) select typecode from '+@@CC_TABLENAME
    exec(@cc_exec)
    set @cc_table = 'update #temp set table_name='''+@@CC_TABLENAME+''' where table_name is null'
    exec(@cc_table)

    FETCH NEXT FROM @CSR_TL INTO @@CC_TABLENAME
END

--drop table #temp

CLOSE @CSR_TL
DEALLOCATE @CSR_TL

Upvotes: 1

Hadi
Hadi

Reputation: 37313

Just try using Temp Table instead of Table variable, and there is no need to join Information_Schema.Columns table with Information_Schema.Tables. Also you missed to execute the query stored in @strQuery, just use EXEC(@strQuery)

CREATE PROCEDURE usp_try
AS
BEGIN
    DECLARE @StartLoop INT
    DECLARE @EndLoop INT
    DECLARE @esal INT
    DECLARE @sqlquery varchar(max)

    CREATE TABLE #Result(table_name nvarchar(100),typecodes nvarchar(100))
    CREATE TABLE #InitResult(id INT IDENTITY(1, 1),
                         table_name NVARCHAR(50),
                         typecode NVARCHAR(50))

   INSERT INTO #InitResult
    select  col.TABLE_NAME,'typecodes' as typecodes from [NFUM_Studio].INFORMATION_SCHEMA.COLUMNS col
    where col.COLUMN_NAME = 'typecode' 

    SELECT @StartLoop = MIN(ID),
         @EndLoop = MAX(ID)
    FROM @InitResult

    WHILE @StartLoop <= @EndLoop
       BEGIN

        SET @sqlquery = 'insert into #Result SELECT * FROM ('


        SELECT @sqlquery = @sqlquery + 'SELECT ''' + Table_name + ''' as [Table_name], [' + type_code + '] FROM ' + Table_name ' UNION ALL '
        FROM #InitResult


        SET @sqlquery = SUBSTRING(@strquery,1,LEN(@strquery) - 11)


        SET @sqlquery = @sqlquery + ' ) AS T1'


        EXEC(@strquery)




          SET @StartLoop = @StartLoop + 1
       END

    SELECT *
    FROM #Result
END

Upvotes: 1

Related Questions