Is it possible to send a table/column name as a param? RRS feed

  • Question

  • Hi,

    is it possible to send a table/column name as a param and the stored procedure will perform a dynamic select statment using this param?

    something like:

    select @colName from @tblName ;


    Tuesday, November 19, 2019 7:22 PM


All replies

  • Use dynamic sql. But why do you need this?

    You will find Erland's very informative posting on this topic:

    Tuesday, November 19, 2019 7:24 PM
  • CREATE PROCEDURE DynamicQuery 
       @tableName varchar(128),
       @columnNames varchar(1000)  -- = Col1, Col2, Col3 ...
    	DECLARE @sql varchar(max) = '';
    	SET @sql = 'SELECT ' + @columnNames + ' FROM ' + @tableName;
    	PRINT (@sql);
    	--EXECUTE (@sql);

    A Fan of SSIS, SSRS and SSAS

    Tuesday, November 19, 2019 8:29 PM
  • You can avoid dynamic sql

    Create a user-defined data type with a single column.
    Develop a procedure with a table variable as an input parameter.
    Declare a table variable of the type of the user defined data type.
    Loading 10 records into the table variable and pass the table 
    variable to the stored procedure.

    create type tt_example AS TABLE
     (spid int)
    create procedure usp_example
     @spids tt_example READONLY
     SELECT *
     FROM @spids
    declare @spids tt_example

    insert into @spids
    select top 10 spid
    from sys.sysprocesses

    exec usp_example @spids=@spids

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 20, 2019 6:02 AM
  • Thank you but its less readable.

    Also i dont see how you send the tabl name and col name as params.

    Wednesday, November 20, 2019 3:29 PM