June 17, 2008 at 2:28 am
I have the following procedure:
CREATE PROCEDURE [dbo].[spGetTestedExamPatterns]
@ExamIdint
As
SET NOCOUNT ON;
DECLARE @sql varchar(200)
SET @sql = 'SELECTP.PatternId, P.PatternName
FROMdbo.tblPatterns P, dbo.tblExamPatterns EP
WHEREP.PatternId = EP.PatternId'
If @ExamId <> 0
SET @sql = @sql + ' ANDExamId = ' + CAST(@ExamId As VARCHAR)
exec(@sql)
as you noticed, i used dynamic statement because an impotant reason:
I need the query to change according to the @ExamId value.
The problem that i need to add a where command only if the @ExamId parameter is not (0).
i tried to use CASE but couldnot find an expression to select all data if @ExamId is 0 and select a specific row (which ExamId equals @ExamId) when @ExamId is not 0.
Sure i can do this using If-Then but then i have to repeat the statement many as the parameters involved in this If condition.
So do you have any ideas about avoiding using dynamic SQL in such example, and specially i have two another versions of this procedure, one of 2 paramters, and one of 3 parameters, and i have
June 17, 2008 at 3:10 am
Hello,
Yes, you can do that without dynamic SQL.
There are several ways, and everything (and much more) you can find at http://www.sommarskog.se/
Especially the article Dynamic Search Conditions will be of interest to you, but I suggest you also read some other, namely "Curse and Blessings of Dynamic SQL" and "Arrays and Lists in SQL Server".
I suppose that in your case a condition (table.column=@parameter OR @parameter = 0) in the WHERE clause could do the job pretty well, but it will be best if you check all possible solutions at Erland Sommarskog's pages (the above link) first.
CREATE PROCEDURE [dbo].[spGetTestedExamPatterns]
@ExamId int
As
SET NOCOUNT ON;
SELECT P.PatternId, P.PatternName
FROM dbo.tblPatterns P, dbo.tblExamPatterns EP
WHERE P.PatternId = EP.PatternId
AND (ExamId = @ExamId OR @ExamId = 0)
June 17, 2008 at 3:47 am
Thank you a lot, really i'm using this technique with NULL values, but i made it more complicated for me with this condition.
Thank you very much, you saved me a lot of code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply