Returning Scope_Identity() for GUID

I'm stumped... I'm trying to get the GUID value returned from an insert (I
really need to use a GUID because this is a web app and I want the records t
o
be difficult to find i.e. identity key within range is too easy to browse)
Declare @.Identity nvarchar(30)
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
This returns null while
Drop Table #T
Declare @.Identity uniqueidentifier
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
returns error - "Operand type clash: numeric is incompativle with
uniqueidentifier.
Suggestions would be greatly appreciated. Thanks in advance.
- Abe1. scope_identity() only returns the last (scoped) identity value - this
value datatype has to be numeric.
2. the first batch returns NULL because there is no identity column in your
table. Thus, the last insert generates a NULL identity (INT) value. The
implicit conversion from INT to nvarchar is allowed. Hence, no error is
raised.
3. the second batch fails because of the implicit conversion. You can't
convert a numeric to uniqueidentifier.
Here is a quick proof of the conversion error:
select convert(uniqueidentifier,1)
-oj
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
> to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||"Returns the last IDENTITY value inserted into an IDENTITY column in the
same scope. A scope is a module -- a stored procedure, trigger, function, or
batch" from BOL .Identity columns need to be of type bigint, int or smallint
and are not compatible with type uniqueidentifier .Therefore,to ask SQL
Server to give you last identity inserted and then assigning that to a
variable previously declared as a uniqueidentifier, equals compatibility
error
To return it do something like:
Declare @.Identity uniqueidentifier
Select @.Identity = newID()
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (I,LName) Values(@.identity,'Rosner')
SELECT @.IDENTITY
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||Hi,
How about capturing the GUID into a local variable and using it for insert
and then as return value as well.
Declare @.PersistGUID uniqueidentifier
Select @.PersistGUID = newid()
Now you could use @.PersistGUID as per your need.
Best Regards
Vadivel
http://vadivel.blogspot.com
"AbeR" wrote:

> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe