Solved

Stored Procedure Output Parameter

Posted on 2004-08-16
6
497 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

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

Suggested Solutions

Title # Comments Views Activity
Refresh Dev server with Production database 8 28
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 33
SQL view 2 27
SSRS Enable Remote Errors 4 26
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

773 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