SQL Server Performance

Using Case statement

Discussion in 'T-SQL Performance Tuning for Developers' started by EasySQL, Mar 23, 2006.

  1. EasySQL New Member

    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 />
  2. Tahsin New Member

    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
  3. dineshasanka Moderator

  4. FrankKalis Moderator

  5. EasySQL New Member

    Frank this piece of code looks much better in implementing
    thanks

Share This Page