将表名作为参数传递给sql server

问题描述

我想将表名作为参数传递，我想在 where 子句中传递该参数

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

推荐答案

未测试但需要使用 DynamicSQL.

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