将WHERE条件传递给存储过程

here is my Storprocedure

这是我的Storprocedure

CREATE PROCEDURE [B]

 @BoardID varchar(5000)

AS

declare @sB varchar(8000)
set @sB= ' '


Select name,id,address  from BoardDetail

WHere IsActive=1 and @sB=@BoardID
GO

here i send string parameter....My @BoardID contain string condition like: name=a and id=5 and address =adfas

这里我发送字符串参数....我的@BoardID包含字符串条件,如:name = a和id = 5 and address = adfas

i want to supply just string and want to set on beside the and can any one help me to fix the error

我想提供只是字符串,并希望在旁边设置,任何人都可以帮我修复错误

7 个解决方案

#1

You need to construct a dynamic query.

您需要构建动态查询。

See this article: The Curse and Blessings of Dynamic SQL. It's the canonical reference for dynamic SQL.

请参阅此文章:动态SQL的诅咒和祝福。它是动态SQL的规范参考。

As others have noted, you should use dynamic SQL sparingly and in situations where no other method is suitable. Dynamic SQL can open up the risk of SQL injection attacks, and as noted in "The Curse and Blessings of Dynamic SQL", there are also more subtle gotchas to watch out for.

正如其他人所指出的那样,您应该谨慎使用动态SQL,并且在没有其他方法适合的情况下。动态SQL可以开辟SQL注入攻击的风险,并且如“动态SQL的诅咒和祝福”中所述,还有一些需要注意的细微问题。

#2

That's a really bad practice. It's going to restrict your ability to validate your SQL parameters, reduce or eliminate query plan reuse, and it might enlargen the hole in the ozone layer.

这是一个非常糟糕的做法。它将限制您验证SQL参数,减少或消除查询计划重用的能力,并且可能会扩大臭氧层中的漏洞。

I'm kidding about the last one - not the first two.

我开玩笑说最后一个 - 而不是前两个。

You're far better off just creating three parameters:

你创建三个参数会好得多:

CREATE PROCEDURE B
  @name varchar(10),
  @id int,
  @address varchar(20)
AS
BEGIN
  SELECT name, address FROM BoardDetail
  WHERE IsActive = 1 AND BoardID = @id AND name = @name AND address = @address
END

Trust me - the road to hell is paved with concatenated query strings.

相信我 - 地狱之路是用连接的查询字符串铺平的。

#3

You can do that using dynamic SQL, with exec or sp_executesql:

你可以使用动态SQL,exec或sp_executesql来做到这一点:

CREATE PROCEDURE [B]
    @BoardWhere varchar(5000)
AS
declare @query varchar(8000)
set @query = 'Select name,id,address from BoardDetail where ' + @BoardWhere
exec (@query)

It's best practice to give a schema name when declaring stored procedures, f.e.:

最好的做法是在声明存储过程时给出模式名称,f.e。:

CREATE PROCEDURE dbo.[B]

And, the stored procedure is open to sql injection, so be aware whom you give execute rights on it. For example, someone could pass "1=1" as a paremeter, or even worse things.

并且,存储过程对sql注入是开放的,因此请注意您对其授予执行权限。例如,有人可以通过“1 = 1”作为一个参数,甚至更糟糕的事情。

#4

If you want multiple search items that you pass at run time, you can do this instead of using dynamic SQl. Remember using dynamic SQL is usually a poor practice if it can be avoided.

如果您想要在运行时传递多个搜索项,则可以执行此操作而不是使用动态SQl。记住使用动态SQL通常是一种不好的做法,如果可以避免的话。

select * from mytable where (my_ID = @my_id OR @my_id IS NULL) and (client_id = @client_id or @client_id is null)

select * from mytable where(my_ID = @my_id OR @my_id IS NULL)和(client_id = @client_id或@client_id为null)

#5

Well, you can't do it that way. Check out this article on a couple of ways to do dynamic where clauses.

好吧,你不能这样做。查看本文有几种方法来做动态where子句。

#6

You're thinking about it wrong (well not wrong, but askew from how the framework lets you work with it).

你在考虑它是错的(好吧没错,但是从框架让你如何使用它的角度来看)。

You're trying to pass in SQL as a parameter and then append it to the SQL you have. This is possible using dynamic SQL but not without it - and you aren't using it.

您尝试将SQL作为参数传递,然后将其附加到您拥有的SQL。这可以使用动态SQL,但不是没有它 - 而且你没有使用它。

What you're literally doing is comparing:

你真正在做的是比较:

WHere IsActive=1 and @sB=@BoardID
becomes:
WHere IsActive=1 and ' '='name=5 and id=6'

which will return no results of course because an empty string is not equal to a string containing those characters.

因为空字符串不等于包含这些字符的字符串,所以当然不返回任何结果。

#7

You are trying to check for a non-existent column

您正在尝试检查不存在的列

Select name,id,address from BoardDetail

从BoardDetail中选择名称,ID,地址

WHere IsActive=1 and @sB=@BoardID

WHAre IsActive = 1和@ sB = @ BoardID

@sB is a variable which is always NULL and is never going to be = @BoardDetail

@sB是一个始终为NULL的变量,永远不会是= @BoardDetail

If you are trying to select based on string values sent by parameter @BoardDetail = 'name=a and id=5 and address =adfas' then try this:

如果您尝试根据参数@BoardDetail ='name = a和id = 5以及address = adfas'发送的字符串值进行选择,请尝试以下操作:

CREATE PROCEDURE [B]

创建程序[B]

@BoardID varchar(5000)

Declare @cmd varchar(8000)

声明@cmd varchar(8000)

SET @cmd = 'Select name,id,address from BoardDetail WHere IsActive=1 and '+ @BoardID

SET @cmd ='从BoardDetail选择名称,ID,地址WHere IsActive = 1和'+ @BoardID

EXECUTE (@CMD)

Raj

#1