Professional Web Applications Themes

Stored Procedure Help - Microsoft SQL / MS SQL Server

Goal: I need to have a stored procedure that will increment an integer value in a control table column every time it is called and return that number to the caller. The procedure will be called where columns name the procedure to generate their default value. It will be used for by more columns than simply the PK values. It would also be called by external programs, and return a number to them as well. identity won't work because it isn't unique across all tables, only works for the identity column, and requires a row insert to trigger it. UniqueIdentifier ...

  1. #1

    Default Stored Procedure Help

    Goal:
    I need to have a stored procedure that will increment an integer value
    in a control table column every time it is called and return that number to
    the caller. The procedure will be called where columns name the procedure
    to generate their default value. It will be used for by more columns than
    simply the PK values. It would also be called by external programs, and
    return a number to them as well. identity won't work because it isn't
    unique across all tables, only works for the identity column, and requires a
    row insert to trigger it. UniqueIdentifier won't work because it doesn't
    return an integer.

    I have a table named GlobalID. It contains a single row with two
    columns; ID_GlobalID, and GlobalID. The GlobalID column stores the last
    number given away for a database column whose default is derived at by
    calling a stored procedure named GetGlobalID.

    How do I write an SP that will do this?

    Thank you for your thoughts,
    Jack T.


    Jack Guest

  2. #2

    Default Re: Stored Procedure Help

    Jack
    Consider to checl MAX value of the primary column
    CREATE TABLE #TEMP
    (
    COL INT NOT NULL PRIMARY KEY,
    COL1 CHAR(1) NOT NULL
    )

    INSERT INTO #TEMP VALUES (1,'A')
    INSERT INTO #TEMP VALUES (2,'B')


    INSERT INTO #TEMP SELECT MAX(COL)+1,'C' FROM #TEMP
    SELECT * FROM #TEMP

    Note: You need to check for example If someone deletes the row so what value
    do you expect to insert?

    "Jack T." <com> wrote in message
    news:phx.gbl... 
    to 



    Uri Guest

  3. #3

    Default Re: Stored Procedure Help

    Consider this not so unusual situation:
    - The Images table stores images for many tables. The table consists of two
    columns. The first is the ID_Images which is an identity column. The Second
    column is Image, which is type BLOB, and contains employee pictures,
    employee signatures, part prints, part pictures, etc. There are more tables
    like this. Tables for add-hoc notes is another.

    - Since the Images table is used by many tables, the normal method of
    storing the Identity created PK value of the parent tables in the Images
    child table as an FK value will not work for two reasons. The first reason
    is that it is very likely with multiple parent tables, the same FK value
    would be created in the Images table from multiple parents. This makes
    coherent RI impossible as well as displaying the correct image for the
    parent row. The second reason is that a single row in a parent table has
    multiple columns in the Images table. In the case of the Employees table, a
    reference to the Images table for both the employee picture and signature
    need to be stored within the same row.

    - One obvious solution is to store the PK value of the Images table in a
    column in the parent table's row. Then setup Cascade/Cascade so that the
    images would automatically be maintained or deleted using the reference in
    the parent row. However I do not wish to use that method for a couple of
    reasons, one being that I would also have to update the parent row with the
    PK of the new Images row. I instead want to create a globally unique value
    for the columns that will be used not only for PKs, but also for other
    columns that store linking values to the Images table. This also allows me
    to link tables employing the more conventional method of using an FK column
    in the Images table.

    - I want ot be able to use this functionality two ways. One is to call this
    SP from a programming language to be able to assign these values at any time
    before inserting rows. The second to use it to generate the value when the
    row makes it to the DBMS without a value being set by the application.

    Any other ideas of how to accomplish this same goal are welcome. I did
    think about using type UniqueIdentifier and NewID. I don't know how
    efficient they are to use when used as PK values and unique values to tie
    rows together due to their size and inability to be ordered. I do know that
    you can't read them if you try to edit the data. That's why I'm looking at
    using an integer or similar type. I don't know if I can call NewID from an
    external program.

    Thanks,
    Jack T.


    Jack Guest

  4. #4

    Default Re: Stored Procedure Help

    CREATE TABLE GlobalID (
    ID_GlobalID DEFAULT (1),
    GlobalID INT DEFAULT (0),
    CONSTRAINT GLOBALID_PK_GlobalID PRIMARY KEY CLUSTERED
    (ID_GlobalID)
    )

    The GlobalID table contains one row. The GlobalID column holds a number
    that is incremented by 1, each time stored procedure is called. The stored
    when called also returns the incremented number to the caller. Let's call
    the stored procedure GetGlobalID().

    /***********************************************/

    CREATE TABLE Employees(
    ID_Employee INT DEFAULT (GetGlobalID()),
    Name VARCHAR(20) NOT NULL,
    ID_Picture INT DEFAULT (GetGlobalID()),
    ID_Signature INT DEFAULT (GetGlobalID()),
    CONSTRAINT PK_Employees PRIMARY KEY (ID_Employee),
    CONSTRAINT UK_Name UNIQUE (Name))


    The Employees table has two references in it to the Images table;
    ID_Picture, and ID_Signature. These columns are initialized
    with a number given them by the SP GetGlobalID just as the
    PK column ID_Employee was.

    /***********************************************/

    CREATE TABLE Quotes(
    ID_Quote INT DEFAULT (GetGlobalID()),
    Name VARCHAR(20) NOT NULL,
    ID_PartPrint INT DEFAULT (GetGlobalID()),
    CONSTRAINT PK_Employees PRIMARY KEY (ID_Quote))


    Quotes often have part prints associated with them The Quotes
    table stores it's PK value in the Images table FK_Image to
    link this row to the image of the part print. The PK column in
    the Quotes table also gets its value from the SP GetGlobalID.

    /***********************************************/

    CREATE TABLE Images (
    ID_Image INT DEFAULT (GetGlobalID()),
    Image IMAGE NULL,
    FK_Image INT NOT NULL,
    CONSTRAINT IMAGES_PK_Images PRIMARY KEY CLUSTERED
    (ID_Image)
    )

    The FK_Image column stores the value of ID_Picture or the
    ID_Signature of the Employees table, or ID_Quote from the
    Quotes table. Thus each row in the Employees table can reference
    up to 2 rows in the images table while each row of the Quotes
    table references one image in the Images table. When an
    Employees row is deleted, up to two rows in the Images table are
    deleted and when a Quotes row is deleted, up to one row in the
    Images table is deleted. More tables than just Employees and
    Quotes store images in the Images table.

    /***********************************************/

    Identity cannot work for PK columns because because it isn't
    unique across tables, it only works for the identity column,
    and requires a row insert to trigger it. I also want to be able to
    call it from a client program to recieve a unique ID without
    having to insert a row to do it. UniqueIdentifier won't work
    because it doesn't return an integer. Thus what I need is a means
    to not only provide a unique default value to columns in rows
    being inserted, but also be available to return to a client
    application the next unique number.

    Thanks,
    Jack T.


    Jack Guest

  5. #5

    Default Re: Stored Procedure Help

    >> The Images table stores images for many tables. The table consists of
    two columns. The first is the ID_Images which is an identity column. The
    Second column is Image, which is type BLOB, and contains employee
    pictures, employee signatures, part prints, part pictures, etc. There
    are more tables like this. <<

    Back to RDBMS Basics 101. A table should have the attributes of the
    entity it models. A picture of a thing is certainly an attribute of
    that thing. A picture of an employee is not the same thing as a picture
    of octopus. So, why are putting these totally different things into one
    honking big garbage pile?

    The reason you are having problems is that the design is flawed.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  6. #6

    Default Re: Stored Procedure Help

    >So, why are putting these totally different things into one honking big
    garbage pile? The reason you are having problems is that the design is
    flawed for it!<

    You don't like my garbage pile?<g> What I am attempting to do is to
    allow for the flexibility of having nearly any table to attach ad-hoc images
    as necessary. I also would like to do that with notes. When the the row
    from the table is deleted, I also want the images and notes associated with
    that row to disappear as well. I would rather not store the PK of the
    Images table so I don't have to update the parent when I attach an image or
    note. How would you accomplish this?

    Great to hear from you again,
    Jack Toering


    Jack Guest

  7. #7

    Default Re: Stored Procedure Help

    > Here I had to to guess what function ID_GlobalID fills. I cannot see that 

    The ID_GlobalID column is simply the PK for the GlobalID table and is set to
    1. Since it is a single row table, it makes it easy to grab this row when
    when I want to updated the number in GlobalID.
     

    I did not know that.
     

    I would not be doing any multi-row inserts where this would have to occur.
     
    >
    > I don't see how deleting a row in Employees is going lead to a row in
    > Images being deleted, unless you have some trigger to do the work.[/ref]

    Since the Images table is the child table, can't I just set it up each
    column in Employees to DDL cascade/cascade the Images columns? The number
    contained in Employees.ID_Signature and Employees.ID_Picture are unique
    across tables?
     

    That shouldn't be a problem if the number assigned to the numbers assigned
    to Employees.ID_Signature and Employees.ID_Picture and used as an FK value
    in Images is unique across tables. The purpose of using the GlobalID table
    is to ensure that. I could do that. To make RI work I would have to add a
    column to the Employees table indicating the source. However it would
    remove the necessity for the GlobalID table and the SP. What I would be
    missing is the ability to know what the values were at the application level
    before I inserted the value. This is useful when you are constructing both
    the parent and child in memory before inserting them into the database.
     

    I agree that I have, which is why I am here. OTOH what I am trying to
    accompish is common place for many large commercial applications. All of
    them allow add-hoc images and notes to be attached to almost any row in any
    table. Yet none of them have a separate set tables for each table in the
    database to support this functionality. By storing the PK of Images in
    Employees, I would have to update the Employees row when I added an image or
    note. By storing the unique number in Employees and listing it as an FK
    value in Images, I was thinking that I could use DDL to remove the rows in
    Images when the Employees row is deleted although I haven't tried doing it
    yet when one table has two rows in the child.

    I thought about using UniqueIdentifier for all PK and pointer columns to
    maintain uniqueness across tables. However its inability to participate in
    any order makes me think this is not a good idea. Clustered PK indexes
    would not be possible from what I read. I am soliciting thoughts here about
    whether or not it is a good idea to use UniqueIdentifier for PKs and columns
    such as the ID_Signature and ID_Picture columns. I've never used them
    before and am unaware of the pros and cons of using them.

    Thank you very much for your thoughts,
    Jack T.


    Jack Guest

  8. #8

    Default Re: Stored Procedure Help

    You're right. It doesn't work in DDL. That being the case, it doesn't make
    any difference whether I use the GlobalID table or not if I store the PK of
    the child in the parent row. I haven't played with that in DDL, but that's
    next.

    Thanks,
    Jack T.


    Jack Guest

Similar Threads

  1. MS SQL stored procedure
    By lfsxdth in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 23rd, 02:14 PM
  2. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  3. Using a stored procedure
    By MarkWright in forum Coldfusion Database Access
    Replies: 13
    Last Post: April 15th, 05:53 PM
  4. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  5. Stored procedure?
    By SG via DotNetMonster.com in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 23rd, 01:06 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139