Help needed in a stored procedure

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

New to SQL Server Programming

Help needed in a stored procedure

Author

Topic

sravz
Starting Member

42 Posts

Posted - 2011-05-17 : 15:15:49

Anyone Help Please

I Had written a stored procedure for my search page
ALTER PROCEDURE [dbo].[sp_CompanySearchSelect]
@nName Varchar(200),
@nField Varchar(100)=NULL
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY

IF(@nField='Client Name')
BEGIN
SELECT @nName='%'+RTRIM(@nName)+ '%';
SELECT NAME,STATUSTYPE,CITY,STATE1,ZIP FROM
CompanyRegistration
WHERE
NAME LIKE @nName
AND COMPANYTYPE='Client'
END
ELSE IF(@nField='Supplier Name')
BEGIN
SELECT @nName='%'+RTRIM(@nName)+ '%';
SELECT NAME,STATUSTYPE,CITY,STATE1,ZIP FROM
CompanyRegistration
WHERE
NAME LIKE @nName
AND COMPANYTYPE='Supplier'
END
ELSE IF(@nField ='Status')
BEGIN
SELECT @nName='%'+RTRIM(@nName)+ '%';
SELECT NAME,STATUSTYPE,CITY,STATE1,ZIP FROM
CompanyRegistration
WHERE
STATUSTYPE LIKE @nName
END
ELSE IF(@nField='NULL' AND @nName<>'NULL')
BEGIN
SELECT @nName='%'+RTRIM(@nName)+ '%';
SELECT NAME,STATUSTYPE,CITY,STATE1,ZIP FROM
CompanyRegistration
WHERE NAME LIKE @nName
END
ELSE
BEGIN
SELECT NAME,STATUSTYPE,CITY,STATE1,ZIP FROM
CompanyRegistration

END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage
END CATCH
END

It gave me the results I want .But is this correct way to get the results.Or is there any other way I can remove the Repeated statments.
Suggestions please.
My table looks like this

ID int
NAME varchar(200)
ADD1 varchar(200)
ADD2 varchar(200)
CITY varchar(200)
STATE1 varchar(2)
ZIP varchar(20)
COUNTRY varchar(3)
COMMENT text
SCHEDULERNOTESUTS text
SCHEDULERNOTESCLIENT text
COMPANYTYPE varchar(100)
STATUSTYPE varchar(50)

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-17 : 16:04:51

with such varying conditions like this, I think you should just create a dynamic query and use with recompile option.. optmizer will pick the plan that it thinks will work best for most scenarios ... the plans are different for each condition and you dont want optimizer to pick a wrong plan..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-05-17 : 16:07:25

And also, perhaps, denormalize further by moving the company name and type into a different table and mapping it back to the CompanyRegistration through some ID.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-17 : 16:34:34

how about using case...when not true just have the column equal itself...like



WHERE COMPANYTYPE = 
CASE	WHEN @nField='Client Name'		THEN 'Client'
		WHEN @nField='Supplier Name'	THEN 'Supplier'
										ELSE COMPANYTYPE
END
AND STATUSTYPE LIKE
CASE	WHEN @nField ='Status'			THEN '%'+RTRIM(@nName)+ '%'
										ELSE STATUSTYPE
END
AND  
etc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sravz
Starting Member

42 Posts

Posted - 2011-05-17 : 21:51:31

Hey Dinakar,

Those tables are aalready created and used by existing system.So I cannot change them.Thanks though.Good to knowthat.

sravz
Starting Member

42 Posts

Posted - 2011-05-17 : 21:53:36

Hey Brett,

I will try that tom and let u know.

Thanks,
Sravz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources