adding items to SQL

[Techno]

Hi there

another quick Q

how is it possible to get back a new unique ID number in a table from SQL? The reason is because if u wanna add new items to a DB in SQL, you obviously have to give it a unique id number - no? (im gonna be using Store proc)

i dont think you can just add all the information and let SQL take care of assigning it a unique ID (that is the primary key) - or can u??

[si_the_geek]

Yes you can, just set up the field to be Identity(in SQL Server), or Autonumber(Access), or Serial(several others). You then dont need to add that field when Inserting new records (the database will add it for you).

Assuming you meant SQL Server, just go into the table design in Enterprise Manager, select the field that you want to be unique, then set Identity to True (Seed and Increment should both default to 1, which is fine unless you want particular numbers in the field).

[Techno]

i think i understand

i am using SQL Server enterprise 2000
the DB was not made by me but some guy a long time ago

the ID is a primary key in a table

i have made a store proc which takes in the parameters and inserts them into the table

so should i NOT insert the idnumber as a param in store proc?

how does it work?

if i insert values using the store proc, i take it that SQL will automatically give it a unique ID number (record number)?

[si_the_geek]

basically: the database server knows that the field should have unique numbers, so it keeps track of what has been used, and just assigns the next available unique number to the field.

if you specify a number for the field it may or may not work, depending on whether the value has been used before, and variaous database setup issues (I'm not sure what though, I've only been back from holiday a few days!).

[wey97]

Like si said, you still need to know whether the ID seed and increment are set. You'll have to look at the table design or find out from the designer.

If not, you need some other way to get a unique ID. Assuming it is auto-generated then you don't worry about it in the stored procedure. If you have fields like this in the table:

ID
name
address
phone

then all you need to do in the stored proc. is this.

CREATE PROCEDURE Proc1
    @name nvarchar(32)
    @address nvarchar(64)
    @phone nvarchar(16)
AS
    INSERT INTO [TableName] VALUES(@name, @address, @phone)
GO

You can test it in Query Analyzer with

USE DatabaseName
EXEC Proc1 'Joe Blow', '99 Elm Street', '(123) 123-1234'