Professional Web Applications Themes

Create Identity Field - Microsoft SQL / MS SQL Server

I have a need for a custom identity field. NewID() is absolute overkill and too awkward and Identity is far too simple and database specific. I can easily create a stored procedure to create this, but how would I tie it to the field? I've been playing around with stored procs, defaults, formula fields, and user defined functions but I cannot see how to put it together. It could be done as a trigger, but I would prefer the ability to manually get the id before I create the record as well....

  1. #1

    Default Create Identity Field

    I have a need for a custom identity field. NewID() is absolute overkill and
    too awkward and Identity is far too simple and database specific. I can
    easily create a stored procedure to create this, but how would I tie it to
    the field? I've been playing around with stored procs, defaults, formula
    fields, and user defined functions but I cannot see how to put it together.
    It could be done as a trigger, but I would prefer the ability to manually
    get the id before I create the record as well.


    Stephen Guest

  2. #2

    Default Re: Create Identity Field

    Create the ID in your INSERT statement for each new row:

    INSERT INTO Sometable (idcol, colA, colB)
    SELECT COALESCE(MAX(idcol),0)+1, 'Blah blah bah', 'Nothing'
    FROM Sometable

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Stephen Brown" <net> wrote in message
    news:IkiTa.3794$.. 
    and 
    together. 


    David Guest

  3. #3

    Default Re: Create Identity Field

    An identity is defined on a column. That is, it is specified in the table
    definition that a column is an identity column.

    CREATE TABLE [Table5] (
    [idCol] [int] IDENTITY (1, 1) NOT NULL
    ) ON [PRIMARY]
    GO

    Each time a row is inserted into this table, idCol automagically
    increments.
    The system variable IDENTITY can be used in a stored proc to capture the
    value of the most recent insert into a table with an identity column
    performed by that stored proc. IDENTITY itself does not generate an new
    identity value.

    regards, -marty nicholson

    "Stephen Brown" <net> wrote in message
    news:IkiTa.3794$.. 
    and 
    together. 


    Martin Guest

  4. #4

    Default Create Identity Field

    You may create user-defined function to generate values
    you need and set this function as default for the field.
     
    absolute overkill and 
    specific. I can 
    would I tie it to 
    defaults, formula 
    to put it together. 
    ability to manually 
    andsm Guest

Similar Threads

  1. Replies: 5
    Last Post: November 8th, 05:25 PM
  2. Using a from field to create URL
    By Taffydog in forum Dreamweaver AppDev
    Replies: 3
    Last Post: March 26th, 07:57 PM
  3. Recordset doesn't return identity field
    By A_X_L_V in forum ASP Components
    Replies: 7
    Last Post: October 21st, 02:48 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