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

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 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.

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.