Author |
Topic |
hdv212
Posting Yak Master
140 Posts |
Posted - 2008-08-08 : 12:31:18
|
hii 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 ASINSERT [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 19Operand 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) |
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2008-08-08 : 15:02:58
|
thanks for replywhen 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 uniqueidentifierexec dbo.sp_t2_Insert @x,6select @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 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2008-08-08 : 15:41:54
|
you need to specify outputdeclare @x uniqueidentifierexec dbo.sp_t2_Insert @x output,6select @x |
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2008-08-08 : 15:56:02
|
thanks Vinnie881my problem was solved. |
|
|
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 ASset nocount onif @c1 is null set @c1 = newid()INSERT [dbo].[t2]( [c1], [c2])VALUES( @c1, @c2) E 12°55'05.25"N 56°04'39.16" |
|
|
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 ASset nocount onif @c1 is null set @c1 = newid()INSERT [dbo].[t2]( [c1], [c2])VALUES( @c1, @c2)
do you think this sp return inserted pk record ? |
|
|
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" |
|
|
hdv212
Posting Yak Master
140 Posts |
Posted - 2008-08-09 : 19:04:53
|
yes Pesothanks for reply |
|
|
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? |
|
|
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" |
|
|
|