SQL Server Performance Forum – Threads Archive

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. Thanks CREATE PROCEDURE INTFC ( @DIWOIDdecimal(11,0), @DILOANchar(35), @DIDEPTchar(2), @DIFORMchar(10), –have a total of 50 i/p params ) AS SET NOCOUNT ON DECLARE @PROCESS_IDINT /* WILL NEED TO CHANGE THIS SOON */ SET @DIBNKNAM = SUBSTRING(@DIBNKNAM,1,2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> SET @DIMNAME = SUBSTRING(@DIMNAME,1,2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> SET @DICBNAME = SUBSTRING(@DICBNAME,1,40) SET @DICBCNT = SUBSTRING(@DICBCNT,1,4<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> IF @SOURCE is NULL BEGIN INSERT INTO DBO.INSP ( DIWOID, DILOAN, DIDEPT, DIBNKGRP, DIFORM, –have a total of 50 columns ) VALUES ( @DIWOID, @DILOAN, @DIDEPT, @DIFORM –have a total of 50 columns ) RETURN END IF @SOURCE = 1 BEGIN DECLARE @ERROR INT DECLARE @ORDERTYPE varchar (10) SET @ORDERTYPE = @DIFORM SET @PROCESS_ID = @DICOID IF @DILTYPE = ‘1’ SET @DILTYPE=’FHA' IF @DILTYPE = ‘2’ SET @DILTYPE=’VA' IF @DILTYPE = ‘3’ SET @DILTYPE=’CONV' IF @DILTYPE = ‘4’ SET @DILTYPE=’UNIN' IF @DIFORM =’1′ SET @DIFORM = ‘I' IF @DIFORM =’2’ SET @DIFORM = ‘I' IF @DIFORM =’3’ SET @DIFORM = ‘I' IF @DIFORM =’4’ SET @DIFORM = ‘I' IF @DIFORM =’5’ SET @DIFORM = ‘I' IF @DIFORM =’6’ SET @DIFORM = ‘I' IF @DIFORM =’7’ SET @DIFORM = ‘I' IF @DIFORM =’8’ SET @DIFORM = ‘I' IF @DIFORM =’9’ SET @DIFORM = ‘I' IF @DIFORM =’10’ SET @DIFORM = ‘I' IF @DIFORM =’11’ SET @DIFORM = ‘I' IF @DIFORM =’12’ SET @DIFORM = ‘I' IF @DIFORM =’13’ SET @DIFORM = ‘I' IF @DIFORM =’14’ SET @DIFORM = ‘I' IF @DIFORM =’15’ SET @DIFORM = ‘I' IF @DIFORM =’16’ SET @DIFORM = ‘P' IF @DIFORM =’17’ SET @DIFORM = ‘P' IF @DIFORM =’18’ SET @DIFORM = ‘P' IF @DIFORM =’19’ SET @DIFORM = ‘P' IF @DIFORM =’20’ SET @DIFORM = ‘P' IF @DIFORM =’21’ SET @DIFORM = ‘P' IF @DIFORM =’22’ SET @DIFORM = ‘P' IF @DIFORM =’23’ SET @DIFORM = ‘P' IF @DIFORM =’24’ SET @DIFORM = ‘P' IF @DIFORM =’25’ SET @DIFORM = ‘P' IF @DIFORM =’26’ SET @DIFORM = ‘P' IF @DIFORM =’27’ SET @DIFORM = ‘P' IF @DIFORM =’28’ SET @DIFORM = ‘P' IF @DIFORM =’29’ SET @DIFORM = ‘P' IF @DIFORM =’31’ SET @DIFORM = ‘P' IF @DIFORM =’32’ SET @DIFORM = ‘P' IF @DIFORM =’33’ SET @DIFORM = ‘P' IF @DIFORM =’34’ SET @DIFORM = ‘P' IF @DIFORM =’35’ SET @DIFORM = ‘P' IF @DIFORM =’36’ SET @DIFORM = ‘P' IF @DIFORM =’37’ SET @DIFORM = ‘P' IF @DIFORM =’38’ SET @DIFORM = ‘P' IF @DIFORM =’39’ SET @DIFORM = ‘P' IF @DIFORM =’40’ SET @DIFORM = ‘P' IF @DIFORM =’41’ SET @DIFORM = ‘P' IF @DIFORM =’42’ SET @DIFORM = ‘P' IF @DIFORM =’43’ SET @DIFORM = ‘P' IF @DIFORM =’44’ SET @DIFORM = ‘P' ELSE SET @DIFORM = @DIFORM IF (@DIJOB IS NULL ) AND (@SOURCE = 1) BEGIN SET @DIJOB =(SELECT ISNULL(MAX(JOB_NBR),0) + 1 FROM ORDERS(NOLOCK) WHERE WORK_ORDER_ID [email protected]) END INSERT INTO DBO.INSP ( DIWOID, DILOAN, DIDEPT, DIBNKGRP, DIFORM –have a total of 50 columns ) VALUES ( @DIWOID, @DILOAN, @DIDEPT, @DIFORM –have a total of 50 columns ) –EXEC P_rd @PROCESS_ID RETURN END GO

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

can’t you check @DIFORM < 10
use varchar for the parameters —————————————-
http://spaces.msn.com/members/dineshasanka

What about
SELECT @DIFORM =
CASE
WHEN @DIFORM <= 15 THEN ‘I’
WHEN @DIFORM BETWEEN 16 AND 44 THEN ‘P’
ELSE @DIFORM
END
Why have you declared @DIFORM as char(10)? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Frank this piece of code looks much better in implementing
thanks

]]>