sql存储过程参数作为动态查询的参数

This procedure has three parameters. But when I try to execute by passing parameters it shows me an error. Please help me.

此过程有三个参数。但是当我尝试通过传递参数执行时它会向我显示错误。请帮帮我。

create procedure queryfunctions @Tabname varchar(150),@colname varchar(150),@valuesname varchar(150)
as
begin
declare @sql varchar(4000)
select @sql='select * from @Tabname where @colname=@valuesname'
exec(@sql)
end

exec queryfunctions 'education','eduChildName','Revathi'

Error :

Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@Tabname".

消息1087,级别15,状态2,行1必须声明表变量“@Tabname”。

2 个解决方案

#1

Here is a much safer alternative:

这是一个更安全的替代方案:

ALTER PROCEDURE dbo.queryfunctions 
  @Tabname NVARCHAR(511),
  @colname NVARCHAR(128),
  @valuesname VARCHAR(150)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = 'SELECT * FROM ' + @Tabname 
           + ' WHERE ' + QUOTENAME(@colname) + ' = @v';

  EXEC sp_executesql @sql, N'@v VARCHAR(150)', @valuesname;
END
GO

EXEC dbo.queryfunctions N'dbo.education', N'eduChildName', 'Revathi';

What did I change?

我改变了什么?

Always use dbo prefix when creating / referencing objects.

创建/引用对象时始终使用dbo前缀。

Table and column names are NVARCHAR and can be longer than 150 characters. Much safer to allow the parameters to accommodate a table someone might add in the future.

表名和列名是NVARCHAR,可以超过150个字符。允许参数容纳某人可能在将来添加的表更安全。

Added SET NOCOUNT ON as a guard against network overhead and potentially sending erroneous result sets to client.

添加了SET NOCOUNT ON作为防范网络开销并可能向客户端发送错误的结果集。

@sql should always be NVARCHAR.

@sql应该始终是NVARCHAR。

Use QUOTENAME around entity names such as tables or columns to help thwart SQL injection and also to guard against poorly chosen names (e.g. keywords).

在表格或列等实体名称周围使用QUOTENAME可以帮助阻止SQL注入,并防止选择不当的名称(例如关键字)。

Use proper parameters where possible (again to help thwart SQL injection but also to avoid having to do all kinds of escaping of delimiters on string parameters).

尽可能使用适当的参数(再次帮助阻止SQL注入,但也避免必须在字符串参数上进行各种转义分隔符)。

#2

-3

Why are you passing object names as parameters?

为什么要将对象名称作为参数传递?

If you pass string value to @valuesname, your code should be

如果将字符串值传递给@valuesname,则代码应为

create procedure queryfunctions 
(
@Tabname varchar(150),@colname varchar(150),@valuesname varchar(150) 
)
as 
begin 
declare @sql varchar(4000) 
select @sql='select * from '+@Tabname+' where '+@colname+'='''+@valuesname+'''' 
exec(@sql) 
end

Don't know how to use single quotes in dynamic sql? Refer this http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

不知道如何在动态sql中使用单引号?请参阅http://beyondrelational.com/modules/2/blogs/70/posts/10827/understanding-single-quotes.aspx

#1