none
How to use a variable to identify a Sql sequence RRS feed

  • Question

  • i there,

    i'm developing a app to print serial numbers and based on a product i want to use a one of the sequence created

    i have 3 sequence created on MS Sql, seq_a, seq_b and seq_c

    on the start i ask the user to read the production order, and based on that number i get a product reference, based on the product reference i then get the variable to load if it's seq_a ,b or c.

    but i cannot pass it to the sql Query

     "insert into table a with (tablockx) (nome, of, sn, ref, lbldate ,QRCode,date ,op ,posx,posy,printid,controlo)output inserted.qrcode
            values (@nome, @of, next value for seq_c, @refcode, replace(cast(convert(varchar,getdate(),102) as varchar(10)),'-',''),
            @refcode+replace(cast(convert(varchar,getdate(),5)as varchar(10)),'-','')+right('000'+cast((next value for seq_c )as varchar (4)),4), getdate(), @op,@labelx,@labely, @printid, 'xx' );"

    if i write next value for seq_c it works but i want next value for "seq_variable"

    Monday, December 16, 2019 10:39 AM

Answers

  • Maybe in this case you can build a dynamic query, i.e.  construct the ‘next value from XXX’ part using string concatenation (& operator), where ‘XXX’ depends on required target sequence.

    But you can also pass a new parameter to this query — ‘@seq_variable’, with possible values: “a”, “b”, “c”. Then extend the query to include a part like this:

    “declare @i int

    if @seq_variable  = 'a'

                    set @i = next value for Seq_a

    else if @seq_variable  = 'b'

                    set @i = next value for Seq_b

    else

                    set @i = next value for Seq_c

    insert into … values ( …, @i, … )”



    • Edited by Viorel_MVP Monday, December 16, 2019 8:19 PM
    • Marked as answer by Nrdroque Tuesday, December 17, 2019 4:41 PM
    Monday, December 16, 2019 8:17 PM

All replies

  • Hello,

    If you are asking how to get the "NEXT VALUE" for a sequence then the following is how to do that.

    Given the following sequence

    CREATE SEQUENCE dbo.GetInvoiceNumber
        AS INT
        START WITH 1
        INCREMENT BY 1
        MINVALUE 1
        MAXVALUE 999
        CYCLE
        CACHE 10;

    You get the next value

    ''' <summary>
    ''' Requires a open connection
    ''' </summary>
    ''' <param name="cn"></param>
    ''' <returns></returns>
    Public Function GenerateInvoice(cn As SqlConnection) As String
    
        Dim sql As String = "SELECT CONVERT(VARCHAR(4), GETDATE(), 12) " &
                            "+ RIGHT('0000' + CAST( NEXT VALUE FOR " &
                            "GetInvoiceNumber AS VARCHAR(3)),4)"
    
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = sql}
            Return cmd.ExecuteScalar.ToString
        End Using
    
    End Function

    Then to be variable do this

    ''' <summary>
    ''' Requires a open connection
    ''' </summary>
    ''' <param name="cn"></param>
    ''' <returns></returns>
    Public Function GenerateInvoice(cn As SqlConnection, sequence As String) As String
    
        Dim sql As String = "SELECT CONVERT(VARCHAR(4), GETDATE(), 12) " &
                            "+ RIGHT('0000' + CAST( NEXT VALUE FOR " &
                            $"{sequence} AS VARCHAR(3)),4)"
    
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = sql}
            Return cmd.ExecuteScalar.ToString
        End Using
    
    End Function


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Monday, December 16, 2019 12:34 PM
    Moderator
  • Also, I'm surprised that the query statement runs at all as when placed into SSMS I get tons of errors and use of reserved words


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, December 16, 2019 12:44 PM
    Moderator
  • Maybe in this case you can build a dynamic query, i.e.  construct the ‘next value from XXX’ part using string concatenation (& operator), where ‘XXX’ depends on required target sequence.

    But you can also pass a new parameter to this query — ‘@seq_variable’, with possible values: “a”, “b”, “c”. Then extend the query to include a part like this:

    “declare @i int

    if @seq_variable  = 'a'

                    set @i = next value for Seq_a

    else if @seq_variable  = 'b'

                    set @i = next value for Seq_b

    else

                    set @i = next value for Seq_c

    insert into … values ( …, @i, … )”



    • Edited by Viorel_MVP Monday, December 16, 2019 8:19 PM
    • Marked as answer by Nrdroque Tuesday, December 17, 2019 4:41 PM
    Monday, December 16, 2019 8:17 PM