Solved

Stored Procedure Output Parameter

Posted on 2004-08-16
6
495 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now