Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."