?
Solved

Stored Procedure Output Parameter

Posted on 2004-08-16
6
Medium Priority
?
503 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 200 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

752 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