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 2005 Forums
 Transact-SQL (2005)
 How to return inserted Pk record in SP (not int)?

Author  Topic 

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-08 : 12:31:18
hi
i have a stored procedure for insert, my table has a uniqueidentifer column as primary key, i want when my sp execute, it returns pk of inserted record like this :
CREATE PROCEDURE [dbo].[sp_t2_Insert]
@c1 uniqueidentifier output,
@c2 int = null
AS
INSERT [dbo].[t2]
(
[c1],
[c2]
)
VALUES
(
newid(),
@c2
)
select @c1 = @@identity;
GO

but the following error message has shown me :
Msg 206, Level 16, State 2, Procedure sp_t2_Insert, Line 19
Operand type clash: numeric is incompatible with uniqueidentifier


it's not integer, but i want to return non int values.
thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-08 : 12:55:00
try this I havn't tested.

CREATE PROCEDURE [dbo].[sp_t2_Insert]
@c1 uniqueidentifier output,
@c2 int = null
AS

DECLARE @t TABLE (c1 UNIQUEIDENTIFIER)
INSERT [dbo].[t2]
(
[c1],
[c2]
)
OUTPUT inserted.c1 INTO @t(c1)
VALUES
(
newid(),
@c2
)
select @c1 = (select C1 from @T)







Go to Top of Page

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-08 : 15:02:58
thanks for reply
when i build this sp :
CREATE PROCEDURE [dbo].[sp_t2_Insert]
@c1 uniqueidentifier output,
@c2 int = null
AS

DECLARE @t TABLE (c1 UNIQUEIDENTIFIER)
INSERT [dbo].[t2]
(
[c1],
[c2]
)
OUTPUT inserted.c1 INTO @t(c1)
VALUES
(
newid(),
@c2
)
select @c1 = (select C1 from @T)


and execute it like this :
declare @x uniqueidentifier
exec dbo.sp_t2_Insert @x,6
select @x


it return null, but when i modify sp as follow :
CREATE PROCEDURE [dbo].[sp_t2_Insert]
@c1 uniqueidentifier output,
@c2 int = null
AS

DECLARE @t TABLE (c1 UNIQUEIDENTIFIER)
INSERT [dbo].[t2]
(
[c1],
[c2]
)
OUTPUT inserted.c1 INTO @t(c1)
VALUES
(
newid(),
@c2
)
select @c1 = (select C1 from @T)
print @c1


it worked correctly, where does my problem ?
thanks
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-08 : 15:41:54
you need to specify output



declare @x uniqueidentifier
exec dbo.sp_t2_Insert @x output,6
select @x
Go to Top of Page

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-08 : 15:56:02
thanks Vinnie881
my problem was solved.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-08 : 18:00:25
Even simpler.
CREATE PROCEDURE [dbo].[sp_t2_Insert]
@c1 uniqueidentifier output,
@c2 int = null
AS

set nocount on

if @c1 is null
set @c1 = newid()

INSERT [dbo].[t2]
(
[c1],
[c2]
)
VALUES
(
@c1,
@c2
)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-09 : 15:57:00
quote:
Even simpler.
CREATE PROCEDURE [dbo].[sp_t2_Insert]
@c1 uniqueidentifier output,
@c2 int = null
AS

set nocount on

if @c1 is null
set @c1 = newid()

INSERT [dbo].[t2]
(
[c1],
[c2]
)
VALUES
(
@c1,
@c2
)

do you think this sp return inserted pk record ?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-09 : 17:28:05
No.
I don't think so. I know so.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

hdv212
Posting Yak Master

140 Posts

Posted - 2008-08-09 : 19:04:53
yes Peso
thanks for reply
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-09 : 22:12:52
Peso, if this individual was using newSequentialID(), opposed to NewID(), are you aware of a more efficiant way to retrieve it from a stored procedure than the method I posted?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-10 : 04:17:17
It depends. The c1 column might already have a default value NEWID() or NEWSEQUENTIALID() function.
Your method works, but is there really a need for a table variable to hold the output for one scalar value?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -