如何使用变量表名进行选择查询？ [英] How Do I Make Select Query With Variable Table Name?

问题描述

你好亲爱的

假设我们有很多表，名称如table1和table2和table3

i想要使用变量名进行查询，所以我可以对所有表格进行查询

 创建 程序 selectWithVaribleTableName 
  @ x   int   -    [任意数字介于1和1000之间]  
  as  
 声明  @ tablename   nvarchar （ 256 ）
  set   @ tablename  = '  table' +  convert （ nvarchar （ 100 ）， @ x ）
  select  * 来自  @ tablename  
  go  
 

但我收到错误。

请帮帮我

解决方案

在存储过程中，所有表名都应该在编译时知道，这意味着你不能使用变量名。



如果你需要动态查询那么你应该使用 exec（）： http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/ [ ^ ]



但是存在与此相关的安全问题，并且不鼓励。

  CREATE   PROCEDURE  selectWithVaribleTableName（< span class =code-sdkkeyword> @ TableName   Varchar （ 50 ））
  as  
 开始 
  DECLARE   @ Query   as   varchar （max）
  SET   @ Query  =  ' 选择* FROM' +  @ TableName  
  EXEC （ @ Query ）
  end  

hello dears
Suppose that we have many tables, with name like "table1" and "table2" and "table3"
i want make query with varible name, so that i can make query on all tables

CREATE PROCEDURE selectWithVaribleTableName
@x int -- [any number between 1 and 1000]
as
declare @tablename nvarchar(256)
set @tablename ='table' + convert(nvarchar(100),@x)
select * from @tablename
go

but i get error.
please help me

解决方案

In stored procedures all table names should be known at "compile time" meaning you can't use variable names.

If you need dynamic queries then you should use exec() : http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]

However there are security issues associated with this and is discouraged.

CREATE PROCEDURE selectWithVaribleTableName(@TableName Varchar(50))
as
begin
DECLARE @Query as varchar(max)
SET @Query = 'Select * FROM ' + @TableName
EXEC (@Query)
end

这篇关于如何使用变量表名进行选择查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！