STORED PROCEDURES - IF THEN STATEMENTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

STORED PROCEDURES - IF THEN STATEMENTS

Author

Topic

Kickaboo
Starting Member

10 Posts

Posted - 2008-02-21 : 17:01:37

Hi,

This is driving me up the wall,

I have used SQL for donkey's years, but never really used Stored Procedures, well, I am starting to migrate functions from a front end ASP.Net system, and utilising the stored procedures.

I have a problem with something I thought would be really basic, i am trying to use IF THEN statements within the stored procedure to change the WHERE elements based on the parameters passed to it.

Here is the script (copied from the modification screen)

USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/21/2008 21:50:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@Search VarChar(100)
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Status.Status, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Status ON dbo.Members.EntryID = dbo.ActiveMember_Status.UserID LEFT OUTER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
IF (@Search='Dowle')
WHERE (dbo.Members.Username = @Search) OR
(dbo.Members.Forename = @Search) OR
(dbo.Members.Surname = @Search) OR
(dbo.ActiveMember_Mobile.Value = @Search) OR
(dbo.ActiveMember_Email.Value = @Search) OR
(dbo.ActiveMember_Location.Location1 = @Search) OR
(dbo.ActiveMember_Location.Location2 = @Search) OR
(dbo.ActiveMember_Location.Location3 = @Search) OR
(dbo.ActiveMember_Location.Location4 = @Search)
END IF

ORDER BY dbo.Members.Username

The bit I am trying to do above isn't real, but the same error appears every which way I try.

Msg 156, Level 15, State 1, Procedure USERS_LIST, Line 14
Incorrect syntax near the keyword 'WHERE'.

What am i doing wrong?

Thanks

David

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-21 : 17:54:10

you can't put if in the sql statement.
try using CASE WHEN construct.
or for complex searches try using dynamic sql.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kickaboo
Starting Member

10 Posts

Posted - 2008-02-28 : 16:29:51

HI,

thanks for this, the CASE construct dosn't work either!

Its odd, cause CASE works for every other section of the SQL statement, but when I use it in the WHERE clause I get an error that the syntax is incorrect near = in the clause

What i was trying to do was create one list of all members, and related info from various areas of the system, which would handle different queries from different areas of the system, so the where would be very different dependant on what area of the system was calling the procedure!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-28 : 16:31:31

well show us your sql

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-28 : 16:38:09

Try using this WHERE clause:

WHERE   ( @Search = 'Dowle'
          AND ( dbo.Members.Username = @Search )
          OR ( dbo.Members.Forename = @Search )
          OR ( dbo.Members.Surname = @Search )
          OR ( dbo.ActiveMember_Mobile.Value = @Search )
          OR ( dbo.ActiveMember_Email.Value = @Search )
          OR ( dbo.ActiveMember_Location.Location1 = @Search )
          OR ( dbo.ActiveMember_Location.Location2 = @Search )
          OR ( dbo.ActiveMember_Location.Location3 = @Search )
          OR ( dbo.ActiveMember_Location.Location4 = @Search )
        )
        OR @Search <> 'Dowle'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:56:20

[code]ALTER Procedure dbo.USERS_LIST
(
@Search VARCHAR(100)
)
AS

SET NOCOUNT ON

SELECT m.EntryID,
m.EntryDate,
m.Username,
m.Forename,
m.Surname,
m.Gender,
m.DateOfBirth,
m.LastAction,
m.AdminUser,
ams.Status,
amm.Value AS Mobile,
ame.Value AS Email,
aml.Location1,
aml.Location2,
aml.Location3,
aml.Location4,
dbo.F_AGE_IN_YEARS(m.DateOfBirth, GETDATE()) As Age
FROM dbo.Members AS m
INNER JOIN dbo.ActiveMember_Status AS ams ON ams.UserID = m.EntryID
LEFT JOIN dbo.ActiveMember_Location AS aml ON aml.UserID = m.EntryID
LEFT JOIN dbo.ActiveMember_Email AS ame ON ame.UserID = m.EntryID
LEFT JOIN dbo.ActiveMember_Mobile AS amm ON amm.UserID = m.EntryID
WHERE @Search IS NULL
OR @Search IN (m.Username, m.Forename, m.Surname, amm.Value, ame.Value = @Search)
OR @Search IN (aml.Location1, aml.Location2, aml.Location3, aml.Location4)[/code]

E 12°55'05.25"
N 56°04'39.16"

Kickaboo
Starting Member

10 Posts

Posted - 2008-02-28 : 16:58:31

Show me your SQL? Your such a romantic!

ok, i'll show you!

USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/28/2008 21:51:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@UserID Int, @Username varchar(100), @Surname varchar(100), @DOB VarChar(40), @Location varchar(100), @Email Varchar(100), @Mobile varchar(100), @STYPE Int
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Status.Status, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Status ON dbo.Members.EntryID = dbo.ActiveMember_Status.UserID LEFT OUTER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
WHERE
CASE WHEN @STYPE = '1' Then
(dbo.Members.EntryID = @UserID) or
(dbo.Members.Username = @Username) or
(dbo.Members.Surname = @surname) or
(dbo.Members.DateofBirth = Convert(datetime, @DOB)) or
(dbo.ActiveMember_Location.Location2 = @Location) or
(dbo.ActiveMember_Location.Location3 = @Location) or
(dbo.ActiveMember_Location.Location4 = @Location) or
(dbo.ActiveMember_Email.value = @Email) or
(dbo.ActiveMember_Mobile.value = @Mobile)
End

ORDER BY dbo.Members.Username

The problem is somewhere around the CASE and the error is

Msg 170, Level 15, State 1, Procedure USERS_LIST, Line 15
Line 15: Incorrect syntax near '='.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:27:47

Here we go again

ALTER PROCEDURE	dbo.USERS_LIST
(
	@UserID INT,
	@Username VARCHAR(100),
	@Surname VARCHAR(100),
	@DOB VARCHAR(40),
	@Location VARCHAR(100),
	@Email VARCHAR(100),
	@Mobile VARCHAR(100),
	@sType INT
)
AS

SET NOCOUNT ON

SELECT		m.EntryID,
		m.EntryDate,
		m.UserName,
		m.ForeName,
		m.SurName,
		m.Gender,
		m.DateOfBirth,
		m.LastAction,
		m.AdminUser,
		ams.Status,
		amm.Value AS Mobile,
		ame.Value AS Email,
		aml.Location1,
		aml.Location2,
		aml.Location3,
		aml.Location4,
		dbo.F_AGE_IN_YEARS(m.DateOfBirth, GETDATE()) As Age
FROM		dbo.Members AS m
INNER JOIN	dbo.ActiveMember_Status AS ams ON ams.UserID = m.EntryID
LEFT JOIN	dbo.ActiveMember_Location AS aml ON aml.UserID = m.EntryID
LEFT JOIN	dbo.ActiveMember_Email AS ame ON ame.UserID = m.EntryID
LEFT JOIN	dbo.ActiveMember_Mobile AS amm ON amm.UserID = m.EntryID
WHERE		@sType <> 1
		OR m.EntryID = @UserID
		OR m.UserName = @UserName
		OR m.SurName = @SurName
		OR m.DateOfBirth = CONVERT(DATETIME, @DOB)
		OR @Location IN (aml.Location1, aml.Location2, aml.Location3, aml.Location4)
		OR ame.Value = @Email
		OR amm.Value = @Mobile
ORDER BY	m.UserName

E 12°55'05.25"
N 56°04'39.16"

Kickaboo
Starting Member

10 Posts

Posted - 2008-02-28 : 17:33:41

HI Peso,

Thanks for that, but its not quite what I am looking for

@stype is the type of search the user is conducting, there will be a series of them.

But, if its Stpye 1, then it needs to invoke that WHERE clause.

Stype 2, will use the same set of data, but have a different WHERE clause to it as well.

I think, from what I can see, the solution you have above uses Stype as part of the Where Clause, and not part of the CASE?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:37:54

This is fun! We could go on playing guessing games all night... Oh wait! You just wrote some more vital piece of information about your business rules.

WHERE		@sType = 1 AND	(	m.EntryID = @UserID
					OR m.UserName = @UserName
					OR m.SurName = @SurName
					OR m.DateOfBirth = CONVERT(DATETIME, @DOB)
					OR @Location IN (aml.Location1, aml.Location2, aml.Location3, aml.Location4)
					OR ame.Value = @Email
					OR amm.Value = @Mobile)
			OR
		@sType = 2 AND	(	m.UserName = @UserName
					OR m.SurName = @SurName
					OR ame.Value = @Email
					OR amm.Value = @Mobile)
ORDER BY	m.UserName

E 12°55'05.25"
N 56°04'39.16"

Kickaboo
Starting Member

10 Posts

Posted - 2008-02-28 : 17:43:25

Hi Peso,

Thanks again, but thats still not quite what I mean,

Stype is not a field. Its a variable passed to the stored procedure. If Stype is 1, then the WHERE Clause is invoked. is Stype is 2, a totally different WHERE clause is invoked on the same dataset.

The example you are suggesting is that the STYPE is a field in the database that is part of the WHERE clause. It isn't.

What I am trying to do, is use CASE to determine the STYPE being sent to the stored procedure, then have the stored procedure apply a different WHERE clause.

D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:47:09

What? You can't read code?
Where have I written @sType is a column?

E 12°55'05.25"
N 56°04'39.16"

Kickaboo
Starting Member

10 Posts

Posted - 2008-02-28 : 17:51:42

SOrry, I appologies, the penny just dropped on how it was being structured! You are a star!

D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources