locked
how to call stored procedure for a returned integer to be a value in a select result set RRS feed

  • Question

  • User1868852945 posted

    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

    AS

    DECLARE @ret INT

    SELECT @ret = (next_val + 1) FROM next_val;

    UPDATE next_val set next_val = @ret

    RETURN @ret

    GO

    insert into Table_1(x1,row_num) select 'xxx', get_next_val

    Friday, November 24, 2017 5:23 PM

Answers

All replies

  • User753101303 posted

    Hi,

    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: https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 24, 2017 5:35 PM
  • User1868852945 posted

    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 statemet?

    Friday, November 24, 2017 6:29 PM
  • User753101303 posted

    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).

    Friday, November 24, 2017 7:27 PM
  • User1868852945 posted

    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.

    Friday, November 24, 2017 9:23 PM
  • User347430248 posted

    Hi gunderj,

    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 Identity value.

    example:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[AddEmployeeReturnIDwithoutput] 
          @FirstName varchar(50),
          @LastName varchar(50),
          @BirthDate datetime,
          @City varchar(50),
          @Country varchar(50),
          @id int output
    AS
    BEGIN
          SET NOCOUNT ON;
          INSERT INTO  Employees (FirstName, LastName, BirthDate, City, Country)
          VALUES (@FirstName, @LastName, @BirthDate, @City, @Country) 
          SET @id=SCOPE_IDENTITY()
          RETURN  @id
    END
    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;
    try
    {
        con.Open();
        cmd.ExecuteNonQuery() ;
        string id = cmd.Parameters["@id"].Value.ToString() ; 
        lblMessage.Text = "Record inserted successfully. ID = " +  id;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        con.Dispose();
    }

    Reference:

    Return Identity value from Stored Procedure in SQL Server

    Regards

    Deepak

    Monday, November 27, 2017 6:42 AM