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…
DECLARE curTimeTH CURSOR FOR 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. BEGIN IF @nRate = 0 or @nRate is null BEGIN 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 BEGIN PRINT N'No rate for Person ID:' + cast(@nPerson_ID as varchar(6)) ; END ELSE BEGIN Update timeth set rate = @nCurRate WHERE row_id = @nRowID END END 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.
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.
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!