none
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 ;

    Thanks.

    Tuesday, November 19, 2019 7:22 PM

Answers

All replies

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

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

    http://www.sommarskog.se/dynamic_sql.html

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


    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)
    go
    create procedure usp_example
     @spids tt_example READONLY
    AS
     SELECT *
     FROM @spids
    GO
    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, http://sqlblog.com/blogs/uri_dimant/

    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
    Answerer
  • 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