question about identity. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

question about identity.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-08 : 10:08:26

jeremy writes "maybe im going about this the wrong way
my table structure is
id = identity
question = varchar
category = integer
answer = varchar

i am going to have to create a random set of 25 questions by category. right now i am creating a select statement like
select * from where id in ( 1, 2, 3, ...)

right now i am using a function in vbscript to create these random numbers, but what i have just realized is that this could become a problem because i could generate numbers that are not in the category that i am pulling from.

is there a way that when inserting data into the table that the id will be created based on the category value ie. category+autonumber?

i know i can do this at run time w/ my app but can sql do this? am i going about this the right way?"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-02-08 : 10:30:53

You can use an insert trigger to do this. Search this site for triggers and you'll find a wealth of information on them.

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-08 : 11:43:29

Jeremy,

You can do the insert within a transaction block. First get the max id for the category then increment that as your new Id. The transaction block will ensure data integrity. HTH.

-- arguments: @question, @category, @answer
declare @question varchar(1000), @category int, @answer varchar(100)
-- local variable: @id
declare @id int

begin transaction

-- get the max id for your category
select @id=max(id)
from mytable
where category=@category

-- now insert
insert into mytable
select id=@id+1, question=@question,category=@category,answer=@answer

if @@error = 0 commit transaction else rollback transaction

Edited by - Lou on 02/08/2002 11:46:03

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-08 : 12:22:46

That will only work in a single user environment as it doesn't lock the table.
And the transaction is unnecessary (which is part of the problem).

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-08 : 13:47:57

You're right. The default isolation level is read committed. If you set "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", that should lock the table correctly. The transaction block is to ensure in event of server/connection problems the @id value is correct?

Edited by - Lou on 02/08/2002 13:50:21

Edited by - Lou on 02/08/2002 14:11:51

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-08 : 14:35:56

I just double checked BOL. I knew there was a reason I like to explicitly declare transaction blocks. BOL says stored procedures defaults to implicit transactions off. I guess you can use explicit transaction blocks or turn implicit transactions on. I like explicit for the sake of clarity.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources