如何从SQL Server中传递的参数中选择表名和列名?

I have two very similar tables in our database, and I need to write a stored procedure for my Visual Studio 2010 Web Application to read the data from one of these tables given a table number.

我的数据库中有两个非常相似的表，我需要为我的Visual Studio 2010 Web应用程序编写一个存储过程，以便在给定表号的情况下读取其中一个表的数据。

Currently, we only have two tables to select from, but I can see this growing to more as this project grows.

目前，我们只有两个表可供选择，但是我可以看到随着这个项目的发展，这个表会越来越多。

This is sort of what I am trying to do, but this code is not correct:

这就是我想做的，但是这个代码是不正确的:

PROCEDURE [dbo].[spGetData]
  @tableID int
AS
BEGIN
SET NOCOUNT ON;
    declare @col1 nvarchar(50), @table nvarchar(50)
    set @col1=case when @tableID=1 then 'SMRequestID' else 'WHRequestID' end
    set @table=case when @tableID=1 then 'SMRequest' else 'WHRequest' end

    select @col1 as 'Request', WorkOrder, PartNumber, Qty, EmployeeID
    from @table
END

Basically, the ColumnName and TableName depend on the @tableID parameter that will be passed in.

基本上，ColumnName和TableName取决于将传入的@tableID参数。

How would I go about doing that?

我该怎么做呢?

Note: My searches are not turning up anything related, but I am a C# developer and not a database developer. I imagine this has been asked before, it is just I am not using the right keywords.

注意:我的搜索没有发现任何相关信息，但是我是c#开发人员，而不是数据库开发人员。我想这之前有人问过，只是我没有使用正确的关键词。

3 个解决方案

#1

One option would be to use a conditional based upon the ID and put the code for a specific table in each section for the table.

一种选择是使用基于ID的条件，并将特定表的代码放在表的每个部分中。

I prefer this method to get away from the dynamic sql and allow the database server to get a fighting chance to optimize the thing for speed reasons by precompiling.

我更喜欢这种方法来摆脱动态sql，并允许数据库服务器通过预编译来获得一个战斗机会，以优化速度。

NOTE: database servers are pretty bad at string manipulation (create dynamic sql) in general.

注意:一般来说，数据库服务器在字符串操作(创建动态sql)方面非常糟糕。

EDIT1: EXAMPLE

EDIT1:示例

FOR INSTANCE: THIS SQL

例如:SQL

declare @mytest varchar(5)
set @mytest = 'PROCS'

IF @mytest = 'PROCS'
BEGIN /* STORED PROCS */
  SELECT DISTINCT 
    o.name AS ObjectName_StoredProcedure
  FROM sysobjects as o 
  WHERE o.xtype = 'P'
END
ELSE
IF @mytest = 'DEFAULT'
BEGIN
   SELECT DISTINCT 
    o.name AS ObjectName_StoredProcedure
  FROM sysobjects as o 
  WHERE o.xtype = 'D'
END

gives you the store procedure names or the default constraints depending on what you pass to the parameter.

根据传递给参数的内容，给出存储过程名称或默认约束。

EDIT2: Based on OP code:

EDIT2:基于OP代码:

CREATE PROCEDURE [dbo].[spGetData]   
  (@tableID int  )
   AS 
  BEGIN
     SET NOCOUNT ON;
     IF @tableID = 1
     BEGIN
       SELECT SMSRequestId AS 'Request',
          WorkOrder, PartNumber, Qty, EmployeeID 
       FROM SMRequest
     END   
     IF @tableID = 2
     BEGIN
       SELECT WHRequestID AS 'Request',
          WorkOrder, PartNumber, Qty, EmployeeID 
       FROM WHRequest
    END
 END

#2

Although I think Mark is quite correct given the small number of tables and simplicity of your queries, here is a dynamic sql example that passes both the table and column names:

尽管我认为Mark是非常正确的，考虑到表的数量和查询的简单性，下面是一个动态sql示例，它同时传递表名和列名:

CREATE PROCEDURE spGetData
(
@TableName nvarchar(128),
@ColumnName nvarchar(128)
)
AS
  BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(4000)
    SET @SQL = 'SELECT ' + @ColumnName + ', as Request, WorkOrder, PartNumber, Qty, EmployeeID FROM ' + @TableName 
    EXEC sp_executesql @SQL
  END

You can call it as follows:

你可以这样称呼它:

exec spGetData 'SMRequest', 'SMRequestID'
exec spGetData 'WHRequest', 'WHRequestID'

#3

Do it with dynamic SQL:

使用动态SQL完成:

PROCEDURE [dbo].[spGetData]
  @tableID int
AS
BEGIN
SET NOCOUNT ON;
    declare @col1 nvarchar(50), @table nvarchar(50),  @cmd nvarchar(400)
    set @col1=case when @tableID=1 then 'SMRequestID' else 'WHRequestID' end
    set @table=case when @tableID=1 then 'SMRequest' else 'WHRequest' end


    @cmd = "select " + @col1 + " as 'Request', WorkOrder, PartNumber, Qty, EmployeeID from " + @table

   EXEC(@cmd)

END

#1