locked
Passing tablename in a stored procedure and then Querying the same table inside the procedure. RRS feed

  • Question

  • I am writing a procedure where I am passing the value of my table as a string, now I want to query the same table inside, is it possible anyways with passing parameters of type table, something like the below code.

    create procedure test(@table_name varchar(max))
    AS
    BEGIN
    declare @tablename varchar(max)=@table_name;
    declare @object_id int;
    set @object_id=OBJECT_ID(@table_name)
    select *
    from sys.columns
    where object_id=@object_id
    Select *
    from <want to query the same table>
    END

    Please let me if there is some other approach to do this, the requirement remains the same that I don't want to pass the table variable as the parameter, because I don't want the overhead of passing values in the table variable while every time I call the procedure


    Thursday, July 26, 2012 1:28 PM

Answers

  • As far as I know, the only thing you can do is using dynamic SQL. But that will give you new problems. Probably you want to do something with the result set of the query inside your proc. That is not possible, unless the dynamic SQL inserts the result set into a table that you can access from your T-SQL code.

    create procedure test(@table_name varchar(max))
     AS
     BEGIN
     declare @tablename varchar(max)=@table_name;
     declare @object_id int;
     set @object_id=OBJECT_ID(@table_name)
     select *
     from sys.columns
     where object_id=@object_id;
    
     declare @statement varchar(max);
     set @statement = 'Select * from ' + @tablename;
     execute (@statement);
     END

    • Edited by Chris Sijtsma Thursday, July 26, 2012 1:46 PM
    • Proposed as answer by Naomi N Thursday, July 26, 2012 2:04 PM
    • Marked as answer by DivasP Friday, July 27, 2012 5:00 AM
    Thursday, July 26, 2012 1:36 PM

All replies

  • As far as I know, the only thing you can do is using dynamic SQL. But that will give you new problems. Probably you want to do something with the result set of the query inside your proc. That is not possible, unless the dynamic SQL inserts the result set into a table that you can access from your T-SQL code.

    create procedure test(@table_name varchar(max))
     AS
     BEGIN
     declare @tablename varchar(max)=@table_name;
     declare @object_id int;
     set @object_id=OBJECT_ID(@table_name)
     select *
     from sys.columns
     where object_id=@object_id;
    
     declare @statement varchar(max);
     set @statement = 'Select * from ' + @tablename;
     execute (@statement);
     END

    • Edited by Chris Sijtsma Thursday, July 26, 2012 1:46 PM
    • Proposed as answer by Naomi N Thursday, July 26, 2012 2:04 PM
    • Marked as answer by DivasP Friday, July 27, 2012 5:00 AM
    Thursday, July 26, 2012 1:36 PM
  • declare @sql varchar(max)
    declare @tablename varchar(max)
    Set @tablename = 'contact';
    set @sql = 'select * from dbo.'  + @tablename 

    exec (@sql)

    --

    create procedure test(@tablename varchar(max))
    AS
    BEGIN
    declare @sql varchar(max)
    set @sql = 'select * from dbo.'  + @tablename 

    exec (@sql)
    END

    exec test 'contact'

    • Edited by JR1811 Thursday, July 26, 2012 1:44 PM
    Thursday, July 26, 2012 1:40 PM
  • I am writing a procedure where I am passing the value of my table as a string, now I want to query the same table inside, is it possible anyways with passing parameters of type table, something like the below code.

    create procedure test(@table_name varchar(max))
    AS
    BEGIN
    declare @tablename varchar(max)=@table_name;
    declare @object_id int;
    set @object_id=OBJECT_ID(@table_name)
    select *
    from sys.columns
    where object_id=@object_id
    Select *
    from <want to query the same table>
    END

     You said is it possible anyways with passing parameters of type table, but what I can see is your parameter is of Varchar type

    Please let me if there is some other approach to do this, the requirement remains the same that I don't want to pass the table variable as the parameter, because I don't want the overhead of passing values in the table variable while every time I call the procedure

     I think there is no need of table table variable at all, how ever since you want to select your table in the SP then you have to pass the parameter which contains table name and the same is being selected in  the SP, some thing as follows,

     But still could you please elaborate more about the exact requirement of this SP, as I can see you retrieved the columns information from sys.columns and then there is only a select statement.

    	CREATE PROCEDURE TEST(@TABLE_NAME VARCHAR(MAX))
    	AS
    	BEGIN
    		Declare @sqlText nvarchar(100)
    		DECLARE @TABLENAME VARCHAR(MAX)=@TABLE_NAME;		
    		DECLARE @OBJECT_ID INT;
    		SET @OBJECT_ID=OBJECT_ID(@TABLE_NAME)
    		
    		SELECT *
    		FROM SYS.COLUMNS
    		WHERE OBJECT_ID=@OBJECT_ID
    		
    		set @sqlText='SELECT * FROM '+ @TABLE_NAME
    		Execute sp_executesql @sqlText
    	END


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Thursday, July 26, 2012 1:57 PM
  • I think you still need to pass table name as the parameter then you can further run dynamic Sql to do that. Please reference this sample for Dynamic Sql

    DECLARE @IntVariable int;
    DECLARE @SQLString nvarchar(500);
    DECLARE @ParmDefinition nvarchar(500);
    
    /* Build the SQL string one time.*/
    SET @SQLString =
         N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
           FROM AdventureWorks.HumanResources.Employee 
           WHERE ManagerID = @ManagerID';
    SET @ParmDefinition = N'@ManagerID tinyint';
    /* Execute the string with the first parameter value. */
    SET @IntVariable = 197;
    EXECUTE sp_executesql @SQLString, @ParmDefinition,
                          @ManagerID = @IntVariable;
    /* Execute the same string with the second parameter value. */
    SET @IntVariable = 109;
    EXECUTE sp_executesql @SQLString, @ParmDefinition,
                          @ManagerID = @IntVariable;


    Many Thanks & Best Regards, Hua Min

    Thursday, July 26, 2012 2:08 PM
  • So what do you want to really? I don't understand this I don't want the overhead of passing values in the table variable while every time I call the procedure. Have you measure this overhead and concluded that it is excessive? How are you calling the procedure?

    Passing table names to stored procedures is usually the wrong thing to do, and often indicates and an error in the design.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Thursday, July 26, 2012 2:41 PM
    Thursday, July 26, 2012 2:39 PM
  • @manish_bi: there way a typo in the first line, "with" should have been without*, sorry for confusing you and killing your time, anyways your solution gave me what I was looking thanks.
    Friday, July 27, 2012 4:51 AM