Select & Update Stored procedure

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

SQL Server 2000 Forums

SQL Server Development (2000)

Select & Update Stored procedure

Author

Topic

GeorgeT
Starting Member

2 Posts

Posted - 2004-05-20 : 14:20:44

Please help me with a sample of stored procedure. I need to update all rows in address table by calling a stored prosedure from visual basic using out and in parameters. I am looking on how to select data from database, then send it to VB for verification and then send it back to stored procedure to update same record and move to the next level. Please advice me on the best solution or provide a sample. Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-20 : 14:30:30

Do you have an ID on the address table?
If so call an SP with ID as input parameter (start with 0)
The sp returns the address with the next highest ID.
Display the address and if changes:
Call update SP with the ID and all fields as input parmaters - this just updates the address.
Then call the retrieval SP again.

You could make them one SP which takes an action parameter which says whether or not to update and returns the next ID or always update - better to not update if no changes though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GeorgeT
Starting Member

2 Posts

Posted - 2004-05-20 : 14:53:01

Would you provide me with a sample code for SP

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-20 : 16:48:30

create proc s_GetAddress
@id int
as

select top 1 *
from address
where id > @id
order by id
go

create proc s_UpdAddress
@id int ,
@name varchar(50) ,
@addr1 varchar(50) ,
@addr21 varchar(50)
as

update address
set name = @name ,
addr1 = @addr1 ,
addr2 = @addr2 ,
addr3 = @addr3
where id = @id
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources