Solved

return a value from an INSERT statement

Posted on 2000-05-03
13
236 Views
Last Modified: 2008-02-01
I am trying to return the value of the item that was just inserted using a stored procedure.  I have 2 stored procedures, GET_ID, and NEW_ID.  When I call NEW_ID, I want it to create a new ID in the table (based on a formula) and return the value of the new id.  I cannot use identity as the value is not autogenerated, it is calculated. EX:

Create Procedure test
(
    @in varchar(50)
)
As
    declare @NewID varchar(50)
    set @NewID = CONVERT(varchar(50), NEWID())
   
    insert into table1 (newid)
    values (@NewID)

-- All I want is the value of @NewID returned after this executes.  Any help would be great.

0
Comment
Question by:adjennin
[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
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 6

Expert Comment

by:crsankar
ID: 2772952
Here is an example

CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

While using execute you will have to declare this parameter (@@SUM) as output.

0
 
LVL 2

Expert Comment

by:DawsonB
ID: 2772953
Create Procedure test
(
    @in varchar(50)
)
As
Set NoCount On
    declare @NewID varchar(50)
    set @NewID = CONVERT(varchar(50), NEWID())
     
    insert into table1 (newid)
    values (@NewID)

    Select  NewID
    From    table1
    Where   NewID = @NewID

This shoud see your pocedure return a recordset with one column, NewID, and it's value, the same value you have just put in to it. I noticed that you have a variable @in varchar(50) passed in to the stored procedure, but your code doesn't seem to use it, I assume you meant to use it with your Convert(varchar(50),NewID()) bit?

0
 
LVL 6

Expert Comment

by:crsankar
ID: 2772964
Sorry the last statement is required only if it is a local variable.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:adjennin
ID: 2773010
DawsonB... that was the first thing I tried.  When I execute the procedure, it does not return any records.  It completely ignores the SELECT statement.
0
 

Author Comment

by:adjennin
ID: 2773022
I even tried creating a simpe select procedure GETID and adding

EXEC sp_GETID @input

with the same results.  When I execute the GETID procedure, it works fine and returns records.  when I do the NEWID it does not return any records.
0
 
LVL 1

Expert Comment

by:noonie072398
ID: 2773115
Is this Microsoft SQL Server?

If so what version?

If it is Version 7 did you note the "Set NoCount On" in the example from DawsonB?
0
 

Author Comment

by:adjennin
ID: 2773140
It is MSDE. Which is shrunken SQL 7
0
 

Author Comment

by:adjennin
ID: 2773194
When I put in SET NOCOUNT ON, it returns empty recordsets. Without it, it returns no data.
0
 
LVL 1

Expert Comment

by:noonie072398
ID: 2773198
Not having used that particular product... Does it have the equivalent of Query analyser or can you execute sql in an interactive environment?

If so note the output:

In SQL Server 7 it is possible (when using certain odbc drivers) to have two values returned as record sets. The first is the values expected the second is the output "n rows affected".

If you don't set nocount on then your subsequent operations will be using the last record set returned and everything gets confused.

Happened to me several times before I found out.
0
 

Author Comment

by:adjennin
ID: 2773226
Actually, you can use Access 2000 to interface with MSDE and do all your work from there.  Since I am only trying to retrieve one value, can I just return the NEW_ID value that is generated instead of a recordset with a single piece of data in it?
0
 
LVL 2

Expert Comment

by:DawsonB
ID: 2773242
Ok, this is actually more efficient, but is this bit:
    set @NewID = CONVERT(varchar(50), NEWID())
 supposed to look something like
    set @NewID = CONVERT(varchar(50), @in())
???

As far as I can tell, the sp as it is written wont do anything with @in.
Your Convert is on NewID?




Create Procedure test
(
    @in varchar(50)
)
As
Set NoCount On
    declare @NewID varchar(50)
    set @NewID = CONVERT(varchar(50), @in())
     
    insert into table1 (newid)
    values (@NewID)

    Select  @NewID as NewID

Try that,
0
 
LVL 2

Accepted Solution

by:
DawsonB earned 40 total points
ID: 2773254
Sorry, I didn't explain

Select @NewID as NewID will return a recordset of one field named NewID, with the value of the Variable @NewID, and as there is no reading of a table, it is both more efficient, and more correct in this case.
The problem I think though is as I mentioned in my previous message regarding the convert of @in & newid.
0
 

Author Comment

by:adjennin
ID: 2773266
Works great!
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 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