adjennin
asked on
return a value from an INSERT statement
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.
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.
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?
(
@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(
Sorry the last statement is required only if it is a local variable.
ASKER
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.
ASKER
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.
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.
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?
If so what version?
If it is Version 7 did you note the "Set NoCount On" in the example from DawsonB?
ASKER
It is MSDE. Which is shrunken SQL 7
ASKER
When I put in SET NOCOUNT ON, it returns empty recordsets. Without it, it returns no data.
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.
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.
ASKER
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?
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,
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works great!
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.