Optimizing store proc

I have the following store proc and was wondering if I can optimized it by using a SELECT CASE instead of all those IF? I tried but don't know how to write it.

Thanks

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[Get_Cl_SearchMultiColumn]( @.strSearchTermColumnNamenvarchar (50),@.strSearchTermSearchTermnvarchar (200) )as if (@.strSearchTermColumnName ='Monitor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2FROM Cl_SystemsWHERE contains(Monitor1,@.strSearchTerm)orcontains(Monitor2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='MonitorSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2SerialFROM Cl_SystemsWHERE contains(Monitor1Serial,@.strSearchTerm)orcontains(Monitor2Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Microscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Microscope1,Microscope2FROM Cl_SystemsWHERE contains(Microscope1,@.strSearchTerm)orcontains(Microscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialMicroscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialMicroscope1,SerialMicroscope2FROM Cl_SystemsWHERE contains(SerialMicroscope1,@.strSearchTerm)orcontains(SerialMicroscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Controller')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Controller1,Controller2FROM Cl_SystemsWHERE contains(Controller1,@.strSearchTerm)orcontains(Controller2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ControllerFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Cont1Firmware,Cont2FirmwareFROM Cl_SystemsWHERE contains(Cont1Firmware,@.strSearchTerm)orcontains(Cont2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialController')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialController1,SerialController2FROM Cl_SystemsWHERE contains(SerialController1,@.strSearchTerm)orcontains(SerialController2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Joystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joystick1,Joystick2FROM Cl_SystemsWHERE contains(Joystick1,@.strSearchTerm)orcontains(Joystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='JoystickFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joy1Firmware,Joy2FirmwareFROM Cl_SystemsWHERE contains(Joy1Firmware,@.strSearchTerm)orcontains(Joy2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialJoystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialJoystick1,SerialJoystick2FROM Cl_SystemsWHERE contains(SerialJoystick1,@.strSearchTerm)orcontains(SerialJoystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Camera')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4FROM Cl_SystemsWHERE contains(Camera1,@.strSearchTerm)orcontains(Camera2,@.strSearchTerm)orcontains(Camera3,@.strSearchTerm)orcontains(Camera4,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='CameraSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1Serial,Camera2Serial,Camera3Serial,Camera4SerialFROM Cl_SystemsWHERE contains(Camera1Serial,@.strSearchTerm)orcontains(Camera2Serial,@.strSearchTerm)orcontains(Camera3Serial,@.strSearchTerm)orcontains(Camera4Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ZMotor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3FROM Cl_SystemsWHERE contains(ZMotor1,@.strSearchTerm)orcontains(ZMotor2,@.strSearchTerm)orcontains(ZMotor3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Stage')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3FROM Cl_SystemsWHERE contains(Stage1,@.strSearchTerm)orcontains(Stage2,@.strSearchTerm)orcontains(Stage3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Lens')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Lens1,Lens2,Lens3FROM Cl_SystemsWHERE contains(Lens1,@.strSearchTerm)orcontains(Lens2,@.strSearchTerm)orcontains(Lens3,@.strSearchTerm)return 0end

I don't know why you need a WHERE clause with CONTAINS predicate but try the link below for CASE statement for an expert. Hope this helps.

http://www.craigsmullins.com/ssu_0899.htm

|||

Well it seems that I am trying to use the CASE in a different situation then what explain everywhere.

I would like to use it one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTermColumnName

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

Oups I made a few mistakes in my last statement, I should have been:

Well it seems that I am trying to use the CASE in a different situation than what explained everywhere.

I would like to use it on one of the parameters sent to my store proc but I am not sure if it is possible and how to write it

Something like:

Select Case @.strSearchTerm

Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end

case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...

End Select

|||

What I am saying is I don't know if you can use the CONTAINS predicate with CASE statement. The links below one is a full text expert he knows more about fulltext and the other two are the two versions of CONTAINS. Hope this helps.

http://spaces.msn.com/jtkane/

http://msdn2.microsoft.com/en-US/library/ms189760.aspx

http://msdn2.microsoft.com/en-US/library/ms187787.aspx

sql

opinion on preventing duplicate record insertion

Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this articlehttp://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005

I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit...thanks ..

You essentially have two points of validation for the duplication.. at the code level, and at the database level.
You should employ both to good effect so that you do not get any and more importantly you control what happens when it already exists.

Unique constraints work - if you've set the correct ones, you will never get a duplicate entry in the database.
You should make sure you have them set.

Next, on a stored procedure that inserts the record, you can either try to insert and an error will occur because of the constraint and a SqlException will be generated that will bubble back to your code.
OR, you can use the IF EXISTS code that is suggested in the link you sent.
This checks if those valuse already exists in the database and doesn't try the insert.
I would advise that you also apply this to your logic.

On the code level, you can store Session or Viewstate Variables to flag that the update has happened as suggested in the fourth page of that article.

So you can incorporate all three so that

1. It checks the session variable and doesn't allow for duplicates
2. If it "slips the net" here, OR you later change your UI code, the stored procedure IF EXISTS catches and handles
3. If logic later changes, different sp used, your Unique Constraint will eventualy catch and throw an exception.

You are ensuing at all points that the problem cannot occur.

|||

"unique constraints" are reliable - the problem is handling the duplication condition. In my opinion the first method in the article is the preferred method - that of detecting the duplicate before the constraint does.