the above statement doesn;t stop the database table identity increment. because its a parameter, anyway you cannot insert anything into identity column normally except when used insert_identity property.
I think its not possible to stop the identity property.
still you wants to stop the identity increment, then follow these steps.
1. remove the identity property of that column in that table.
2. when inserting into that table, don't insert any think into that column, leave it as null.
2. write a trigger for the insert statement on that table,
in that trigger, write the logic such that,
i) take the maximum id of that table,
ii)if that id is greater than your maximum limit, don;t update it or place your intended value
ii) if that id is lesser than your maximum limit, then increment 1 to that id, and update that row of that table.
declare @primarycolumnname Varchar(50)
declare @tableid int
set @primarycolumnname = (select primarycolumnname from inserted)
set @tableid = (select max(tableid) from tablename)
if(@tableid >= 10000) -- suppose you wants to stop , if it is graterthan 10000
begin
-- here you can write your logic, if you wants do palce any value as tableid, if it cross the limit,
end
else
begin
update tablename set tableid = @tableid + 1 where primarycolumnname = @primarycolumnname
end
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
The Size argument is ignored for fixed length data types like Int. It is only relevant for variable length data types such as varchar, char, varbinary etc..
Keep it Simp...
Member
546 Points
261 Posts
SqlDbType.Int, 4 - Does specifying the parameter datatype size eventually stop auto increment in ...
Jun 17, 2008 05:57 AM|LINK
Hi, I'm specify the size of the interger dataypes in my code behind files; e.g.
commad.Parameters.Add("@SomeID", SqlDbType.Int, 4)
I know it may seem silly, but I have to ask: [:S]
Will specifying ths size (i.e. 4) eventually stop the auto-icnrement of the underlying primary key SomeID in the SQL Server 2005 database?
What restriction, if any, does this place if the next SomeID value is 10000, which is 5 digits? or 999999, which is 6 digits?
Thank you
ramireddyind...
All-Star
31358 Points
4579 Posts
Re: SqlDbType.Int, 4 - Does specifying the parameter datatype size eventually stop auto increment...
Jun 17, 2008 08:00 AM|LINK
the above statement doesn;t stop the database table identity increment. because its a parameter, anyway you cannot insert anything into identity column normally except when used insert_identity property.
I think its not possible to stop the identity property.
still you wants to stop the identity increment, then follow these steps.
1. remove the identity property of that column in that table.
2. when inserting into that table, don't insert any think into that column, leave it as null.
2. write a trigger for the insert statement on that table,
in that trigger, write the logic such that,
i) take the maximum id of that table,
ii)if that id is greater than your maximum limit, don;t update it or place your intended value
ii) if that id is lesser than your maximum limit, then increment 1 to that id, and update that row of that table.
declare @primarycolumnname Varchar(50) declare @tableid int set @primarycolumnname = (select primarycolumnname from inserted) set @tableid = (select max(tableid) from tablename) if(@tableid >= 10000) -- suppose you wants to stop , if it is graterthan 10000 begin -- here you can write your logic, if you wants do palce any value as tableid, if it cross the limit, end else begin update tablename set tableid = @tableid + 1 where primarycolumnname = @primarycolumnname endbrucevde
Participant
1568 Points
233 Posts
Re: SqlDbType.Int, 4 - Does specifying the parameter datatype size eventually stop auto increment...
Jun 17, 2008 06:07 PM|LINK
The Size argument is ignored for fixed length data types like Int. It is only relevant for variable length data types such as varchar, char, varbinary etc..