get the value from Stored Procudure

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

get the value from Stored Procudure

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-04-22 : 10:19:16

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 10:39:09

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

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-04-22 : 11:07:59

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 11:53:31

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.

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-04-22 : 13:30:53

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 13:33:44

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.

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-04-22 : 13:52:05

Thank you so much.. Its working.. :-)

Archana

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 15:13:12

You are very welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources