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.
| Author |
Topic |
|
toddhd
Yak Posting Veteran
55 Posts |
 Posted - 2005-11-21 : 16:25:58
|
| I have written a stored proc that inserts a record into a database. This is simple stuff, nothing fancy going on. The project specs require that I set the primary key to a uniqueidentifier, which I do, and all that works just fine.What I'd like to do is return that uniqueidentifier so that I can refer to the new record right away. The param is marked as an OUT param, so thought that was all that was needed, but nothing is being returned. I also tried adding a SELECT to return the param, but that caused an error. What am I doing wrong? All I want to do is return the @PackageID below. Thanks for any help you can offer - I'm sure this is an easy one, but my brain just isn't functioning today.CREATE PROCEDURE [dbo].InsertAdaptLibPackage @Language varchar(10), @PackageName nvarchar(128), @PackageType smallint, @PackageDesc nvarchar(512), @CategoryCode varchar(6), @VersionNote nvarchar(512), @AdaptAuthor nvarchar(128), @AppName nvarchar(100), @AppVersion nvarchar(20), @ClientName nvarchar(256), @ImplementDate datetime, @UserID nvarchar(256), @ModUserID nvarchar(256), @DateAdd datetime, @DateMod datetime, @Deleted bit = NULL, @DateLastExport datetime = NULL, @PackageID uniqueidentifier OUTPUTASSET @PackageID = NEWID()INSERT INTO [dbo].[AdaptLibPackage] ( [PackageID], [Language], [PackageName], [PackageType], [PackageDesc], [CategoryCode], [VersionNote], [AdaptAuthor], [AppName], [AppVersion], [ClientName], [ImplementDate], [UserID], [ModUserID], [DateAdd], [DateMod], [Deleted], [DateLastExport]) VALUES ( @PackageID, @Language, @PackageName, @PackageType, @PackageDesc, @CategoryCode, @VersionNote, @AdaptAuthor, @AppName, @AppVersion, @ClientName, @ImplementDate, @UserID, @ModUserID, @DateAdd, @DateMod, @Deleted, @DateLastExport)GO |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 16:35:14
|
| When you invoke the stored procedure does the row get created in the table?When you say you tried doing a select of the variable you mean that after the insert statement you had:select @packageidand you got an error?OUTPUT parameters are handled via code by binding some variable to that parameter, they do not get output in a results window or as results. If you want to execute the command in your code and have the value actually returned as a RESULT instead of binding to it as a parameter you should:Remove the parameter from your definition, then instead have the first line of your proc as:declare @PackageId uniqueidentifierKeep the rest the same, then do the select @PackageId at the end.That should allow you to assign a value to it, that will then get used for the insert, then will return it as a result |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-21 : 16:52:39
|
| The stored procedure is written correctly. Post the code that you are using to execute the stored procedure.Tara Kizeraka tduggan |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-21 : 17:06:30
|
| You need to think differently with GUIDs than you would with identity values. With an identity, you are probably used to calling scope identity to get the value assigned by the database. With GUIDs, you can create the new ID value in your interface and pass it to the database through your stored procedure. Since it is a GUID, you know that it does not duplicate an existing value, and this saves you having to process any returned value from the database.Leave newid() as a default for data that is bulk inserted, but for inserts from your interface send a GUID rather than relying on the default. |
|
|
|
|
|
|
|
|
Sproc not returning guid pri. key?