insert new recordset and get the GUID of that rs

Question

insert new recordset and get the GUID of that rs

cadillac

SSC Journeyman

Points: 99
More actions
January 15, 2004 at 4:09 pm

#107701

Hi out there! Perhaps anyone of you can help me.
I need to get a GUID of table and wanted to do it by using a stored procedure that inserts a recordset,
retrieves the @@identity and then gets the recordset with that identity and get the GUID.
The table looks like this: sessionguid (GUID), sessionnr (id) = identity. How would the stored procedure look to get a new GUID? kindest regards; Michael

Michael

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

@@identity would only return the identity for the last record inserted, not for the whol recordset. To do this for a single record:

Create Procedure dbo.TestGuid AS

Declare @MyId int

Insert Into TestTable (TestField)

Values (1)

Set @MyId = @@identity

Select sessionguid

From TestTable

Where sessionid = @MyID

Go

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

cadillac SSC Journeyman Points: 99 More actions · Answer 2

Thanks Rawhide!

And how would I have to manage it, to get the GUID as OUTPUT Parameter? When I call that procedure, an record will be inserted, but the procedure doesn´t return any data.

Michael

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

The example I did above should return it. It does when I tested it on my server.

Post your sp and let's see.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

cadillac SSC Journeyman Points: 99 More actions · Answer 4

Thanks! Just works fine. How Do I run a SP within the enterprise manager? I normally use Access as frontend or ASP-pages to run them. Thanks a lot again. That SP speeds up my DB significant...

Michael

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

Use Query Analyzer to run your sp's. It's listed under the SQL Server tools.

Just type in the same SQL command that you would pass in from the ASP page.

Query Analyzer is a SQL Server developer's best friend.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

cadillac SSC Journeyman Points: 99 More actions · Answer 6

Thanks. I just hoped there would be shortlink within the enterprise manager. I´m still tweaking the DB to further optmize it. This new SP takes about 0,17 sec, instead of the 1,5 sec my old procedure took. Thanks for your blazing fast answers. It´s time for me to sleep (íts 01:49 a.m. in austria....). Thanks and good night!

Michael

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

Actually, there is a shortlink in Enterprise Manager.

If you're already in the db you want to be in, click "Tools" on the menu at the top of the console window and click on SQL Query Analyzer. It will open QA to that database on that server automatically. It will automatically log you in as well even if you're using SQL Server authentication.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]