Creating a stored procedure - 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

General SQL Server Forums

New to SQL Server Programming

Creating a stored procedure

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-09-20 : 21:51:34

There are 2 tables called Master and the supervisor table

Master table fields
-------------------

userid,
logonid
username
textsid

Supervisor table fields
-----------------------

userid
logonid

I need to take the parameter for the procedure as logonid which is coming from my DB2 database

Based on that logonid i need to retrieve the respective userid for that logonid from the Master table

after getting the userid i need to search in the supervisor table for that userid and if the userid already exists there is nothing to do and if the userid doesn't exist then insert the record with that userid and logonid

Please help me out!!

Thanks,
Reacha

Ancy
Starting Member

23 Posts

Posted - 2010-09-21 : 01:02:54

I have not tested this but should work....

Create proc proc1
(@logonid varchar(20))
as
Begin
Declare @userid int

select @userid = userid from Mastertable where logonid = @logonid

If not exists(select userid from Supervisortable where userid = @userid)
Begin
insert into Supervisortable(userid,logonid) values (@userid, @logonid)
End
End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-21 : 12:05:55

for retrieving details from DB2 you need to add it as a linked server to SQL server and then use it like normal table.

and your proc will be like


Create proc proc1 
(@logonid varchar(20))
as
Begin
Declare @db2logonid int,@userid int

select @db2logonid = userid from [DB2servername].schema.table where logonid = @logonid 

select @userid = userid from Mastertable where logonid = @logonid 


insert into Supervisortable(userid,logonid) 
select @userid, @logonid
where not exists (select 1 from  Supervisortable where userid=@userid)
End

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources