Using Case statement

Can i consolidate the following code.Does passing too many params to the sp effect performance.The if block can be consised usin for or between but they are being submitted from front end.<br />Thanks<br /><br />CREATE PROCEDURE INTFC <br />(<br />@DIWOIDdecimal(11,0),<br />@DILOANchar(35),<br />@DIDEPTchar(2),<br />@DIFORMchar(10),<br /> --have a total of 50 i/p params <br />)<br /><br />AS<br />SET NOCOUNT ON<br />DECLARE @PROCESS_IDINT<br />/* WILL NEED TO CHANGE THIS SOON */<br />SET @DIBNKNAM = SUBSTRING(@DIBNKNAM,1,2<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />SET @DIMNAME = SUBSTRING(@DIMNAME,1,2<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />SET @DICBNAME = SUBSTRING(@DICBNAME,1,40)<br />SET @DICBCNT = SUBSTRING(@DICBCNT,1,4<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />IF @SOURCE is NULL<br />BEGIN<br /><br />INSERT INTO<br />DBO.INSP (<br />DIWOID, <br />DILOAN, <br />DIDEPT, <br />DIBNKGRP, <br />DIFORM,<br />--have a total of 50 columns <br /><br />)<br />VALUES<br />(<br />@DIWOID,<br />@DILOAN,<br />@DIDEPT,<br />@DIFORM<br />--have a total of 50 columns <br />)<br /><br />RETURN <br />END<br />IF @SOURCE = 1<br />BEGIN<br />DECLARE @ERROR INT<br />DECLARE @ORDERTYPE varchar (10)<br /><br /><br />SET @ORDERTYPE = @DIFORM<br />SET @PROCESS_ID = @DICOID<br /><br />IF @DILTYPE = '1' SET @DILTYPE='FHA'<br />IF @DILTYPE = '2' SET @DILTYPE='VA'<br />IF @DILTYPE = '3' SET @DILTYPE='CONV'<br />IF @DILTYPE = '4' SET @DILTYPE='UNIN'<br /><br /><br />IF @DIFORM ='1' SET @DIFORM = 'I'<br />IF @DIFORM ='2' SET @DIFORM = 'I'<br />IF @DIFORM ='3' SET @DIFORM = 'I'<br />IF @DIFORM ='4' SET @DIFORM = 'I'<br />IF @DIFORM ='5' SET @DIFORM = 'I'<br />IF @DIFORM ='6' SET @DIFORM = 'I'<br />IF @DIFORM ='7' SET @DIFORM = 'I'<br />IF @DIFORM ='8' SET @DIFORM = 'I'<br />IF @DIFORM ='9' SET @DIFORM = 'I'<br />IF @DIFORM ='10' SET @DIFORM = 'I'<br />IF @DIFORM ='11' SET @DIFORM = 'I'<br />IF @DIFORM ='12' SET @DIFORM = 'I'<br />IF @DIFORM ='13' SET @DIFORM = 'I'<br />IF @DIFORM ='14' SET @DIFORM = 'I'<br />IF @DIFORM ='15' SET @DIFORM = 'I'<br />IF @DIFORM ='16' SET @DIFORM = 'P'<br />IF @DIFORM ='17' SET @DIFORM = 'P'<br />IF @DIFORM ='18' SET @DIFORM = 'P'<br />IF @DIFORM ='19' SET @DIFORM = 'P'<br />IF @DIFORM ='20' SET @DIFORM = 'P'<br />IF @DIFORM ='21' SET @DIFORM = 'P'<br />IF @DIFORM ='22' SET @DIFORM = 'P'<br />IF @DIFORM ='23' SET @DIFORM = 'P'<br />IF @DIFORM ='24' SET @DIFORM = 'P'<br />IF @DIFORM ='25' SET @DIFORM = 'P'<br />IF @DIFORM ='26' SET @DIFORM = 'P'<br />IF @DIFORM ='27' SET @DIFORM = 'P'<br />IF @DIFORM ='28' SET @DIFORM = 'P'<br />IF @DIFORM ='29' SET @DIFORM = 'P'<br />IF @DIFORM ='31' SET @DIFORM = 'P'<br />IF @DIFORM ='32' SET @DIFORM = 'P'<br />IF @DIFORM ='33' SET @DIFORM = 'P'<br />IF @DIFORM ='34' SET @DIFORM = 'P'<br />IF @DIFORM ='35' SET @DIFORM = 'P'<br />IF @DIFORM ='36' SET @DIFORM = 'P'<br />IF @DIFORM ='37' SET @DIFORM = 'P'<br />IF @DIFORM ='38' SET @DIFORM = 'P'<br />IF @DIFORM ='39' SET @DIFORM = 'P'<br />IF @DIFORM ='40' SET @DIFORM = 'P'<br />IF @DIFORM ='41' SET @DIFORM = 'P'<br />IF @DIFORM ='42' SET @DIFORM = 'P'<br />IF @DIFORM ='43' SET @DIFORM = 'P'<br />IF @DIFORM ='44' SET @DIFORM = 'P'<br />ELSE SET @DIFORM = @DIFORM<br /><br /><br />IF (@DIJOB IS NULL ) AND (@SOURCE = 1)<br />BEGIN<br /> SET @DIJOB =(SELECT ISNULL(MAX(JOB_NBR),0) + 1 FROM ORDERS(NOLOCK) WHERE WORK_ORDER_ID =@DIWOID) <br /><br /><br /><br />END<br /><br /><br />INSERT INTO<br />DBO.INSP <br />(<br />DIWOID, <br />DILOAN, <br />DIDEPT, <br />DIBNKGRP, <br />DIFORM<br />--have a total of 50 columns <br /><br />)<br />VALUES<br />(<br />@DIWOID,<br />@DILOAN,<br />@DIDEPT,<br />@DIFORM<br />--have a total of 50 columns <br />)<br />--EXEC P_rd @PROCESS_ID <br />RETURN <br /><br />END<br /><br />GO<br /><br />

A lot of what you are trying to do should be handled by the frontend application, especially setting @DIFORM and @DILTYPE. You can easily convert that before uploading to the database instead of doing it via the SProc. Also, passing parameters should not adversely affect your SP by any means, but you can always test it to verify. If you are adamant about using the SProc, I would at least change the "IF @DIFORM" portion to use the "IN" clause:

EX:
IF @DIFORM IN ('1','2','3','4','5','6','7','8','9') SET @DIFORM = 'I'
.
.
.
etc ...

This will make it more "concise"/

- Tahsin

Frank this piece of code looks much better in implementing
thanks