creating and selecting uniqueidentifier

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

SQL Server 2000 Forums

SQL Server Development (2000)

creating and selecting uniqueidentifier

Author

Topic

namyst
Starting Member

4 Posts

Posted - 2004-01-21 : 12:55:37

I am trying to create a Guid as an output parameter, then pass it in a query string. I would then like to select it. My SP's are as follows:

--This SP creates my guid and passes it in a query string
@LoginID int,
@SalonID int,
@GuidID UNIQUEIDENTIFIER out
as

SET @GuidID = NEWID()

INSERT INTO NetSessionInterface
(LoginID, SalonID, GuidID)
VALUES(@LoginID, @SalonID, @GuidID)

-- This SP returns my guid
@GuidID uniqueidentifier

as

Select *

from NetSessionInterface

where GuidID=@GuidID

The 1st SP returns my guid with curly brackets. My second SP will only return values if my guid does not have curly brackets.

Any ideas how to output without curly brackets or select with them?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-21 : 13:03:08

It works fine for me:

DECLARE @test uniqueidentifier

EXEC test 1,1, @test OUTPUT

EXEC test2 @test

test stored is the first stored proc that you mention and test2 is the second. Here is all of the code that I used to test it:



CREATE PROC test
(@LoginID INT, @SalonID INT, @GuidID UNIQUEIDENTIFIER OUTPUT)
AS

SET @GuidID = NEWID()

INSERT INTO NetSessionInterface (LoginID, SalonID, GuidID)
VALUES(@LoginID, @SalonID, @GuidID)

RETURN
GO

CREATE PROC test2
(@GuidID UNIQUEIDENTIFIER)
AS

SELECT loginid, salonid, guidid
FROM NetSessionInterface
WHERE GuidID = @GuidID

RETURN
GO

SET NOCOUNT ON

CREATE TABLE NetSessionInterface
(
loginid INT NOT NULL,
salonid INT NOT NULL,
guidid UNIQUEIDENTIFIER NOT NULL
)
GO



DECLARE @test UNIQUEIDENTIFIER

EXEC test 1,1, @test OUTPUT

EXEC test2 @test


DROP PROC test2
DROP PROC test
DROP TABLE NetSessionInterface

Tara

namyst
Starting Member

4 Posts

Posted - 2004-01-21 : 13:40:28

My GuidID is coming into the application as a query string.

ex. somepage.aspx?GuidID={633CD95D-DC23-4114-92D8-0CE59CCE6227
}

Try running SP test2 in query anylizer and see if you can get return vales when entering GuidID with curly brackets.

I get an error:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-21 : 13:42:14

Why are you entering a GUID with curly bracket? Let SQL Server generate the GUID for you with SET @GuidID = NEWID()

Tara

namyst
Starting Member

4 Posts

Posted - 2004-01-21 : 14:07:35

I am letting sql server create the guid. This happens in my asp application when the 1st SP is called (your sp test). SQL server returns the guid with the brackets and they end up in my query string.

ex. somepage.aspx?GuidID={633CD95D-DC23-4114-92D8-0CE59CCE6227
}

My asp.net application then runs the 2nd SP (your SP test2). The guid is passed in with brackets and causes an error. Maybe I should parse out the brackets in my .net application. I just thought there might be a better way.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-21 : 14:32:37

http://www.sqlxml.org/faqs.aspx?faq=41

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources