Get Set, Get Select, Which One Do I Use in SQL?

If you are like me I frequently use cursors in sql, but the other day I had created a piece of code like below. Before telling me I could do this a different way, I want to show you what I found.–Go with me…

      SELECT person_id, earn_code, rate, pos_code, company_code
          FROM timeth th
          INNER JOIN (SELECT earn_code,alpha_value, company_code
                      FROM tblearnu
                      WHERE field_code='USE_CURRENT_RATE_TIMETH' and upper(alpha_value)='Y') eu
            ON th.company_code = eu.company_code and th.earn_code = eu.earn_code
          ORDER by person_id, earn_code

          OPEN curTimeTH
          FETCH next from curTimeTH INTO @nPerson_Id, @sEarnCode, @nRate, @sPosCode,
                                         @sCompanyCode, @dtTimeCardDate, @nRowID
          WHILE @@fetch_status = 0  --Loop through as long as there is no errors.
              IF @nRate = 0 or @nRate is null
                 SELECT @nCurRate=(Hourly_Rate
                           FROM emp_cur_pay_rate
                       WHERE person_id = @nPerson_Id
                        AND company_code= @sCompanyCode
                        AND pos_code = @sPosCode)
                If @nCurRate = 0 --Did not find a matching record for time entry
                      PRINT N'No rate for Person ID:' + cast(@nPerson_ID as varchar(6)) ;
                      Update timeth set rate = @nCurRate
                      WHERE row_id = @nRowID

           FETCH next from curTimeTH INTO  @nPerson_Id, @sEarnCode, @nRate, @sPosCode,
                                           @sCompanyCode, @dtTimeCardDate, @nRowID
          END  -- WHILE @@fetch_status = 0

          CLOSE curTimeTH
          DEALLOCATE curTimeTH

I knew of some of the differences between select and set but had never encountered this. I realized that on some records the hourly_rate would be 0 but it was still updating with a real value as if it found it. How could this be?
After spending some time trying to look at my sql statement and investigating online I read this from.

When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it’s previous value)

Wow, I never thought this would potentially cause a major problem.  I had to go back and look at my other sql code to make sure that there never was an opportunity for this to slip through and cause a problem that I would not catch.  Thankfully I can rest at night knowing that my other code was fine.  I changed my line of code (line 17) to read like this;

SET @nCurRate = (
             SELECT hourly_rate
             FROM emp_cur_pay_rate
             WHERE person_id = @nPerson_Id
             AND company_code= @sCompanyCode
             AND pos_code = @sPosCode)

I then started reading about the other issues and differences between set and select. I have compiled a list below to help decide when to use set or select and as for me, I think I will make it a habit to use SET from now on.

1. SET is the ANSI standard for assigning variables and SELECT is not.
2. SET can only assign one variable at any one time while SELECT can create multiple variable assignments at the same time.
3. There does not seem to be any speed differences when it comes to assigning the variable (This changes if you have to assign multiple variables from the same source)
4. SET can only assign a scalar value. If you try and assign multiple values on the same SET you will get an error. A SELECT will randomly pick one value and assign it. This might be mitigated by structuring your select statement different and use DISTINCT.

If you know of more than drop me a note and I will add them to the bottom of this list.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *

5 × = 5

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>