pass table name as parameter in sql server
11,680
Solution 1
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
Solution 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
![Kranti Singh](https://i.stack.imgur.com/Mik0e.png?s=256&g=1)
Comments
-
Kranti Singh about 2 years
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
-
aelveborn over 10 yearspossible duplicate of Dynamic SQL (passing table name as parameter)
-
aelveborn over 10 yearspossible duplicate of Column-name and/or table-name as parameters
-
-
Kranti Singh over 10 yearsMr_eclair ...error Msg 207, Level 16, State 1, Procedure bb_GetPrepositionInfo, Line 10 Invalid column name 'propId'.
-
Mack over 10 yearsYou 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.
-
Kranti Singh over 10 yearsexec 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 over 10 yearsI have change proc name bb_GetPrepositionInfo to bb_GetPrepositionInfoByTable
-
Ehsan Sajjad about 10 yearsnice answer it helped me