Condition Statement in SQL Server IF...Else

Question

1.00/5 (1 vote)

See more:

, +

Hey Guys,
I am trying to make a Stored Procedure in SQL Server 2008 R2
That Takes 3 Parameters (AnalyserID,Date_From,Date_To)
Actually, The User Might not send the three parameters
so, When the stored procedure is executed it might have 1 parameter value or 2 or three or not taken any parameters at all.
I designed the stored procedure as Follows

SQL

CREATE PROCEDURE [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@p_AnalyserID INT,
@p_Date_From Datetime,
@p_Date_To Datetime
AS
DECLARE @query varchar(2000);
SET @query = 'SELECT * FROM T_ANALYSER_RESULTS WHERE T_ANALYSER_RESULTS.AnalysersID = ' + @p_AnalyserID
IF @p_Date_From is not null THEN
@query = @query + ' And T_ANALYSER_RESULTS.ResultDate >' + @p_Date_From
END IF;
IF @p_Date_To is not null THEN
@query = @query + ' T_ANALYSER_RESULTS.ResultDate < ' + @p_Date_To
END IF;
EXEC(@query)

But I am getting Syntax Error

Msg 156, Level 15, State 1, Procedure SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date, Line 8
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date, Line 11
Incorrect syntax near the keyword 'THEN'.

Any Ideas PlZ????
Its Urgent!!

Posted 5-Dec-13 19:26pm

_ProgProg_

Add a Solution

Comments

BulletVictim 6-Dec-13 1:32am

That should be(this is taken from my sql IF statements formatting)
IF @p_Date_From is not null
BEGIN
@query = @query + ' And T_ANALYSER_RESULTS.ResultDate >' + @p_Date_From
END

_ProgProg_ 6-Dec-13 1:35am

Thanks for ur Fast Reply
I did that but now the error Changed to this

Msg 102, Level 15, State 1, Procedure SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date, Line 9
Incorrect syntax near '@query'.
Msg 102, Level 15, State 1, Procedure SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date, Line 12
Incorrect syntax near '@query'.

and Intellisense is highlighting Under @query
Now What??

BulletVictim 6-Dec-13 1:56am

Ganesh Raja's solution should work for you

3 solutions

Solution 2

something like

select *
from T_ANALYSER_RESULTS
Where AnalysersID = IsNull(@p_analyserID, AnalysersID)
And ResultDate >= IsNull(@p_Date_from, ResultDate)
And ResultDate <= IsNull(@p_Date_to, ResultDate)

Creating and executing a query in a SP like you have is defeating the point of having an SP in the first place somewhat.

Posted 5-Dec-13 19:42pm

_Maxxx_

Solution 3

Thanks to you all Guys

This is the right Answer

SQL

ALTER PROCEDURE [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@p_AnalyserID INT,
@p_Date_From datetime = null,
@p_Date_To datetime = null
AS

DECLARE @query varchar(2000);
	SET @query = 'SELECT * FROM T_ANALYSER_RESULTS WHERE 1=1 '
	
	IF @p_AnalyserID is not null BEGIN
		set @query = @query + ' AND T_ANALYSER_RESULTS.AnalysersID = ' + Convert(varchar(30),@p_AnalyserID)
	END
	
	IF @p_Date_From is not null BEGIN
		set @query = @query + ' And T_ANALYSER_RESULTS.ResultDate > ''' + CONVERT(varchar(50),@p_Date_From) + ''''
	END;
	
	IF @p_Date_To is not null BEGIN
		set @query = @query + ' AND T_ANALYSER_RESULTS.ResultDate < ''' + CONVERT(varchar(50),@p_Date_To) + ''''
	END;
	PRINT @query
	EXEC(@query)

Posted 5-Dec-13 21:28pm

_ProgProg_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ganesh Raja · Accepted Answer · 2013-12-05T19:39:00

Solution 1

I have made some changes in the procedure..

SQL

Create PROCEDURE [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@p_AnalyserID INT,
@p_Date_From Datetime,
@p_Date_To Datetime
AS
DECLARE @query varchar(2000);
SET @query = 'SELECT * FROM T_ANALYSER_RESULTS WHERE T_ANALYSER_RESULTS.AnalysersID = ' + Convert(varchar(30),@p_AnalyserID)
IF @p_Date_From is not null
Begin 
set @query = @query + ' And T_ANALYSER_RESULTS.ResultDate >' + convert(varchar(50),@p_Date_From)
END
IF @p_Date_To is not null
Begin 
set @query = @query + ' And T_ANALYSER_RESULTS.ResultDate < ' + convert(varchar(50),@p_Date_To)
END
Print(@query)
EXEC(@query)

Note: if the parameter data not available , u should pass "NULL" at least(input to procedure)

Posted 5-Dec-13 19:39pm

Ganesh Raja

Comments

_ProgProg_ 6-Dec-13 2:33am

Thanks Ganesh Raja For your answer
Now the Stored Procedure are free of synteax errors

ALTER PROCEDURE [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@p_AnalyserID INT,
@p_Date_From datetime = null,
@p_Date_To datetime = null
AS
DECLARE @query varchar(2000);
SET @query = 'SELECT * FROM T_ANALYSER_RESULTS WHERE T_ANALYSER_RESULTS.AnalysersID = ' + Convert(varchar(30),@p_AnalyserID)
IF @p_Date_From is not null BEGIN
set @query = @query + ' And T_ANALYSER_RESULTS.ResultDate > ' + CONVERT(varchar(50),@p_Date_From)
END;
IF @p_Date_To is not null BEGIN
set @query = @query + ' T_ANALYSER_RESULTS.ResultDate < ' + CONVERT(varchar(50),@p_Date_To)
END;

EXEC(@query)

But there is something else
after executing it without sending Date_From and Date_To
it executes successfully

but if send Date_From and Date_To it gives me error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.

here is the syntax of executing

DECLARE @return_value int

EXEC @return_value = [dbo].[SP_T_ANALYSER_RESULTS_GET_BY_AnalyserID_Date]
@p_AnalyserID = 1,
@p_Date_From = N'01/01/2013',
@p_Date_To = N'01/01/2014'

SELECT 'Return Value' = @return_value

NOW WHAT?!!!!

BulletVictim 6-Dec-13 2:41am

Ok this is just me assuming that you made a mistake here:
@p_Date_From = N'01/01/2013',
@p_Date_To = N'01/01/2014'
Is the "N" supposed to be there?

_ProgProg_ 6-Dec-13 2:44am

i am just clicking Right click on the stored procedure ans selects Execute and give it the parameters.
then it write this code not me

BulletVictim 6-Dec-13 3:01am

Have you tried declaring the @p_Date_From and @p_Date_To without the NULL?