Stored Proc - SQL Server Forums

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

Transact-SQL (2000)

Stored Proc

Author

Topic

anjelah
Starting Member

4 Posts

Posted - 2005-02-14 : 04:42:52

Hi,
I could do with your help. Although I've been doing sql for quite a while, I am new to using stored procs.

The situation is that I have three tables Users, Systems and Users2Systems. At the minute all users belong to one system, we are adding 4 more systems and I would like a stored procedure to get the userids from the users table, take the new systemid as a parameter, and insert all the users with the relevant systemid into the users2systems table.

The relevant columns from the tables are Users.UserID, System.SystemID and UsersToSystem.UserID and UsersToSystem.SystemID

Your help would be much appreciated

Thanks

Anjelah

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-02-14 : 05:14:43

Have you looked at BOL (Books Online) under CREATE PROCEDURE. I highly suggest you to do that.

First jot down what your input parameters are, the process you want to do with-in the stored procedure and the output you want to get from the stored proc. If you have been doing SQL for a while, you'll see a stored procedure is not any different from it; just an additional syntax of CREATE PROCEDURE statement (and a little more..).

I'd give you the stored procedure myself, but you'd probably learn nothing and come back here for your next one. Try to write one u'r self and post the code. You'd get great sugessions to make it better from the PROs in this forum..

Happy SQLing..

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

anjelah
Starting Member

4 Posts

Posted - 2005-02-14 : 06:49:19

Hi,
I had looked at book online and various places on the net. The furthest I got was as follows :

CREATE PROCEDURE PopulateUsers2System (@SystemID varchar(10)) AS

select userid as tmpUserID from users

insert into users2systems(userid,systemid) values(tmpUserID, @SystemID)

The bit i don't get is how to loop through the users table and apply the insert for each user. If I was doing it in code it would be as follows:

set cn = opencn
strSQL="select userid as tmpUserID from users "
set rs = openrs (strSQL,cn)
if not rs.eof then
while not rs.eof
strSQL = "insert into users2systems(userid,systemid) values(tmpUserID, @SystemID)"
cn.execute(strSQL)
wend
end if
closers rs
closecn cn
I would appreciate a pointer on how to loop through..

Thanks

Anjelah

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2005-02-14 : 06:55:43

I don't think you need any looking for this. Now, let me understand what you are asking.
You want to take all the userid from users and insert it into users2systems along with @systemID parameter.. right..
here it is...


CREATE PROCEDURE PopulateUsers2System (@SystemID varchar(10)) 
AS
insert into users2systems(userid,systemid) 
    select userid, @SystemID from users

HTH..Test it and see if it gives you what you want..

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

anjelah
Starting Member

4 Posts

Posted - 2005-02-14 : 07:06:00

thank you, that is what I needed. (I do tend to overcomplicate things sometime!)

Much appreciated

Anjelah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources