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
Go to Top of Page

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!
Go to Top of Page

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
Go to Top of Page

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'
Go to Top of Page

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"
Go to Top of Page

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

Go to Top of Page

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"
Go to Top of Page

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?
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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"
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -