Beginner Needs Help With SP - 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

Beginner Needs Help With SP

Author

Topic

WeeBubba
Starting Member

18 Posts

Posted - 2006-12-08 : 18:19:25

hi there. this is my first post on this forum. im new to SQLServer so please go easy on me. :)

i am trying desperately to call a stored procedure from within another stored procedure. if i post my code below could somebody tell me where i am going wrong. the SP i have written is executing OK but the OUTPUT parameter Id_User is coming back as null, whereas it should be giving my a value there. i have tried changing the line 'EXEC sp_User_I' for 'INSERT INTO @User_Id EXEC sp_User_I' but this wont compile.

CREATE PROCEDURE sp_Account_I]
@Username varchar(16),
@Password varchar(88),
@Surname varchar(32),
@DateBirth datetime,
@Email varchar(64),
@Id_Account int OUTPUT,
@Id_User int OUTPUT

AS

DECLARE @ErrorCode int;

BEGIN

SET NOCOUNT ON;

EXEC sp_User_I
@Username,
@Password,
@FirstName,
@Surname,
@DateBirth,
@Email,
@Id_Country,
@Id_User

...do some other stuff here for the account...

END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-08 : 18:23:40

You need to put the OUTPUT keyword in the call too, so this should fix it

CREATE PROCEDURE sp_Account_I]
@Username varchar(16),
@Password varchar(88), 
@Surname varchar(32),
@DateBirth datetime,
@Email varchar(64), 
@Id_Account int OUTPUT,
@Id_User int OUTPUT

AS

DECLARE @ErrorCode int;

BEGIN

SET NOCOUNT ON;

EXEC sp_User_I
@Username,
@Password, 
@FirstName, 
@Surname,
@DateBirth,
@Email, 
@Id_Country,
@Id_User OUTPUT

...do some other stuff here for the account...

END

WeeBubba
Starting Member

18 Posts

Posted - 2006-12-08 : 18:33:25

lol argh. thanks for helping me so quickly.

i've got so much to learn!

i've got another question if you don't mind.

on my SP_USER_I, after inserting a user i am retrieving the ID (Identity column) for the newly selected user like this:

BEGIN TRY
INSERT INTO Users(
Username,
Password,
FirstName,
Surname,
DateBirth,
Email,
Id_Country)
VALUES(
@Username,
@Password,
@FirstName,
@Surname,
@DateBirth,
@Email,
@Id_Country)
END TRY
BEGIN CATCH
IF EXISTS(
SELECT
Username
FROM
Users
WHERE
Username = @Username)
RETURN 1; /* The username already exists. */

RETURN 2; /* An unhandled error occurred. */
END CATCH;

SELECT @Id_User = SCOPE_IDENTITY();

i have read that this is the best way. however it strikes me as slightly wobbly in case another user gets inserted in between me inserting and getting the new id (very unlikely i know). can you confirm, is this the way to go? thanks!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-08 : 18:49:49

You're safe - SCOPE_IDENTITY returns the identity value added on the current connection so if someone else adds rows, the connection running that code will still see the indentity of the row it inserted.

You can find the most recent identity value for a table added by any connection, but for that you use IDENT_CURRENT instead of SCOPE_IDENTITY.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-12-09 : 01:24:22

I prefer using SCOPE_IDENTITY

Books Online

quote:

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources