SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 get the value from Stored Procudure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

archana23
Yak Posting Veteran

78 Posts

Posted - 04/22/2013 :  10:19:16  Show Profile  Reply with Quote
Hello,
I have once stored procedure to insert data into security table as like this.
INSERT INTO Security(EISUser,
FName,
Seclevel)

VALUES(@EISUser,
@FName,
@Seclevel)
Recored in inserting succesfully with UserId as PrimaryKey value.

But my Question is i want to get above UserID (Primarykey value in Security Table) to insert some data in another table as shown like below

INSERT INTO Depts(UserId,
DeptId)

VALUES(@UserId
@DeptId)

So how do i get UserId value..

Can anyone please help me on this.

Thank you

Archana

James K
Flowing Fount of Yak Knowledge

3708 Posts

Posted - 04/22/2013 :  10:39:09  Show Profile  Reply with Quote
If the UserID is autogenerated (i.e., if it is an identity column), you can insert a statement in between the two inserts like this:
DECLARE @UserID INT = SCOPE_IDENTITY()
Another alternative, which would work (and work correclty if you were to insert multiple rows to the Security table) is to use the OUTPUT clause - if you like to use that see here: http://msdn.microsoft.com/en-us/library/ms177564.aspx
Go to Top of Page

archana23
Yak Posting Veteran

78 Posts

Posted - 04/22/2013 :  11:07:59  Show Profile  Reply with Quote
Hello James,

Thanks for your reply. That helped me to get the UserId but i have one more different problem that is once i get the UserId i need to execute below Insert query

INSERT INTO Depts(UserId,
DeptId)

VALUES(@UserId
@DeptId)

But I am getting @DeptId (From front end) as comma separated values like this @DeptId = 1,5,6,8,9.

How can i insert these values for same UserId in Depts Table.

Thank you

Archana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3708 Posts

Posted - 04/22/2013 :  11:53:31  Show Profile  Reply with Quote
quote:
Originally posted by archana23

Hello James,

Thanks for your reply. That helped me to get the UserId but i have one more different problem that is once i get the UserId i need to execute below Insert query

INSERT INTO Depts(UserId,
DeptId)

VALUES(@UserId
@DeptId)

But I am getting @DeptId (From front end) as comma separated values like this @DeptId = 1,5,6,8,9.

How can i insert these values for same UserId in Depts Table.

Thank you

Archana

You need string splitter function to do this. There are several on the web. Copy the one from here and run the code to create the function: http://www.sqlservercentral.com/articles/Tally+Table/72993/ (The code is in Figure 21).

Once you install that function, change you insert statement to this:
insert into Depts (UserId, DeptId)
select
	@UserId,
	Item
from
	dbo.DelimitedSplit8K(@DeptId,',')
I am assuming that you want to insert all those different DeptID's for the same UserId. In other words, a single user is associated with multiple Departments. If that is not the case post back.
Go to Top of Page

archana23
Yak Posting Veteran

78 Posts

Posted - 04/22/2013 :  13:30:53  Show Profile  Reply with Quote
Hello James,

I want to insert all those different DeptID's for the same UserId like as shown below.

If UserId = 5 and @DeptId = 1,2,7,8,9

Then i want this to be inserted as like this

UserId DeptID
5 1
5 2
5 7
5 8
5 9

Is above functionality works like this??

Archana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3708 Posts

Posted - 04/22/2013 :  13:33:44  Show Profile  Reply with Quote
quote:
Originally posted by archana23

Hello James,

I want to insert all those different DeptID's for the same UserId like as shown below.

If UserId = 5 and @DeptId = 1,2,7,8,9

Then i want this to be inserted as like this

UserId DeptID
5 1
5 2
5 7
5 8
5 9

Is above functionality works like this??

Archana

Yes, exactly.
Go to Top of Page

archana23
Yak Posting Veteran

78 Posts

Posted - 04/22/2013 :  13:52:05  Show Profile  Reply with Quote
Thank you so much.. Its working.. :-)

Archana
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3708 Posts

Posted - 04/22/2013 :  15:13:12  Show Profile  Reply with Quote
You are very welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000