Alan Warren
asked on
Return Scope_Identity as output param of type uniqueidentifier wth default value of NewID()
Hi all,
got a problem with returning scopeidentity of type uniqueidentifier
-- tableScript
-- Creating a table using NEWID for uniqueidentifier data type.
CREATE TABLE cust
(
cust_id uniqueidentifier NOT NULL
DEFAULT newid(),
company varchar(30) NOT NULL,
contact_name varchar(60) NOT NULL,
address varchar(30) NOT NULL,
city varchar(30) NOT NULL,
state_province varchar(10) NULL,
postal_code varchar(10) NOT NULL,
country varchar(20) NOT NULL,
telephone varchar(15) NOT NULL,
fax varchar(15) NULL
)
GO
-- Test procedure to return Scope Identity Of Unique ID Column
Alter procedure spTestReturnScopeIdentityO fUniqueIDC ol
(
@myid uniqueidentifier = null OUTPUT
)
as
INSERT cust
(company, contact_name, address, city, state_province,
postal_code, country, telephone, fax)
VALUES
('Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68')
-- Error:
-- Operand type clash: numeric is incompatible with uniqueidentifier
Set @myid = SCOPE_IDENTITY()
Go
Alan
got a problem with returning scopeidentity of type uniqueidentifier
-- tableScript
-- Creating a table using NEWID for uniqueidentifier data type.
CREATE TABLE cust
(
cust_id uniqueidentifier NOT NULL
DEFAULT newid(),
company varchar(30) NOT NULL,
contact_name varchar(60) NOT NULL,
address varchar(30) NOT NULL,
city varchar(30) NOT NULL,
state_province varchar(10) NULL,
postal_code varchar(10) NOT NULL,
country varchar(20) NOT NULL,
telephone varchar(15) NOT NULL,
fax varchar(15) NULL
)
GO
-- Test procedure to return Scope Identity Of Unique ID Column
Alter procedure spTestReturnScopeIdentityO
(
@myid uniqueidentifier = null OUTPUT
)
as
INSERT cust
(company, contact_name, address, city, state_province,
postal_code, country, telephone, fax)
VALUES
('Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68')
-- Error:
-- Operand type clash: numeric is incompatible with uniqueidentifier
Set @myid = SCOPE_IDENTITY()
Go
Alan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can output the ID, yes.
ASKER
got it !!
thanks for your help cheeky-monkey
Don't know if you are at home with VbScript/ASP but here it is
Returns: VarMyID: {19ED704A-B5DD-46CA-98C2-B 694547B29B 8}
Regards Alan
<%@ Language=VBScript %>
<HTML>
<HEAD>
</HEAD>
<BODY>
<P> </P>
</BODY>
</HTML>
<%
Const adCmdStoredProc = 4
Const adParamOutput = 2
Const adGUID = 72
Const adVariant = 12
Const adExecuteNoRecords = 128
Dim objConn ' Database Connection
dim sDataConnect ' ADO SQL connect string
Dim objCmd ' ADO Command Object
Dim varMyID ' variable to contain newID
' Instantiate a database connection
Set objConn = Server.CreateObject("ADODB .Connectio n")
' Create an ADO connect string
sDataConnect = adoConnectSQL("password", "sa", "Alzowze", "(local)")
' Open a database connection
objConn.Open sDataConnect
' Instantiate an ADO command object
Set objCmd = Server.CreateObject("ADODB .Command")
With objCmd
.CommandText = "spTestReturnScopeIdentity OfUniqueID Col"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@myid", adGUID, adParamOutput, 16)
Set .ActiveConnection = objConn
.Execute , , adExecuteNoRecords
varMyID = .Parameters.Item("@myid")
End With
Response.Write("VarMyID: " & varMyID)
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
<%
Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource)
' Returns SQLOLEDB ADO connect string
' Uses SQL Server security
Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource
sProvider = "Provider=SQLOLEDB.1;"
sPassword = "Password=" & psPassword & ";"
sPersist = "Persist Security Info=True;"
sUser = "User ID=" & psUser & ";"
sCatalog = "Initial Catalog=" & psCatalog & ";"
sDataSource = "Data Source=" & psDataSource
adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource
End Function
%>
thanks for your help cheeky-monkey
Don't know if you are at home with VbScript/ASP but here it is
Returns: VarMyID: {19ED704A-B5DD-46CA-98C2-B
Regards Alan
<%@ Language=VBScript %>
<HTML>
<HEAD>
</HEAD>
<BODY>
<P> </P>
</BODY>
</HTML>
<%
Const adCmdStoredProc = 4
Const adParamOutput = 2
Const adGUID = 72
Const adVariant = 12
Const adExecuteNoRecords = 128
Dim objConn ' Database Connection
dim sDataConnect ' ADO SQL connect string
Dim objCmd ' ADO Command Object
Dim varMyID ' variable to contain newID
' Instantiate a database connection
Set objConn = Server.CreateObject("ADODB
' Create an ADO connect string
sDataConnect = adoConnectSQL("password", "sa", "Alzowze", "(local)")
' Open a database connection
objConn.Open sDataConnect
' Instantiate an ADO command object
Set objCmd = Server.CreateObject("ADODB
With objCmd
.CommandText = "spTestReturnScopeIdentity
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@myid", adGUID, adParamOutput, 16)
Set .ActiveConnection = objConn
.Execute , , adExecuteNoRecords
varMyID = .Parameters.Item("@myid")
End With
Response.Write("VarMyID: " & varMyID)
Set objCmd = Nothing
objConn.Close
Set objConn = Nothing
%>
<%
Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource)
' Returns SQLOLEDB ADO connect string
' Uses SQL Server security
Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource
sProvider = "Provider=SQLOLEDB.1;"
sPassword = "Password=" & psPassword & ";"
sPersist = "Persist Security Info=True;"
sUser = "User ID=" & psUser & ";"
sCatalog = "Initial Catalog=" & psCatalog & ";"
sDataSource = "Data Source=" & psDataSource
adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource
End Function
%>
I'm at home with whatever my boss decides he wants done today! Thanks for posting the full solution and glad to be of help.
Cheers.
Cheers.
ASKER
Appreciate your help :)
Obviously this is part of a bigger procedure, but I needed to get the return/output param sorted out before I could append all the other input parameters.
As soon as I got it into my head that 'there is no Identity property associated with uniqueidentifier' duh! it became clear to me that I couldnt use scope_Identity() or @@Identity to return the uniqueidentifier, even though default value was set to NewID(), so I would have to either create the GUID in ASP or as you suggested, Set it in the proc and return the set value.
Phew!
I hate GUID's
Thanks Again
Alan (Page Ed Databases)
Obviously this is part of a bigger procedure, but I needed to get the return/output param sorted out before I could append all the other input parameters.
As soon as I got it into my head that 'there is no Identity property associated with uniqueidentifier' duh! it became clear to me that I couldnt use scope_Identity() or @@Identity to return the uniqueidentifier, even though default value was set to NewID(), so I would have to either create the GUID in ASP or as you suggested, Set it in the proc and return the set value.
Phew!
I hate GUID's
Thanks Again
Alan (Page Ed Databases)
I know how it is. The goal is a perfectly valid one, it's just that one step isn't.....it can do your head in sometimes!
GUIDs can be fiddly at times so I try to use an int ID for anything that happens "behind the scenes" but when it's being passed around in public, I always use a GUID. It's nice to know that they are all but impossible to duplicate.
Best of luck!
GUIDs can be fiddly at times so I try to use an int ID for anything that happens "behind the scenes" but when it's being passed around in public, I always use a GUID. It's nice to know that they are all but impossible to duplicate.
Best of luck!
ASKER
Absolutely right, there is no Identity value with cols of type uniqueidentifier
BOL: All column constraints and properties except IDENTITY are allowed on the uniqueidentifier data type.
If I want to return @myID as an output param to the caller will this work ok?
Alter procedure spTestReturnScopeIdentityO
(
@myid uniqueidentifier = null OUTPUT
)
as
Select @myid = NewID()
INSERT cust
(cust_id, company, contact_name, address, city, state_province,
postal_code, country, telephone, fax)
VALUES
(@myid, 'Maison Dewey', 'Catherine Dewey', 'Rue Joseph-Bens 532', 'Bruxelles', NULL,
'B-1180', 'Belgium', '(02) 201 24 67', '(02) 201 24 68')
go
Then in the calling VBA app:
Dim MyID as variant
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.CommandText = "spTestReturnScopeIdentity
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@myid", adGUID, adParamOutput, 16)
Set .ActiveConnection = CurrentProject.Connection ' ADO.connection object
.Execute , , adExecuteNoRecords
MyID= .Parameters.Item("@myid")
End With
Debug.Print MyID
Alan