pass table name as parameter in sql server

Question

I want to pass table name as parameter and I want to that parameter in where clause

CREATE PROC [dbo].[bb_GetPrepositionInfo] 
    @userid INT,@propId INT,@tabName varchar(50)
AS 
      SET NOCOUNT ON 
      SET XACT_ABORT ON  

     BEGIN TRAN 

    SELECT *
    FROM   @tblname
    WHERE  ([acq_id] = @propId AND [user_id] = @userid) 

   COMMIT
    GO

possible duplicate of [Dynamic SQL (passing table name as parameter)](http://stackoverflow.com/questions/1325044/dynamic-sql-passing-table-name-as-parameter) — GSerg, Nov 29 '13 at 07:55
possible duplicate of [Column-name and/or table-name as parameters](http://stackoverflow.com/q/5791764/11683) — GSerg, Nov 29 '13 at 07:56

Vishwanath Dalvi · Answer 1 · 2013-11-29T08:23:39.717

3

Not tested but You need to use Dynamic SQL.

CREATE PROC [dbo].[bb_GetPrepositionInfo] 
    @userid INT,@propId INT,@tabName varchar(50)
AS 
    SET NOCOUNT ON 
    SET XACT_ABORT ON  

    BEGIN TRAN

    DECLARE @SQL varchar(250)
    SELECT @SQL = 'SELECT * FROM ' + QuoteName(@tabName) + ' where acq_id=' + Quotename(@propId) + ' AND user_id=' + Quotename(@userid)
    EXEC (@SQL)

    COMMIT
GO

edited Nov 29 '13 at 08:23

answered Nov 29 '13 at 07:56

Vishwanath Dalvi

35,388
41
123
155

Mr_eclair ...error Msg 207, Level 16, State 1, Procedure bb_GetPrepositionInfo, Line 10 Invalid column name 'propId'. – Kranti Singh Nov 29 '13 at 08:04
exec bb_GetPrepositionInfoByTable 523,35, 'bb_acquire_prvider' ....getting this error .....Msg 207, Level 16, State 1, Line 1 Invalid column name '35'. Msg 207, Level 16, State 1, Line 1 Invalid column name '523'. – Kranti Singh Dec 05 '13 at 07:13
I have change proc name bb_GetPrepositionInfo to bb_GetPrepositionInfoByTable – Kranti Singh Dec 05 '13 at 07:14

raza rabbani · Answer 2 · 2013-11-29T08:14:45.337

2

CREATE PROC [dbo].[bb_GetPrepositionInfo] 
  DECLARE  @userid INT
  DECLARE   @propId INT 
  DECLARE  @tabName varchar(50)
  DECLARE @sqlCommand varchar(200)
AS 
  SET NOCOUNT ON 
  SET XACT_ABORT ON  

  BEGIN TRAN

  SET @sqlCommand = 'SELECT * from ' + @tabName +'WHERE  [acq_id]='+  @propId +'AND   [user_id] = '+ @userid 
  EXEC (@sqlCommand)

  COMMIT
 GO

edited Nov 29 '13 at 08:14

answered Nov 29 '13 at 07:59

raza rabbani

449
3
12

1

You don't need "@sqlCommand varchar(200)" as a parameter if you are going to reset it every time in the procedure. Consider instead declaring it as a variable within the body of the procedure. – Mack Nov 29 '13 at 12:15

pass table name as parameter in sql server

2 Answers2

Linked