CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
veix
veix

Reputation: 39

Multiple repeated IF statements vs single IF with loop in stored procedure

I have following IF statement in my stored procedure:

IF @parameter2 IS NOT NULL
    BEGIN
        -- delete the existing data
        DELETE FROM @tab

        -- fetch data to @tab
        INSERT INTO @tab EXECUTE sp_executesql @getValueSql, N'@parameter nvarchar(MAX)', @parameter2
        SET @value2 = (SELECT * FROM @tab)

        IF @value2 = @parameter2
            RETURN 5
    END
ELSE
    RETURN 5

This is to check if the @parameter2 value already exists in the database. Now the trouble I have is that I have to do this for up to 10 parameters. I am wondering If it would be faster to just copy the statement and repeat the code for all the possible parameters. That would mean that I have 10 almost identical IF statements. The other option I see possible is inserting all the parameters to @tab and loop through them with CURSOR or WHILE. I am concerned about the speed of looping because as far as I know they are pretty slow.

Upvotes: 0

Views: 100

Answers (1)

George Menoutis
George Menoutis

Reputation: 7250

As mentioned in comments, it would be about the same.

Indeed cursors are slow. But the reasons why they are slow is not avoided if you type a multitude of IFs in your code. You are essentially using a cursor in an "unrolled form"; The problems stay the same: all execution plans of your procedure will be re-created, and the optimizer will not have the chance to use a better, set-based plan.

So, your options are:

  1. Either use a cursor of a multitude of IFs. For the sake of readability and ease, I would recommend the cursor. The performance will be the same
  2. If possible, re-write the code of @getValueSql to support operating not on one value for @parameterN, but rather on a parameter table which will have N rows, one for each parameter value you're interested in. This is probably hard or impossible to do, but the only way to increase performance.

However, I should also mention that the cursor drawbacks won't be very noticeable on just 10 iterations, except maybe if you have exceptinally complex and nested queries. Remember, "Premature optimization is the root of all evil". The most probable thing is you don't need to worry about this.

Upvotes: 1

Related Questions