First, let’s define a stored procedure for those who don’t already know. A stored procedure is basically a query that you would run frequently that you have saved. The power is that you can have input parameters. For example, if you always execute this query:
Select * From Companies where number_of_employees
…frequently and the number 100 changes each time, it becomes cumbersome to type this out every time. With a store procedure this cumbersome query turns into this:
execute Comp_str_proc 100 where “Comp_str_proc”
is the name of our new stored procedure and 100 is the input parameter. To create this procedure just type the following and execute it:
create procedure Comp_str_prc @num_of_emps as select * from Companies where number_of_employees
Easy as 1,2,3!
Ok, back to my original subject, cursors. Another problem with the cumbersome repetitive queries is that sometimes you can’t update a table like you want to with only a simple update command. If there are many conditions that you want met before the update continues or if you need to create your own primary key value based on existing rows, then a simple update command won’t work. It won’t work even if it is in a stored procedure. You need a cursor. A cursor is a result set of a query. You can read this information one row at a time into variables. Then, you can manipulate these variables and put them back into the table. This allows you to make complex updates to rows in a table that is otherwise impossible in a simple update command.
For example, say I have a table named “people” with an id field and name field that contains only unique names. Also, let’s say that the table might be empty, or it might have rows already in it. What I want is a stored procedure that will take only unique values out of a predefined table that could possibly have duplicates, and insert them into the people table. Some of the obvious problems are that I have to pull out only the distinct names from the predefined table, check the people table to see if there are rows already, and if so, what id value am I at so far. This can be done without much sweat inside the stored procedure. One problem that might not be so obvious is how to I check to see if a name pulled out of the predefined table is already in the people table, and if so, not update the table. We can solve these problems with a cursor in a stored procedure relatively easy that would otherwise be impossible in a single update command.
To illustrate the T-SQL code that solves this problem I will just paste it below and number each line to elaborate on.
1 create procedure unique_name_into_people as
2 declare @name_exists int
3 declare @max int
4 declare @count int
5 declare @namestring varchar(255)
6 declare @fetchstatus2 int
7 declare distinctname cursor for
8 select distinct name
9 from “Predefined Table”
10 for read only
11 12 open distinctname
13 14 set @name_exists = (select count(*) from people)
15 if (@name_exists > 0)
16 begin
17 set @max = (select max(id)
18 from people)
19 set @count = @max
20 end
21 else
22 set @count = 0
23 24 while (0=0 ) Begin
25 fetch next
26 from distinctname
27 into @namestring
28 set @count = @count + 1
29 set @name_exists = (select count(*)
30 from people
31 where name = @namestring)
32 if (@name_exists > 0)
33 set @count = @count – 1
34 else
35 begin
36 insert into people
37 (id,
38 name)
39 values (@count, @namestring)
40 end
41 if (@@fetch_status 0) break
42 end –while
43 close distinctname
44 deallocate distinctname
Lines 1-6 just declare variables for us to use later in the procedure.
Lines 7-12 declare our cursor that we need to get each row’s value.
Line 7 and 10 are the syntax for the declaration and lines 8 and 9 are what you want in the cursor.
Line 10 can be “for read only”, “for update of ‘field_name'”, etc.
I usually choose “for read only”, that way I don’t accidentally mess up my predefined table. Sometimes it’s necessary though. Now, declaring the cursor only allocates the memory. It doesn’t actually execute the select until you do line 12.
Line 14 checks to see if the people table is empty or has rows in it.
Lines 15-22 set @count to the appropriate id value for the next row in the people table. If the people table is empty, that value is 0, otherwise it is the maximum value of the ids that are currently in the table. The @count value is set exactly to zero or exactly to @max because in the loop on line 28 it is incremented by one appropriately.
Now, on lines 25-27 we see some more cursor stuff. Line 12 opened the cursor. Line 25 fetches the first row in the result set (cursor) and puts that value into the variable @namestring. The number of variables is dependent on how many fields you selected when you declared the cursor on lines 8 and 9. Since I only selected one field I only need one variable here.
Line 29 checks to see if the value of @namestring already exists in the people table.
Lines 32-40 basically say, if a name already exists in the people table then decrement count by one and that’s it, otherwise put the values @count and @namestring into the people table.
Line 41 is how we get out of the while loop. The @@fetch_status variable is the return code of the fetch next command on line 25. If it is a 1 then the cursor has been traversed completely.
Line 43 closes the cursor and line 44 de-allocates the space that was allocated when you declared the cursor on lines 7-10. This stored procedure has been extremely useful to me in updating my tables with unique values from an external source. It took me awhile to iron out all the small things, but it works like a charm now.
I hope you get as much use out of this code as I do. Thanks for reading and if you have any questions or improvements they are welcome.
Nick Pile is an Murdok staff writer
