Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50

    Unanswered: MS SQL Server Locking problem!

    Hi,
    MS SQL server has many sort of locking levels and locking modes.
    Among all of these can I know what best suit for me when simulating the identity column property to avoid dublicating rows with same number, such as :
    ---------------------------------------------------------------
    CREATE PROC NewNumber
    @OwnerNo int,
    @ReturnValue int OUTPUT
    AS

    DECLARE @NewNum int
    DECLARE ctr_cursor CURSOR FOR
    SELECT MAX(MessageNo) AS MaxNumber FROM tblMessages WHERE OwnerNo = @OwnerNo

    OPEN ctr_cursor
    FETCH NEXT FROM ctr_cursor INTO @NewNum
    If @@FETCH_STATUS = 0
    BEGIN
    SELECT @NewNum = @NewNum + 1
    END
    ELSE
    BEGIN
    SELECT @NewNum = 1
    END

    SELECT @ReturnValue = @NewNum

    CLOSE ctr_cursor
    DEALLOCATE ctr_Cursor

    GO
    ---------------------------------------------------------------
    I can't use the identity (seed & increment) property with my case because my column is not unique, the uniqueness is built on two columns.

    I would really appreciate your help .
    Thanks
    Khalid Yousef

  2. #2
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50

    Unhappy

    too bad!
    I can't find some one help me
    still waiting
    Khalid Yousef

  3. #3
    Join Date
    Feb 2002
    Posts
    14
    Create a table named UniqueID with 2 columns
    TableName Varchar(15)
    LastID Int

    Add a record with the table or value name and the number you want to start with.

    Create a stored procedure to increase the number and call it when you need to add a record to your other table.

    This will work faster than searching the table every time.



    CREATE procedure GetNextTableUID (@tableName varchar(20)) as

    /* Gets the next UniqueID for the given table.
    * If there is no entry for that table, adds one.
    */

    begin transaction;

    declare @thisUID int;

    set @tableName = lower(@tableName);

    -- Get next UID

    select @thisUID = LastUID + 1
    from UniqueID UPDLOCK HOLDLOCK
    where lower(TableName) = lower(@tableName);

    -- Update UniqueID table

    if ((@@rowcount = 0) or (@thisUID is null)) begin
    set @thisUID = 1;
    insert UniqueID values (@tableName, @thisUID);
    end else
    update UniqueID set LastUID = @thisUID
    where lower(TableName) = lower(@tableName);

    -- Check for errors

    if @@error <> 0 begin
    rollback transaction;
    raiserror( 'SQL Error: GetNextTableUID', 16, -1 )
    return 0;
    end

    commit transaction;
    return @thisUID;



    GO

  4. #4
    Join Date
    Jan 2002
    Location
    Libya
    Posts
    50
    I thanked god, that you were there!

    I recieved two more professional solutions by email, but yours was the best, you got me stright to the point.

    Many many thanks
    All the best
    Khalid Yousef

Posting Permissions

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