Post reply

Query/UDF help

  • November 2, 2007 at 2:01 pm

    #180912

    Hi all,

    I have spent hours on this and don’t seem to be able to get the results I need.

    I have a table, which stores a single number, which is used to select the next available number for the system ID. This table and how it works I can not alter (vendor application).

    What I need is to be able to for each row in a result set select the number from this table, then add one to the number, and update the table.

    That is if the number starts at 100, and my query has five records in the record set, I need to see in the query result:

    RecIDData

    100A

    101B

    102C

    103D

    104E

    While the table which stores the next available number would end with a value of 105.

    If I were designing the source system myself, I would use an auto incrimenting number for the source. Which would be easier, as I could simply insert the data, and the next ID would be automatically set.

    I worked with user defined functions, and can get the table value returned, but it appears UDF’s can not update the source table. Any suggestions on how to pull this one off?

  • November 2, 2007 at 9:52 pm

    #747969

    If I understand you right yo need:

    1) Insert result of your query into #Table with identity(1,1) column.

    2) Insert (or Update) into static table from #Table, new Number will be MAX(Number) + #Table.ID.

    _____________
    Code for TallyGenerator

  • November 2, 2007 at 9:59 pm

    #747970

    Ugh! A "Sequence" table... I hate 'em... If you don't already hate 'em, you will.;)

    Ok, David... let's pretend that the following is what your 3rd party sequence table looks like...

    --===================================================================

    -- Create a table that simulates the vendor's "NextID" table

    -- and seed it with the value of 100 as posted

    --===================================================================

    CREATE TABLE dbo.NextID (ID INT)

    INSERT INTO dbo.NextID (ID)

    SELECT 100

    ... you need to build a stored procedure that looks like this... (you should probably add some safeguards to prevent 0 and negative increments)...

    [Code] CREATE PROCEDURE dbo.GetNextID

    --===================================================================

    -- Create a stored proc to get the NextID and do an increment

    --===================================================================

    --===== Declare I/O parameters

    @Increment INT, --Qty of ID's to "reserve"

    @NextID INT OUT --NextID currently stored in the table

    AS

    --===== Declare local variables

    DECLARE @IncrementedID INT

    --===== Get the NextID while applying the increment

    UPDATE dbo.NextID

    SET @IncrementedID = ID = ID + @Increment,

    @NextID = @IncrementedID - @Increment

    GO[/code]

    THEN the fun begins... you can either use a cursor and step through stuff (please don't do that to perfectly good code 😛 ), or you can do the following (read the comments... I used a very simple query just to demo) ...

    --===== Make sure our "result set" table doesn't already exist

    IF OBJECT_ID('TempDB..#MyResult','U') IS NOT NULL

    DROP TABLE #MyResult

    --===== Define local variables

    DECLARE @Increment INT

    DECLARE @NextID INT

    --===== Exec a query and put result set into a temp table including a zero based IDENTITY

    SELECT TOP 5

    RowNum = IDENTITY(INT,0,1),

    CustomerID,CompanyName

    INTO #MyResult

    FROM NorthWind.dbo.Customers

    --===== Capture the number of rows in the result set for

    -- our increment

    SELECT @Increment = @@ROWCOUNT

    --===== Get the NextID using the increment

    EXEC dbo.GetNextID @Increment, @NextID OUT

    --===== Select everything from the result set table and add the NextID

    -- to the zero based row number

    SELECT RowNum+@NextID AS ID,

    CustomerID,CompanyName

    FROM #MyResult

    --===== Just for grins, show the current content of the NextID table

    SELECT * FROM dbo.NextID

    The reason why the GetNextID proc is written the way it is, is to prevent the inevitable deadlocks that would occur if you did an UPDATE/SELECT in a transaction to keep people from sneeking numbers in. And, warning, do not ever include calls to the GetNextID proc in any explicit transaction or any transaction that could be rolled back... never. Result will be the same as what we had... blocked code and an average of 640 deadlocks per day :w00t:

    Lemme know if you have any questions on this...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 2, 2007 at 10:12 pm

    #747972

    Jeff Moden (11/2/2007)

    Ugh! A "Sequence" table... I hate 'em... If you don't already hate 'em, you will.;)

    Once, when I gave up to convince developers not to use "sequence" tables I just replaced it with a simple view:

    [Code]SELECT MAX(ID_col) as LastTableID from TheTable[/Code]

    Not to spoil their day I added INSTEAD OF trigger for everything:

    [Code]DECLARE @Message nvarchar (50)

    SET @Message = 'F**k off!'[/Code]

    Then I happily proceeded with set-based inserts.

    Can you imagine their faces when they realized that all those careful row-by-row selects/updates they wrote for 6 month where directed straight to NULL device?

    😀

    _____________
    Code for TallyGenerator

  • November 2, 2007 at 10:18 pm

    #747973

    Heh... now THAT's entertainment 😀 Would have loved to see their faces...:w00t:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 6, 2007 at 8:49 am

    #748720

    Thanks Jeff.

    That helps alot!

    Ugh, this vendor system is filled with the not set based, and such logic. Its enough to make one wonky.

  • November 6, 2007 at 10:11 pm

    #748967

    You bet... thanks for the feedback. Gotta love those "3rd party vendors". 😛 Only reason I knew how to do this is because I had to fix one at work...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply