Loop through table variable using data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Loop through table variable using data

Author

Topic

beetle3379
Starting Member

11 Posts

Posted - 2010-10-15 : 16:31:34

Hello,

I am fairly new to writing stored procedures and am having a bit of trouble. I am used to Java and C so some of the syntax and etc is getting the better of me. My task is this:

I have performed a series of joins and dumped the results into a table variable. This table variable has 3 columns that represent an id, the first name and last name of a person.

What I need to do is loop through the table and with each row, use an insert stored procedure to accept the three items, store it and move on in the table.

Basically:

for (each item in the table)
storedprocedure(id(which will be a function),last,first)
end

Any help would be greatly appreciated!
If I have left out an important piece, I'm sure you'll let me know!
Thanks in advance!

Loren Bailey

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-15 : 16:33:20

You can use a WHILE loop or a cursor for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-15 : 17:16:41

If all you need for your stored procedure to do is to insert values from a table into another table, then you can do that with an single insert statement.

insert into MyOtherTable
	(
	id,
	firstname,
	lastname
	)
select
	a.id,
	a.firstname,
	a.lastname
from
	@MyTable a
order by
	a.id

If you really need to have a loop, then here is sample code using a cursor. You should read about cursors in SQL Server Books Online.


-- Sample cursor
declare MyCursor cursor local
for
select
	a.id,
	a.firstname,
	a.lastname
from
	@MyTable a
order by
	a.id

open MyCursor

declare @fetch_status	int
declare @id		int
declare @firstname	varchar(100)
declare @lastname	varchar(100)

select @fetch_status = 0

while @fetch_status = 0
	begin

	fetch next from MyCursor
	into
		@id,
		@firstname,
		@lastname

	select @fetch_status = @@fetch_status

	if @fetch_status <> 0 begin continue end

	exec MyProc @id, @firstname, @lastname

	end 	--While end

close MyCursor

deallocate MyCursor

CODO ERGO SUM

beetle3379
Starting Member

11 Posts

Posted - 2010-10-15 : 17:34:10

Thanks for that Michael...
I think I do need a loop because the tables I am querying will be constantly updating and getting new entries that will need to be inserted to the tune of 10-20 per day. So, when the query comes up with new values that aren't already in the table, I need to insert them and often it will be several at a time. This will be run by a VBScript every 15 mins or so.

I will read up on cursors like you said and see if I can get the above to work for me. It should since it looks to me like an index in other languages.

Thanks!

Loren Bailey

beetle3379
Starting Member

11 Posts

Posted - 2010-10-19 : 13:50:00

Hello,

I have read up on the cursors but I think I must be missing something. I tried to incorporate your example as well as some others I have seen and end up with the same result. When I make my stored procedure call, it indicates that when it tries to do an insert, the @code is being inserted with a NULL value. However, when I run the query that is supposed to pull this information, it does come up with results.

Could someone look at my usage and verify that I am doing this correctly please? Here is the what I have done:

DECLARE @current table
(exist varchar(100))

DECLARE @codes table
(code varchar(100))

INSERT @codes
select distinct pvr.code
from prsn_instc_prsn pvr
LEFT OUTER JOIN prsn_2 r (NOLOCK) ON pvr.code = r.code
JOIN prsn_instc pv (NOLOCK) ON pvr.prsn_instc_id = pv.prsn_instc_id
where r.code is NULL and pv.created_date > dateadd (MINUTE,-15,getDate()) AND pv.created_date < getDate()--get a date range to be decided

INSERT @current
select code from prsn_2

DECLARE @id_t int, @insCode varchar(100), @insLast varchar(100),@insFirst varchar(100),@fetch_status int

DECLARE myCursor CURSOR local
FOR
select distinct pvr.code, pvr.last_name, pvr.first_name
from prsn_instc_prsn pvr, @codes c
where pvr.code = c.code and pvr.last_name is not NULL and pvr.first_name is not NULL

OPEN myCursor
select @fetch_status = 0

WHILE @fetch_status = 0
BEGIN
FETCH next from myCursor
INTO @insCode,@insLast,@insFirst
select @fetch_status = @@fetch_status
IF @insCode not in (select exist from @current) AND
@fetch_status <> 0
CONTINUE
select @id_t = 1 + max(id) from prsn_2
EXEC stp_prsn_2 @id = @id_t,@code = @insCode, @last_name = @insLast, @first_name = @insFirst

END
close myCursor
deallocate MyCursor

Thanks for any pointers anyone can provide.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-19 : 14:11:36

Your while loop exit condition does not look right.

This is probably closer to what you need, but I am not sure what purpose the @current table serves.

if @insCode not in (select exist from @current) OR @fetch_status <> 0
	begin
	set @fetch_status = -999
	continue
	end

CODO ERGO SUM

beetle3379
Starting Member

11 Posts

Posted - 2010-10-19 : 15:21:11

The checking the current table is so when I do the insert a row that already exists will be prevented. It will throw an error because that is a unique field in that destination table. I was trying to find a way to prevent the error if the query returns something that already exists. I want it to continue through the table if it encounters a duplicate.

Loren Bailey

beetle3379
Starting Member

11 Posts

Posted - 2010-10-20 : 11:54:52

So, just to clarify and make sure I read the cursor documentation correctly, the block:
DECLARE myCursor CURSOR local
FOR
select distinct pvr.code, pvr.last_name, pvr.first_name
from prsn_instc_prsn pvr, @codes c
where pvr.code = c.code and pvr.last_name is not NULL and pvr.first_name is not NULL

Creates the cursor and specifies that this select statement directs the cursor the the data set returned by it and this section:

FETCH next from myCursor
INTO @insCode,@insLast,@insFirst

Identifies that it is the first row of those results? Is that correct or do I have that part built incorrectly?

Thanks again for your help.

Loren Bailey

beetle3379
Starting Member

11 Posts

Posted - 2010-10-22 : 14:24:43

Hello,

I have figured out the NULL problem here and got this to work as I wanted by adding a fetch next before the loop starts and assigning it to the variables. Thanks for the advice on the cursor...they work great.

I do have another problem though, I was writing this as a query until I got the results I wanted and now want to make it a stored procedure. It runs fine as a query but when I try to create a stored procedure I get a syntax error. The error is at the very beginning: "DECLARE @current table
(exist varchar(100))" It says "Syntax error near the keyword Declare"

Any ideas why it will run as a query but can't create a stored procedure? I did some looking and find nowhere that table variables can't be used in stored procedures.

Thanks!

Loren Bailey

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-22 : 14:33:43

Are you really asking for help fixing a syntax error in code you didn't post?

CODO ERGO SUM

beetle3379
Starting Member

11 Posts

Posted - 2010-10-22 : 14:38:18

Sorry...I just figured it was the same code as above with a slight difference in an additional FETCH. The code is:

DECLARE @current table
(exist varchar(100))

DECLARE @codes table
(code varchar(100))

INSERT @codes
select distinct pvr.code
from prsn_instc_prsn pvr
LEFT OUTER JOIN prsn_2 r (NOLOCK) ON pvr.code = r.code
JOIN prsn_instc pv (NOLOCK) ON pvr.prsn_instc_id = pv.prsn_instc_id
where r.code is NULL and pv.created_date > dateadd (MINUTE,-15,getDate()) AND pv.created_date < getDate()--get a date range to be decided

INSERT @current
select code from prsn_2

DECLARE @id_t int, @insCode varchar(100), @insLast varchar(100),@insFirst varchar(100),@fetch_status int

DECLARE myCursor CURSOR local
FOR
select distinct pvr.code, pvr.last_name, pvr.first_name
from prsn_instc_prsn pvr, @codes c
where pvr.code = c.code and pvr.last_name is not NULL and pvr.first_name is not NULL

OPEN myCursor
FETCH NEXT FROM myCursor
INTO @insCode, @insLast, @insFirst

select @fetch_status = 0

WHILE @fetch_status = 0
BEGIN
FETCH next from myCursor
INTO @insCode,@insLast,@insFirst
select @fetch_status = @@fetch_status
IF @insCode not in (select exist from @current) AND
@fetch_status <> 0
CONTINUE
select @id_t = 1 + max(id) from prsn_2
EXEC stp_prsn_2 @id = @id_t,@code = @insCode, @last_name = @insLast, @first_name = @insFirst

END
close myCursor
deallocate MyCursor

Thanks

Loren Bailey

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-22 : 14:44:35

That isn't code for creating a stored procedure.

CODO ERGO SUM

beetle3379
Starting Member

11 Posts

Posted - 2010-10-22 : 14:48:15

Didn't scroll up high enough. I left out my main block of comments and this was above it. Sorry again

CREATE PROC dbo.unknown_add

DECLARE @current table
(exist varchar(100))

DECLARE @codes table
(code varchar(100))

INSERT @codes
select distinct pvr.code
from prsn_instc_prsn pvr
LEFT OUTER JOIN prsn_2 r (NOLOCK) ON pvr.code = r.code
JOIN prsn_instc pv (NOLOCK) ON pvr.prsn_instc_id = pv.prsn_instc_id
where r.code is NULL and pv.created_date > dateadd (MINUTE,-15,getDate()) AND pv.created_date < getDate()--get a date range to be decided

INSERT @current
select code from prsn_2

DECLARE @id_t int, @insCode varchar(100), @insLast varchar(100),@insFirst varchar(100),@fetch_status int

DECLARE myCursor CURSOR local
FOR
select distinct pvr.code, pvr.last_name, pvr.first_name
from prsn_instc_prsn pvr, @codes c
where pvr.code = c.code and pvr.last_name is not NULL and pvr.first_name is not NULL

OPEN myCursor
FETCH NEXT FROM myCursor
INTO @insCode, @insLast, @insFirst

select @fetch_status = 0

WHILE @fetch_status = 0
BEGIN
FETCH next from myCursor
INTO @insCode,@insLast,@insFirst
select @fetch_status = @@fetch_status
IF @insCode not in (select exist from @current) AND
@fetch_status <> 0
CONTINUE
select @id_t = 1 + max(id) from prsn_2
EXEC stp_prsn_2 @id = @id_t,@code = @insCode, @last_name = @insLast, @first_name = @insFirst

END
close myCursor
deallocate MyCursor

Loren Bailey

beetle3379
Starting Member

11 Posts

Posted - 2010-10-22 : 14:52:30

Nevermind...I found it. I forgot AS. Sorry to bother you

Thanks

Loren Bailey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources