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
 how to return uniqueidentifier in sp?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

786 Posts

Posted - 10/18/2013 :  15:04:53  Show Profile  Reply with Quote
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

786 Posts

Posted - 10/18/2013 :  15:13:31  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 10/18/2013 :  15:16:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 10/18/2013 :  15:21:28  Show Profile  Reply with Quote
Aha, I feel down on the finish line!

Thanks!

Any idea on how to improve the things I described at my followup reply?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 10/18/2013 :  15:26:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
What you have posted is the way it's done for guids.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 10/23/2013 :  16:24:29  Show Profile  Reply with Quote
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);

Edited by - Hommer on 10/23/2013 16:25:58
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3653 Posts

Posted - 10/23/2013 :  16:35:37  Show Profile  Reply with Quote
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.
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 10/23/2013 :  17:05:20  Show Profile  Reply with Quote
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.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3653 Posts

Posted - 10/23/2013 :  17:25:07  Show Profile  Reply with Quote
Can you post the code?
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 10/24/2013 :  07:15:28  Show Profile  Reply with Quote
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?

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3653 Posts

Posted - 10/24/2013 :  08:56:52  Show Profile  Reply with Quote
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
Go to Top of Page

Hommer
Aged Yak Warrior

786 Posts

Posted - 10/24/2013 :  11:41:07  Show Profile  Reply with Quote
Great, Thanks!
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.09 seconds. Powered By: Snitz Forums 2000