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…
[code language=”sql”]
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
[/code]
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.
http://stackoverflow.com/questions/3945361/t-sql-set-versus-select-when-assigning-variables
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;
[code language=”sql”]
SET @nCurRate = (
SELECT hourly_rate
FROM emp_cur_pay_rate
WHERE person_id = @nPerson_Id
AND company_code= @sCompanyCode
AND pos_code = @sPosCode)
[/code]
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!
