Results 1 to 5 of 5

Thread: Increment field

  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Increment field

    Hi,

    I have a sproc that inserts an XML document into a table. Therefore, many rows are created at once. One of the fields of my table is called JobNumber.

    How can I perform the insert such that JobNumber starts at 1 and is incremented for each inserted job upto n jobs.

    I do not want an identity column since the the ID is a different field called JobSearchSet.

    Thus one JobSearch might generate XML for 5 jobs therfore they will be numbered 1 to 5

    A different Job search (and hence a different JobSearchSet) might generate XML for 140 jobs which I want numbered 1 to 140.

    Any ideas.

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    i suppose u r inserting using a loop. Create a local variable of datatype number and initialize it to 0
    In your loop before inserting the record, add 1 to the local variable.
    In this way at the first insert the local variable will be 1, second insert will be 2 etc.
    If your procedure inserts more than 1 XML doc, be sure to reset the variable at the right time.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Increment Field

    Thanks,

    No I dont use a loop. I use SQLPrepareDocument. I've thought about a trigger but the sproc might be called simultaneously by several users and I want to guarantee a consequtive number from 0 to n for each row as it relates to each XML doc being inserted. I think a trigger might get confused. I know that Oracle has a SEQUENCE function however, I can not find similar in SQL 2000. Only one XML document is being inserted and all the elements within it are dealt with transparently.

    Here is the sproc. As you can see, I have declared a local variable @JobNumber. I've tried to select it and increment it in one statement (similar to what you would do if generating ID's from a table) however it will not accept the syntax.

    Any ideas?


    CREATE PROCEDURE dbo.Insert_tblSearchSetResults
    @SearchSet varchar(1000),
    @Mode varchar(1),
    @XMLData text
    AS
    BEGIN
    DECLARE @idoc int
    DECLARE @doc varchar(1000)
    declare @JobNumber tinyint

    SET NOCOUNT ON

    -- set the jobNumber to 1
    SET @JobNumber = 1

    -- remove first 77 characters from search set since they will always be the same
    SET @searchSet = SUBSTRING(@searchset, 78, LEN(@searchSet)-77)

    -- update cache stats
    EXEC UpdateSearchSetCacheStats @searchSet, 1 -- 1 idicates creation of results

    --Create an internal representation of the XML document.
    EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLData

    IF EXISTS (SELECT [ID] FROM sysobjects WHERE([Name] = 'tblSearchSetResults'))BEGIN

    -- SELECT stmt using OPENXML rowset provider
    INSERT INTO [ESAPI].[tblSearchSetResults]
    SELECT @SearchSet AS [SearchSetID],
    @Mode AS [SearchMode],
    *,
    @JobNumber,
    GETDATE() AS [timeCached]
    FROM OPENXML (@idoc, '/JobVerbose/Record',2)
    WITH (
    RecordsReturned int '../RecordsReturned',
    MatchCount int '../MatchCount',
    VacID varchar(30),
    VacTitle varchar(130),
    VacSocCode varchar(5),
    VacWageText varchar(52),
    VacHoursText varchar(60),
    VacWage varchar(52),
    VacWageQualifier varchar(2),
    VacHours float,
    VacCurrency varchar(3),
    VacHoursQualifier varchar(9),
    Location varchar(40),
    VacPermTempFlag varchar(1),
    DateReceived varchar(8),
    ESVac varchar(1),
    Quality int,
    ErrorCode varchar(10) '../ErrorCode',
    ErrorDescription varchar(250) '../ErrorDescription'
    )
    END ELSE BEGIN

    -- SELECT stmt using OPENXML rowset provider
    SELECT @SearchSet AS [SearchSetID],
    @Mode AS [SearchMode],
    *,
    GETDATE() AS [timeCached]
    INTO [ESAPI].[tblSearchSetResults]
    FROM OPENXML (@idoc, '/JobVerbose/Record',2)
    WITH (
    RecordsReturned int '../RecordsReturned',
    MatchCount int '../MatchCount',
    VacID varchar(14),
    VacTitle varchar(130),
    VacSocCode varchar(5),
    VacWageText varchar(52),
    VacHoursText varchar(60),
    VacWage varchar(52),
    VacWageQualifier varchar(2),
    VacHours float,
    VacCurrency varchar(3),
    VacHoursQualifier varchar(9),
    Location varchar(40),
    VacPermTempFlag varchar(1),
    DateReceived varchar(8),
    ESVac varchar(1),
    Quality int,
    ErrorCode varchar(10) '../ErrorCode',
    ErrorDescription varchar(250) '../ErrorDescription'
    )
    END

    EXEC sp_xml_removedocument @iDoc

    SET NOCOUNT OFF
    END
    GO

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    because this looks like MSSQL server, i cannot make any comments on ur code. In oracle is works the way i described by just adding a counter and incrementing at at the right time, or using a sequence.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Thanks again.

    I have implemented a solution. Basically, I use a local table variable with an identity column. Because it's local to the sproc it will generate a consequtive sequence and will not be interfered with by other users calling the sproc. At the end I then insert * from the local table into the main table.

    This is not ideal since there are now two inserts and there will be a possibility of locking issues when several instances of the sproc are trying to perform the final insert into the main table.

    Will have to wait and see.

    Regards

    the_little_hairy_welshman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •