Help w/ refactoring repeated code

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 w/ refactoring repeated code

Author

Topic

fr0id
Starting Member

4 Posts

Posted - 2008-11-18 : 15:09:43

Below is a compact version of my rather lengthy SP. As you can see, the only difference between the code in the IF/ELSE blocks is the WHERE clause looks for IS NULL or IS NOT NULL depending on the value of @isRegistered.


CREATE PROCEDURE dbo.ProviderSearch	
	@firstName VARCHAR(50) = NULL
	@isRegistered BIT
AS
BEGIN
	IF(@isRegistered = 1) BEGIN
		SELECT TOP(10) *
		FROM dbo.Provider prv
		WHERE ((@firstName IS NULL OR @firstName = '') OR (prv.FirstName LIKE  @firstName + '%')) AND
			prv.AppUserID IS NULL
	END
	ELSE BEGIN
		SELECT TOP(10) *
		FROM dbo.Provider prv
		WHERE ((@firstName IS NULL OR @firstName = '') OR (prv.FirstName LIKE  @firstName + '%')) AND
			prv.AppUserID IS NOT NULL
	END
END

In addition to the problem of repeated code, I now need to actually bring back all of the rows in the Provider table regardless of the value of the AppUserID column. My initial thoughts are to do something like:


CREATE PROCEDURE dbo.ProviderSearch	
	@firstName VARCHAR(50) = NULL
	@isRegistered BIT = NULL
AS
BEGIN
	IF(@isRegistered IS NOT NULL) BEGIN
		IF(@isRegistered = 1) BEGIN
			SELECT TOP(10) *
			FROM dbo.Provider prv
			WHERE ((@firstName IS NULL OR @firstName = '') OR (prv.FirstName LIKE  @firstName + '%')) AND
				prv.AppUserID IS NULL
		END
		ELSE BEGIN
			SELECT TOP(10) *
			FROM dbo.Provider prv
			WHERE ((@firstName IS NULL OR @firstName = '') OR (prv.FirstName LIKE  @firstName + '%')) AND
				prv.AppUserID IS NOT NULL
		END
	END
	ELSE BEGIN
		SELECT TOP(10) *
		FROM dbo.Provider prv
		WHERE ((@firstName IS NULL OR @firstName = '') OR (prv.FirstName LIKE  @firstName + '%')) AND
	END
END

Is there a better way of doing this?

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-11-19 : 06:39:31

You can have all the IF ELSE checks in WHERE Clause. Similar to how you've used @firstName.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 06:51:37

[code]CREATE PROCEDURE dbo.ProviderSearch
@firstName VARCHAR(50) = NULL
@isRegistered BIT = NULL
AS
BEGIN
IF) BEGIN
IF(@isRegistered = 1) BEGIN
SELECT TOP(10) *
FROM dbo.Provider prv
WHERE ((@firstName IS NULL OR @firstName = '') OR (prv.FirstName LIKE @firstName + '%'))
AND ((prv.AppUserID IS NULL AND @isRegistered =1)
OR (prv.AppUserID IS NOT NULL AND @isRegistered =0)
OR @isRegistered IS NULL)[/code]

fr0id
Starting Member

4 Posts

Posted - 2008-11-20 : 11:59:21

Worked great. Thx! =)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 12:07:42

cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources