Passing a WHERE Condition to a Stored Procedure

Question

here is my 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

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

Answer 1

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

Answer 2

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.

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.

Answer 3

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.

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.

Answer 4

You are trying to check for a non-existent column

Select name,id,address from BoardDetail

WHere IsActive=1 and @sB=@BoardID

@sB is a variable which is always NULL and is never going to be = @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:

CREATE PROCEDURE [B]

@BoardID varchar(5000)

AS

Declare @cmd varchar(8000)

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

EXECUTE (@CMD)

Raj

Answer 5

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.

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.

Answer 6

You can do that using dynamic SQL, with exec or 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.:

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.

Answer 7

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.

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