Solved

Stored Procedure Output Parameter

Posted on 2004-08-16
6
499 Views
Last Modified: 2012-06-27
I have a stored procedure that I would like to modify to return the guid generated by the sql server....  Actually I am not sure if I want to use an output parameter or return the guid.  As long as it works, I can go either way.  My table is set up so that there are 3 fields, the last of which is set to generate a row guid.

Here is my current Stored Procedure:

CREATE PROCEDURE dbo.NewInsertCommand_table1
(
      @one nvarchar(50),
      @two nvarchar(50),
      @theID uniqueidentifier
)
AS
      SET NOCOUNT OFF;
INSERT INTO Table1(one, two, theID) VALUES (@one, @two, @theID);
      SELECT one, two, theID FROM Table1 WHERE (theID = @theID)
GO

This sp was generated by Visual Studio .net 2003.

Thanks
0
Comment
Question by:forrest321
  • 4
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11814845
CREATE PROCEDURE dbo.NewInsertCommand_table1
(
     @one nvarchar(50),
     @two nvarchar(50),
     @theID uniqueidentifier OUTPUT
)
AS
     SET NOCOUNT OFF;
     @theID = NEWID()                -- Add this line
INSERT INTO Table1(one, two, theID) VALUES (@one, @two, @theID);
     SELECT one, two, theID FROM Table1 WHERE (theID = @theID)
GO
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11814866
Actually:

CREATE PROCEDURE dbo.NewInsertCommand_table1
(
     @one nvarchar(50),
     @two nvarchar(50),
     @theID uniqueidentifier OUTPUT
)
AS
     SET NOCOUNT OFF;
     @theID = NEWID()                -- Add this line
INSERT INTO Table1(one, two, theID) VALUES (@one, @two, @theID);
     -- SELECT one, two, theID FROM Table1 WHERE (theID = @theID)          -- You no longer need this line
GO
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11814879
Also, please follow-up on the abandoned question:
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20971140.html
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 2

Author Comment

by:forrest321
ID: 11814978
line 9: incorrect syntax near '@theID'
line 12: incorrect syntax near GO

awarded points on the xml question.  i had forgotten all about it.  thanks
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 11815603
Sorry about that, let me try it again (maybe I will get it right this time)

CREATE PROCEDURE dbo.NewInsertCommand_table1
(
     @one nvarchar(50),
     @two nvarchar(50),
     @theID uniqueidentifier OUTPUT
)
AS
     SET NOCOUNT OFF
     SET @theID = NEWID()                -- Add this line
INSERT INTO Table1(one, two, theID) VALUES (@one, @two, @theID)
     -- SELECT one, two, theID FROM Table1 WHERE (theID = @theID)          -- You no longer need this line
GO
0
 
LVL 2

Author Comment

by:forrest321
ID: 11882182
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question