Help with IF select Else select statement

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with IF select Else select statement

Author

Topic

ec300
Starting Member

3 Posts

Posted - 2007-02-27 : 16:45:19

Hi,

I am trying to create a procedure to use diffrent select statements based on usertype. The following code runs but dose not return any results. I have tryed the select statements by them self and they do return results.

If you see something wrong or have anouther way of doing this let me know.

Thanks


CREATE PROCEDURE spGetCom
/*
	(
		@pupUser nvarchar(50) = Null
		@pupUserType int =  Null
	)
*/
(@pupUser nvarchar(50),
@pupUserType int)

AS
If @pupUserType = 7
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL
End
Else If @pupUserType = 5
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL AND C.comUserGroup = @pupUserType OR C.comUserGroup = '10'
End
Else If @pupUserType = 3
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL AND C.comUserGroup = @pupUserType OR C.comUserGroup = '10' OR C.comUserGroup = '13'
End
Else If @pupUserType = 1
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL AND C.comUserGroup = @pupUserType OR C.comUserGroup = '10' OR C.comUserGroup = '13'
End

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-02-27 : 17:14:02

The methodology will work the way you have it. Make sure you are passing the variable correctly and that your query's work. My guess is you are not setting pupusertype. Try this


CREATE PROCEDURE spGetCom
@pupUser nvarchar(50),
@pupUserType int

AS
If @pupUserType = 7
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL
End
Else If @pupUserType = 5
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL AND C.comUserGroup = @pupUserType OR C.comUserGroup = '10'
End
Else If @pupUserType = 3
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL AND C.comUserGroup = @pupUserType OR C.comUserGroup = '10' OR C.comUserGroup = '13'
End
Else If @pupUserType = 1
Begin
SELECT C.comID, C.comDate, C.comHeading, C.comText, C.comStatus, C.comPriority
FROM tblCommunication C LEFT JOIN (SELECT * FROM tblCommunicationRead WHERE comReadUser = @pupUser) R ON C.comID = R.comID WHERE C.comStatus = 'Open' AND R.comID IS NULL AND C.comUserGroup = @pupUserType OR C.comUserGroup = '10' OR C.comUserGroup = '13'
End

exec spGetCom 'test',7

See if it executes the 7 query. If it doesn't you likley have a error with the select statment itself.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-27 : 17:16:52

There is no need to have these IF statements and different SELECT statements. You can just use one SELECT statement with a CASE statement in the WHERE clause.

Tara Kizer

ec300
Starting Member

3 Posts

Posted - 2007-02-28 : 09:14:13

Thanks for the comments

vinnie881, I will play with it some more.

thizer, I looked at the Case a little bit but was not sure how to use it in the case. Would you mind giving me an example.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 09:53:15

NO, IT WILL NOT WORK CORRECT!
You have overseen the effect of "OPERATOR PRECEDENSE".

CREATE PROCEDURE spGetCom
(
	@pupUser nvarchar(50) = NULL
	@pupUserType int = NULL
)
AS

IF @pupUserType = 7
	SELECT		C.comID,
			C.comDate,
			C.comHeading,
			C.comText,
			C.comStatus,
			C.comPriority
	FROM		tblCommunication AS C
	LEFT JOIN	tblCommunicationRead AS R ON R.comReadUser = @pupUser AND R.comID = C.comID
	WHERE		C.comStatus = 'Open'
			AND R.comID IS NULL

IF @pupUserType = 5
	SELECT		C.comID,
			C.comDate,
			C.comHeading,
			C.comText,
			C.comStatus,
			C.comPriority
	FROM		tblCommunication AS C
	LEFT JOIN	tblCommunicationRead AS R ON R.comReadUser = @pupUser AND R.comID = C.comID
	WHERE		C.comStatus = 'Open'
			AND R.comID IS NULL
			AND C.comUserGroup IN (@pupUserType, '10')

IF @pupUserType = 3
	SELECT		C.comID,
			C.comDate,
			C.comHeading,
			C.comText,
			C.comStatus,
			C.comPriority
	FROM		tblCommunication AS C
	LEFT JOIN	tblCommunicationRead AS R ON R.comReadUser = @pupUser AND R.comID = C.comID
	WHERE		C.comStatus = 'Open'
			AND R.comID IS NULL
			AND C.comUserGroup IN (@pupUserType, '10', '13')

IF @pupUserType = 1
	SELECT		C.comID,
			C.comDate,
			C.comHeading,
			C.comText,
			C.comStatus,
			C.comPriority
	FROM		tblCommunication AS C
	LEFT JOIN	tblCommunicationRead AS R ON R.comReadUser = @pupUser AND R.comID = C.comID
	WHERE		C.comStatus = 'Open'
			AND R.comID IS NULL
			AND C.comUserGroup IN (@pupUserType, '10', '13')

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 09:55:45

Why?

Example (@pupUserType = 5):
If C.comUserGroup = '10' then all records with this case will be returned, no matter what C.comStatus is!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 10:01:09

Here is a much shorter variant.

CREATE PROCEDURE spGetCom
(
	@pupUser NVARCHAR(50) = NULL
	@pupUserType INT = NULL
)
AS

SET NOCOUNT ON

SELECT		C.comID,
		C.comDate,
		C.comHeading,
		C.comText,
		C.comStatus,
		C.comPriority
FROM		tblCommunication AS C
LEFT JOIN	tblCommunicationRead AS R ON R.comReadUser = @pupUser AND R.comID = C.comID
WHERE		C.comStatus = 'Open'
		AND R.comID IS NULL
		AND 1 =	CASE
				WHEN @pupUserType = 7 THEN 1
				WHEN @pupUserType = 5 AND C.comUserGroup IN (@pupUserType, '10') THEN 1
				WHEN @pupUserType = 3 AND C.comUserGroup IN (@pupUserType, '10', '13') THEN 1
				WHEN @pupUserType = 1 AND C.comUserGroup IN (@pupUserType, '10', '13') THEN 1
				ELSE 0
			END

Peter Larsson
Helsingborg, Sweden

ec300
Starting Member

3 Posts

Posted - 2007-02-28 : 11:20:37

Peso,

That works, I used the last one w/Case. I also like the join better than the Select join (select)

Thanks all for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources