sql存储过程参数作为动态查询的参数 [英] sql stored procedure argument as parameter for dynamic query

问题描述

这个过程有三个参数.但是当我尝试通过传递参数来执行时，它显示了一个错误.请帮帮我.

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'

错误:

消息 1087，级别 15，状态 2，第 1 行必须声明表变量@Tabname".

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

推荐答案

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

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';

我改变了什么?

1. 在创建/引用对象时始终使用 dbo 前缀.
2. 表名和列名是 NVARCHAR 并且 可以超过 150 个字符.允许参数容纳某人将来可能添加的表要安全得多.
3. 添加了 SET NOCOUNT ON 以防止网络开销和可能向客户端发送错误的结果集.
4. @sql 应该总是 NVARCHAR.
5. 在实体名称(例如表或列)周围使用 QUOTENAME 以帮助阻止 SQL 注入并防止选择不当的名称(例如关键字).
6. 尽可能使用适当的参数(再次帮助阻止 SQL 注入，但也避免必须对字符串参数进行各种类型的分隔符转义).

1. Always use dbo prefix when creating / referencing objects.
2. 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.
3. Added SET NOCOUNT ON as a guard against network overhead and potentially sending erroneous result sets to client.
4. @sql should always be NVARCHAR.
5. 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).
6. 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存储过程参数作为动态查询的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！