how to return uniqueidentifier in sp? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

how to return uniqueidentifier in sp?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-18 : 15:04:53

Hi, my dear friends,

What did I do wrong? I have trouble to get the uniqueidentifier I just inserted out.
---sp
CREATE PROCEDURE dbo.FAC_Ins_USR
@LAST_NAME AS nvarchar(60)
,@FIRST_NAME AS nvarchar(60)
,@NewID uniqueidentifier output
AS
BEGIN

SET NOCOUNT ON;

Declare @ErrorSaved int;
SET @ErrorSaved = 0;

SET @NewID = NewID();
INSERT INTO [dbo].[USERS]
([USER_ID], [LAST_NAME] ,[FIRST_NAME] )
VALUES
( @NewID, @LAST_NAME,@FIRST_NAME )
Select @ErrorSaved=@@ERROR;
Return @ErrorSaved;
END
GO
--calling from ssms
declare @myErr int
Declare @myID uniqueidentifier

EXEC @myErr = DBO.FAC_Ins_USR @LAST_NAME ='JOE'
,@FIRST_NAME ='DOE'
,@NewID = @myID
print @myID
print @myErr

The new data went into the table, and the print @myErr shows 0.
But print @myID shows nothing.

---here is the part of the table
CREATE TABLE [dbo].[USERS](
[USER_ID] [uniqueidentifier] DEFAULT NEWID() NOT NULL,...

Thanks!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-18 : 15:13:31

Also, I don't like the way I insert the new uniqueidentifier inside the sp.

But without explicitly insert it, what is the better option of retrieving it after the insert? Scope_Identify() only works on int datatype if I understand correctly.

Btw, this is in sql 2008.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-18 : 15:16:16

EXEC @myErr = DBO.FAC_Ins_USR @LAST_NAME ='JOE'
,@FIRST_NAME ='DOE'
,@NewID = @myID OUTPUT

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-18 : 15:21:28

Aha, I feel down on the finish line!

Thanks!

Any idea on how to improve the things I described at my followup reply?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-18 : 15:26:41

What you have posted is the way it's done for guids.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-23 : 16:24:29

To continue on above question but on the dotnet front end, what kind of the code that will get both the error code and the ID back as the sp has defined here?

I haven't done ado for awhile, I could get the output param .value to show the id udner debug, but had hard time to assign it to any thing like my last twe lined of code tried here. So maybe my cmd.execute is not right?

Thanks!

//cmd.Parameters.Add("@NewId", SqlDbType.UniqueIdentifier);
//cmd.Parameters["@NewId"].Direction = ParameterDirection.Output;

SqlParameter newId = cmd.Parameters.Add("@NewId", SqlDbType.UniqueIdentifier);
newId.Direction = ParameterDirection.Output;

int err = cmd.ExecuteNonQuery();

Session["userId"] = cmd.Parameters["@NewId"].ToString();
Guid newUserId = new Guid (cmd.Parameters["@NewId"].Value);

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 16:35:37

Create and add another parameter to the command object, but set its ParameterDirection as ParameterDirection.ReturnValue, and then get the value from it just like you are getting the newid.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-23 : 17:05:20

Did not work either.

quote:
Originally posted by James K

Create and add another parameter to the command object, but set its ParameterDirection as ParameterDirection.ReturnValue, and then get the value from it just like you are getting the newid.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-23 : 17:25:07

Can you post the code?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-24 : 07:15:28

SqlParameter newId = cmd.Parameters.Add("@NewId", SqlDbType.UniqueIdentifier);
newId.Direction = ParameterDirection.ReturnValue; <-- as you suggested

int err = cmd.ExecuteNonQuery();

or

int err = cmd.ExecuteScalar();

After give it some other thought, maybe returning error code at the same time make things harder than it should be.

quote:
Originally posted by James K

Can you post the code?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 08:56:52

quote:
Originally posted by Hommer

SqlParameter newId = cmd.Parameters.Add("@NewId", SqlDbType.UniqueIdentifier);
newId.Direction = ParameterDirection.ReturnValue; <-- as you suggested

int err = cmd.ExecuteNonQuery();

or

int err = cmd.ExecuteScalar();

After give it some other thought, maybe returning error code at the same time make things harder than it should be.

quote:
Originally posted by James K

Can you post the code?

The parameter list in your stored procedure has to match with the parameters that you send from the C# code. Your stored procedure has 3 parameters and one return value. So you need to do the following:


            SqlParameter newId = cmd.Parameters.Add("@NewId", SqlDbType.UniqueIdentifier);
            newId.Direction = ParameterDirection.Output;

            cmd.Parameters.AddWithValue("@LAST_NAME", "James");
            cmd.Parameters.AddWithValue("@FIRST_NAME", "Köhler");

            SqlParameter retVal = new SqlParameter("@ErrorSaved", SqlDbType.Int);
            retVal.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(retVal);
            cmd.CommandType = CommandType.StoredProcedure;

Now after your executenonquery, get the values of newid and

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-10-24 : 11:41:07

Great, Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources