locked
how run procedure to select specific column name by using stuff and xml RRS feed

  • Question

  • User-1634604574 posted

    i have this code to select column name dynamically i want to make this code as procedure and run the procedure to select spcific column name by parameter how can i do it

    declare @column nvarchar(200);

    select @column = STUFF(( SELECT ',[' + colname +']'
    FROM ( select [name] as colname from sys.columns where object_id = object_id('dbo.customer') ) as cte --cte

    FOR
    XML PATH('')
    ), 1, 1, '')


    exec( 'select '+ @column +' from dbo.customer')

    customer table

    id        age      gender

    1          23          female

    2          22          female

    3          33           female

    for example i want to select only age,gender from that table by parameter dynamically 

    Friday, February 1, 2019 2:07 PM

All replies

  • User2103319870 posted

    as procedure and run the procedure to select spcific column name by parameter how can i do it

    You can try with below query to select column based on parameter

    CREATE PROCEDURE sp_SelectQuery @columnname VARCHAR(500) 
    AS 
    BEGIN 
        DECLARE @sql NVARCHAR(4000);
        SET @sql='select ['+@columnname+'] from dbo.customer';
        EXEC sp_executesql @sql
    END 

    Execute the stored procedure like below

    EXEC sp_SelectQuery 'age'

    Friday, February 1, 2019 2:23 PM