de-slopifying SQL stored proc code (now indented)

Spacedog

When nesting if statements in stored procs in sql server is it possible to tidy this example, if seems that the select statement is basically the same for all cases with slight variations, can the if statements be put into the sealct itself or is there any other more approiate way of writing this proc? or does it matter at the end of the day when the proc' is compiled? I'm still new to SQL so I figure I'd better leard to be tidy now before I start to develop bad habits?

got the code tag groovin too! woop woop!!
________________________________________________

CREATE PROCEDURE spListUserDetails 
(
@IndexValue nvarchar(4),
@OrderValue nvarchar(20),
@Exclude numeric(4),
@PageID tinyint
)
AS

	-- FRSA Qs Onlt --

IF @PageID = 0
	IF @OrderValue = 'FirstName'
		IF @IndexValue = 'All'
			BEGIN
				SELECT
					UserRefNo,
					--UserName,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
				FROM
					tblLogonIDs
				WHERE
					UserRefNo <> @Exclude
				AND
					  UserRefNo <> 1
				ORDER BY
					UserFirstName
			END
		ELSE
			BEGIN
				SELECT
					UserRefNo,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
	
				FROM
					tblLogonIDs
				WHERE
					LEFT(UserFirstName,1) = @IndexValue
				AND
					UserRefNo <> @Exclude
				AND 	
					UserRefNo <> 1
				ORDER BY
					UserName
			END
	ELSE
		IF @IndexValue = 'All'
			BEGIN
				SELECT
					UserRefNo,
					--UserName,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
				FROM
					tblLogonIDs
				WHERE
					UserRefNo <> @Exclude
				AND
					UserRefNo <> 1
				ORDER BY
					UserSurname
			END
		ELSE
			BEGIN
				SELECT
					UserRefNo,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
	
				FROM
					tblLogonIDs
				WHERE
					LEFT(UserSurname,1) = @IndexValue
				AND
					UserRefNo <> @Exclude
				AND 	
					UserRefNo <> 1
				ORDER BY
					UserSurname
			END

ELSE IF @PageID = 1
	IF @OrderValue = 'FirstName'
		IF @IndexValue = 'All'
			BEGIN
				SELECT
					UserRefNo,
					--UserName,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
				FROM
					tblLogonIDs
				WHERE
					UserRefNo <> @Exclude
				AND
					  UserRefNo <> 1
				AND 
					DefaultPageID = 1
				ORDER BY
					UserFirstName
			END
		ELSE
			BEGIN
				SELECT
					UserRefNo,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
	
				FROM
					tblLogonIDs
				WHERE
					LEFT(UserFirstName,1) = @IndexValue
				AND
					UserRefNo <> @Exclude
				AND 	
					UserRefNo <> 1
				AND 
					DefaultPageID = 1
				ORDER BY
					UserName
			END
	ELSE
		IF @IndexValue = 'All'
			BEGIN
				SELECT
					UserRefNo,
					--UserName,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
				FROM
					tblLogonIDs
				WHERE
					UserRefNo <> @Exclude
				AND
					UserRefNo <> 1
				AND 
					DefaultPageID = 1
				ORDER BY
					UserSurname
			END
		ELSE
			BEGIN
				SELECT
					UserRefNo,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
	
				FROM
					tblLogonIDs
				WHERE
					LEFT(UserSurname,1) = @IndexValue
				AND
					UserRefNo <> @Exclude
				AND 	
					UserRefNo <> 1
				AND 
					DefaultPageID = 1
				ORDER BY
					UserSurname
			END

ELSE
	IF @OrderValue = 'FirstName'
		IF @IndexValue = 'All'
			BEGIN
				SELECT
					UserRefNo,
					--UserName,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
				FROM
					tblLogonIDs
				WHERE
					UserRefNo <> @Exclude
				AND
					  UserRefNo <> 1
				AND 
					DefaultPageID = 2
				ORDER BY
					UserFirstName
			END
		ELSE
			BEGIN
				SELECT
					UserRefNo,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
	
				FROM
					tblLogonIDs
				WHERE
					LEFT(UserFirstName,1) = @IndexValue
				AND
					UserRefNo <> @Exclude
				AND 	
					UserRefNo <> 1
				AND 
					DefaultPageID = 2
				ORDER BY
					UserName
			END
	ELSE
		IF @IndexValue = 'All'
			BEGIN
				SELECT
					UserRefNo,
					--UserName,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
				FROM
					tblLogonIDs
				WHERE
					UserRefNo <> @Exclude
				AND
					UserRefNo <> 1
				AND 
					DefaultPageID = 2
				ORDER BY
					UserSurname
			END
		ELSE
			BEGIN
				SELECT
					UserRefNo,
					UserFirstName + ' ' + UserSurName AS UserName,
					UserInstitute,
					UserID,
					DefaultPageID =
					CASE DefaultPageID
	         					WHEN 0 THEN 'Not Set'
	       					WHEN 1 THEN 'FSR'
						WHEN 2 THEN 'FSRA'
					END,
					CONVERT(varchar(12),DateAdded,106) AS DateAdded,
					UserStatus =  
					CASE UserStatus
	         					WHEN 0 THEN 'Inactive'
	       					WHEN 1 THEN 'Active'
					END
	
				FROM
					tblLogonIDs
				WHERE
					LEFT(UserSurname,1) = @IndexValue
				AND
					UserRefNo <> @Exclude
				AND 	
					UserRefNo <> 1
				AND 
					DefaultPageID = 2
				ORDER BY
					UserSurname
			END

RETURN
GO

_______________________________________

also how do you tab in boards posts?

Unpossible

I now understand why lecturers insist on indented code

bonkey

Originally posted by Spacedog
also how do you tab in boards posts?

wrap your code in [code] tags.

Once you've done that, trying to read your code mightn't scare me off so quickly

jc

Spacedog

all indented up now, what do you guys think, room for improvement?

Merrion

WHERE
            (
                LEFT(UserSurname,1) = @IndexValue
           OR
                @IndexValue = 'ALL'
            )

Should eliminate one inner nested IF level... can't think of a clean way to get rid of the IF @OrderValue = 'FirstName' though...