Last post Nov 27, 2017 06:42 AM by Deepak Panchal
Nov 24, 2017 05:23 PM|gunderj|LINK
I have this stored procedure to return an incremented integer to an insert statement also shown below. This effectively simulates oracle's nextval function on a sequence object but does not work - at runtime I get error...
Invalid column name 'get_next_val'
I cannot use SQL Server's identity column to solve this problem because I want the same or similar approach for other RDBMS's.
How can I make the stored provedure return an incremented value to the insert? Thanks
CREATE PROCEDURE get_next_val
DECLARE @ret INT
SELECT @ret = (next_val + 1) FROM next_val;
UPDATE next_val set next_val = @ret
insert into Table_1(x1,row_num) select 'xxx', get_next_val
Nov 24, 2017 05:35 PM|PatriceSc|LINK
Which version? You do have
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql starting with SQL Server 2012.
BTW I noticed once that Oracle does have identity columns:
IMO it is nice when the sequence is anyway bounded to a table as you can just omit this column and it just works without having to tell again and again which sequence to use.
Nov 24, 2017 06:29 PM|gunderj|LINK
The solution has to work for Oracle 10 and above, and for SQL Server versions 2005 and above. It has to work for bulk insert without using identify feature. Thanks. Maybe I should have simply asked how do I return a single value from an SP to a select
Nov 24, 2017 07:27 PM|PatriceSc|LINK
EXEC @Value=MyProc but this is really not suitable (or even AFAIK possible) to use that as part of a SELECT statement.
What is the syntax you would use in other DBMS and that you want to "emulate"? My approach would be perhaps to have a db dependent procedure to generate numbers in a table and then use this table to provide numbers to my final query...
Make sure also you'll get some real benefit compared to maybe have two strategies for your import (one for identity, and one for sequence numbers).
Nov 24, 2017 09:23 PM|gunderj|LINK
All good advice, but my requierment is still appropriate for our needs. The select would have to be select col1, col2, get_next_val from myTable. I cannot use select col1, col2,
exec get_next_val from myTable, and I cannot use a function because in SQL Server a function cannot update the work table to set the changed increment.
Thanks if you can help.
Nov 27, 2017 06:42 AM|Deepak Panchal|LINK
you had mentioned that,"I cannot use SQL Server's identity column to solve this problem because I want the same or similar approach for other RDBMS's."
As we are only provide support for MS SQL. so for MS SQL I suggest you to use
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [dbo].[AddEmployeeReturnIDwithoutput]
@id int output
SET NOCOUNT ON;
INSERT INTO Employees (FirstName, LastName, BirthDate, City, Country)
VALUES (@FirstName, @LastName, @BirthDate, @City, @Country)
String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "AddEmployeeReturnIDwithoutput";
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim();
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();
cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim();
cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();
cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();
cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Connection = con;
string id = cmd.Parameters["@id"].Value.ToString() ;
lblMessage.Text = "Record inserted successfully. ID = " + id;
catch (Exception ex)
Return Identity value from Stored Procedure in SQL Server