Automating record selection parameters

Hi,

I need some advise on how to automate record selection on a stored
proc. Here is my situation. I have a stored proc that I used on
Crystal reports with two parameters - Acctcode and Subacct. When a
user enters ' *' on these parameter, it means to report on all
accounts otherwise, report only on specific account.

Here is my select statement with line numbers:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)

As

3 Select AcctCode, SubAcct
4 From GLDetails
5 Where SubAcct <> ' '
6 and AcctCode = @.Acctcode -- for specific acctcode
7 and SubAcct = @.SubAcct -- for specific subacct

8 Go

-- If a user wants to see all Acctcode, and all Subacct, how do I
disable lines 6 and 7?

Thank you in advance for your help.

EdgarHi Edgar,

How about:

Create proc rb_SubledgerRpt
@.Acctcode varchar(4), @.SubAcct varchar(3)
As
Select AcctCode, SubAcct
From GLDetails
Where SubAcct <> ' '
and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))
and ((@.SubAcct = '*' ) or (SubAcct = @.SubAcct))

-Dick Christoph
"Edgar" <edgarjtan@.yahoo.com> wrote in message
news:1141058639.691125.36720@.v46g2000cwv.googlegro ups.com...
> Hi,
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?
> Thank you in advance for your help.
> Edgar|||One way would be this:

Declare @.sQry nvarchar(500)
Declare @.sWhere nvarchar(500)
Set @.sWhere = ' '
set @.AcctCode = 'ABC'
Set @.sQry =
'Select AcctCode, SubAcct ' +
'From GLDetails ' +
'Where SubAcct <> '' '''

If @.AcctCode <> '*'
Set @.sWhere = @.sWhere + 'and AcctCode = ''' + @.Acctcode + ''''

If @.SubAcct <> '*'
Set @.sWhere = @.sWhere + 'and SubAcct = ''' + @.SubAcct + ''''

Set @.sQry = @.sQry + @.sWhere
EXEC sp_executesql @.sQry|||Edgar (edgarjtan@.yahoo.com) writes:
> I need some advise on how to automate record selection on a stored
> proc. Here is my situation. I have a stored proc that I used on
> Crystal reports with two parameters - Acctcode and Subacct. When a
> user enters ' *' on these parameter, it means to report on all
> accounts otherwise, report only on specific account.
> Here is my select statement with line numbers:
> Create proc rb_SubledgerRpt
> @.Acctcode varchar(4), @.SubAcct varchar(3)
> As
> 3 Select AcctCode, SubAcct
> 4 From GLDetails
> 5 Where SubAcct <> ' '
> 6 and AcctCode = @.Acctcode -- for specific acctcode
> 7 and SubAcct = @.SubAcct -- for specific subacct
> 8 Go
> -- If a user wants to see all Acctcode, and all Subacct, how do I
> disable lines 6 and 7?

Dick and Jennifer suggested two methods. Dick's method is cleaner, but
Jennifer's solution can give better performance. If there are no
indexes on AcctCode or SubAcct (or the table is small), then there is
on performance issue. But if there are indexes, it is not likely that
Dick's solution will make use of them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hmmmm.

I guess I don't understand your comments Mr. Sommarskog. Dick's
solution to me is better. It is simpler, and doesn't use dynamic SQL.

Further, I've had the best success with GL's using a composite index on
Account+Subaccount.
If you don't know the account, you will end up doing a table scan
pretty much no matter what. If you know the account and the subaccount,
retrievals can be VERY fast.

Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
can key in on the best way to do the search relatively easily.

I look forward to hearing why you don't think the optimizer would
indexes. They have in my experience.

Thank you,
Doug|||Doug (drmiller100@.hotmail.com) writes:
> I guess I don't understand your comments Mr. Sommarskog. Dick's
> solution to me is better. It is simpler, and doesn't use dynamic SQL.
> Further, I've had the best success with GL's using a composite index on
> Account+Subaccount.
> If you don't know the account, you will end up doing a table scan
> pretty much no matter what. If you know the account and the subaccount,
> retrievals can be VERY fast.

I will have admit that I did not consider the particular business problem,
but more considered the general case. Let's say that you have something
like:

SELECT ...
FROM tbl
WHERE (indexedcol1 = @.vall OR @.val1 = '*')
AND (indexedcol2 = @.val2 OR @.val2 = '*')
AND (nonindexedcol = @.val3 OR @.val3 = '*')

This will most certainly table scan, even if @.val1 has a distinct value and
@.val3 is '*', because when the optimizer builds the plan it has no
knowledge what value the parameter will have. It can sniff parameter
values, but it cannot build plans that produce wrong results. So it must
be a table scan. (The exception in SQL 2005 is when you add the query hint
OPTION (RECOMPILE).)

> Finally, because Dick's solution doesn't use dynamic SQL, the optimizer
> can key in on the best way to do the search relatively easily.

If you build a dynamic SQL string and include only the the search
parameters that were actually given, you will give the optimizer the
exact right amount of information to work with. If you only supply
@.val1, the optimizer will use that index (if it is selective enough).
If you only supply @.val2, it will use thar index, and if you supply
only @.val3, the query will table scan.

For a longer discsussion on the topic, see my article
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,

Thanks for your comments.

An interesting subtlty is involved here. I wonder if the difference is
significant.
Dick's code had and ((@.AcctCode = '*' ) or (AcctCode = @.Acctcode))

your code had
(indexedcol1 = @.vall OR @.val1 = '*')

this sounds weird, but I can easily see where your code would require a
table scan, yet Dick's code could be done without one.
Does this make any sense to anyone else?
I don't know how to explain my logic.

automating a query

Need help again.

I've a query that searches for a specific record. I need to make this query available for some collegues at work who don't understand SQL, so I wouldn't like to have them editing the query to insert the new number for every search they have to do.

Is there something like a popup text box that calls for the new number and then substitutes it in the query sintaxe?

this would be very helpfull. Thanks

Unfortuantely, there is not.

And if your collegues could endanger your query, think about what they could do to your database...

Put the query in a stored procedure, and show them how to execute the stored procedure.

automaticly create a record's field

I used a field as the record's number,how can I get a automaticly created
number field (it can inrease automaticly) when I insert a record into a
table?Refer to the IDENTITY property in BOL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"authorking" <authorking2002@.hotmail.com> wrote in message
news:uKU01w2CFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I used a field as the record's number,how can I get a automaticly created
>number field (it can inrease automaticly) when I insert a record into a
>table?
>|||First, let's clarify some concepts. In SQL, rows are identified by a Key not
by a "record number". In fact the concept of a record number is quite alien
to the relational database model. The Key is part of your data - it is some
subset of the attributes that uniquely identify a row.
What you are asking for is called a *surrogate* or *artificial* key. SQL
Server provides the IDENTITY feature as a mechanism for an artifically
generated, surrogate key so take a look at IDENTITY in Books Online.
IDENTITY is not a substitute for the natural key of your table. It is just a
surrogate for that key and may be used in foreign key references. Many times
you won't need IDENTITY at all. If you aren't familiar with some of these
key concepts then look them up in a book on relational database
fundamentals.
Hope this helps.
David Portas
SQL Server MVP
--

automatically update another field based on other parts of the record

Sql is not a strong point with me so I'm just going to throw this out there. I have a stored procedure that updates the quantity in my 'CartItems' table. Is there a way to have something else happen within the stored procedure that will update another field based on other parts of the record? There is a 'lineTotal' field that I need to equal the 'pounds * itemSell' fields which are both fields within this record.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS
UPDATE CartItems Set quantity = @.newQuantity
WHERE cartItemID = @.cartItemID
GO

sure. you can update as many fields as you want within the UPDATE statement.

CREATE PROCEDURE UpdateCartItem
(
@.cartItemID Int,
@.newQuantity numeric(9)
)
AS

SET NOCOUNT ON

UPDATE
CartItems
Set
quantity = @.newQuantity
,lineTotal =pounds * itemSell
WHERE
cartItemID = @.cartItemID

SET NOCOUNT OFF

GO

|||Or make the lineTotal a computed column. Just modify the table and tell it the lineTotal column is equal to pounds*itemSell.sql

automatically send a notice after 6 months (was "Date Question")

Hello,

I'm trying to write a query that will do the following... I'll give a quick background:

When a machine is installed, I record an installation date. We would like to automatically send a notice to our customers 6 months past this install date regarding scheduled maintenance.

Right now all I have is the notice, which is a report in Access with a query attached that pulls all machine sales that have install dates.

I'm not sure how to reference this situation... or how to start addressing the problem.

Basically:

Customer(CustomerID, CustomerName, CustomerEmail)
MachineDetail(DetailID, CustomerID, ModelID)
MachineModel(ModelID,ModelName)
InstallInfo(InstallID, DetailID, InstallComplete)

Please help :( Thanks,
CherishTry looking here (http://www.1keydata.com/sql/sql.html) or here (http://www.geocities.com/SiliconValley/Vista/2207/sql1.html) or here (http://www.w3schools.com/sql/default.asp).

;)|||use the DATEADD function
select ...
from ...
where InstallComplete
between dateadd("m",-6,date())
and dateadd("m",-5,date()) this will pull all installs between 5 and 6 months old

that way you can prepare the notices manually

sorry, no idea how to set up access to do this automatically

perhaps ask in the access forum|||Create a stored procedure starting with the code posted by r937, that selects the customers you want to send a notice to and either send an e-mail or write the notices to a file.

Create a batch script to execute this procedure ONCE per month, depending on your OS use:
1) Dos cmd and Win2K(XP) task scheduler or 2) Unix KSH and crontab or 3) Oracle jobs.

There have been many posts in this and other forums on how to send e-mail from either Oracle or MS SQL.
:rolleyes:

PS: Ooops, just noticed you mentioned Access...
I beleive you can e-mail the notices by having Access 'write' to Outlook.
Good Luck.|||LKBrwn_DBA, Access doesn't support stored procs|||LKBrwn_DBA, Access doesn't support stored procs
Yeah, I just noticed it was Access, but he can create a VB 'Procedure' or something like that. :(|||PS: Check these links:
MS Email FAQ (http://www.granite.ab.ca/access/email.htm) and MS ACCESS VB script (http://www.febooti.com/products/command-line-email/online-help/send-email-ms-access.html)
;)|||She, and thanks for all your help, I'll try out the suggestions today :)

Automatic updating of datetime field

I need to automatically update a datetime field for a record to the current time whenever the record is updated.

create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go

One option would be to use an instead of update trigger.

create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go

update t set value='dick' where id=1 and name='fred'
go

Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.

Any ideas?...also, you don't know what database server it is going to be running on, so it has to be platform-independent.

...and it needs to be fully compatible with the Mayan calendar as well.

...oh yeah, and the final code must be a palindrome that reads the same way forwards as backwards! Yeah, that's it! What a kick-ass application design! Whooooo-eeeeeeee!sql

Automatic record counter in MS-SQL

Is there any kind of automatic record counter in MS SQL 2005 that can be
used to reference a record one is updating?
Many thanksAndrew Chalk wrote:
> Is there any kind of automatic record counter in MS SQL 2005 that can
> be used to reference a record one is updating?
> Many thanks
Assuming you don't have a PK defined yet and want to use an
auto-generating number, you can add an INT/BIGINT NOT NULL column to the
table and assign it the IDENTITY property. SQL Server will automatically
assign a unique number to each row inserted into the table. Have a look
at the SCOPE_IDENTITY() function if you want to learn how to retrieve
the last generated identity value. See IDENTITY in BOL for more
information.
David Gugick - SQL Server MVP
Quest Software|||Andrew Chalk wrote:
> Is there any kind of automatic record counter in MS SQL 2005 that can be
> used to reference a record one is updating?
> Many thanks
Rows are referenced by KEYS not by "record counters". Rephrase your
question and explain what you are trying to achieve.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I think you just made CELKO , very .
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:eOIhetdKGHA.720@.TK2MSFTNGP14.phx.gbl...
> Is there any kind of automatic record counter in MS SQL 2005 that can be
> used to reference a record one is updating?
> Many thanks
>sql

automatic printing from trigger

Hi,
I am new to Sql Server 2000. I am looking for help on how to automatically generate a report to a network printer after a record is added/updated/changed in a table. How should I do this? With a Trigger? With DTS?
Please help.With a Trigger ...|||Thanks for the info. I think I was unclear in my initial question and would like to know what are the exact steps needed to perform this function? I don't know how to create a trigger to do this and would be looking for an example of some sort or instructions for the code.

Any help greatly appreciated.|||What about sp_OA... procedures, write component in any language
and use these SP for communication.

Export with DTS or SQLDMO objects.

Multitask temp tables with :
1.X=OBJECT_ID('temp..#temptbl')
2.Send X to object
3.Get temp name
use tempdb
GO
Y=OBJECT_NAME(X)

Too much work to be done ...
MSSQLSERVER2K has native support for e-mail and pager.

Automated Data Export

Here's my process:
1. I have a form where someone enters certain criteria for some data…
2. The form than populates a record in a table with all the criteria…
3. I create a query based on the selected criteria for exporting…

How can I automate the process (maybe using DTS) where SQL server will automatically export the data for me using a stor proc? Create a file, populate the file etc…. or will I need to create an external App to do this?Use a scheduled job.|||A schedule job won't do the entire process. But here's the solution if anyone's interested:

Use a combination of SQL Server agent and the BCP Utilities in SQL Server. The bcp utility copies data between an instance of Microsoft® SQL Server? 2000 and a data file in a user-specified format.

For example
Copying Data From a Query to a Data File:

bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa –Ppassword

For more info check out the Transact-SQL Help section in SQL Server(under bcp utility)

Autoincrement record position in a view

Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?

For example let's say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?

recno persons
1person1
2person2
3person3

Please help me

I will be very grateful if you also reply your answers also and to my email

Email: stavrinc@.hotmail.com

Thank you

Christos StavrinouWith FoxPro you can use the RECNO() function:

SELECT RECNO() AS recno, persons FROM myTable

This is not possible with SQL Server, and I am relatively sure it is not possible with Access.

Terri|||I should have said that this is possible with SQL Server only by inserting your result set into a #TEMP table which has an Identity column.view post 368541

Terri

Auto-increment key id field help

I have a key field ID (int) for a table. How can I auto incremnt the key so that when I insert a new record I can get a new key. I should not have to know the next available number in my table. What settings do I use for the field.

Thanks in advance
MoonWa

Check out the IDENTITY property.

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.codecomments.com ***Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl...
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.codecomments.com ***

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.codecomments.com ***
Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl.. .
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.codecomments.com ***

AUTO UPDATE DATABASE

hello sir,
Please tell me how to update records in a table automatically in SQL.
Actually i want that if i delete a record from a table which has Serial
No. as primary key, then all other records should update automatically
means there Serial No. should be updated in sequence(1,2,3,4,5).
thanks
*** Sent via Developersdex http://www.developersdex.com ***Hi
I assume SerialNo is not an identity ...
create table e(j int ,i int)
insert e select 1, 3 union select 2, 7 union select 3,10 union select 4,20
select * from e
go
--delete the row
delete from e where j=2
--run this immeditaly
declare @.k int
set @.k=0
update e
set @.k=j=@.k+1
select * from e
go
drop table e
"ramji gupta" <gupta.rnd@.gmail.com> wrote in message
news:%23x%23m%2349tHHA.4948@.TK2MSFTNGP06.phx.gbl...
> hello sir,
> Please tell me how to update records in a table automatically in SQL.
> Actually i want that if i delete a record from a table which has Serial
> No. as primary key, then all other records should update automatically
> means there Serial No. should be updated in sequence(1,2,3,4,5).
> thanks
> *** Sent via Developersdex http://www.developersdex.com ***

Auto time stamp in Sql. Express

Is there a property setting in SqlEX. That automatically inserts the date and time in to a field (timestamp) in the dB, when a record is created. If so can someone please show me how this is done.

Thanks in advance

You can set up the default value for the column as getdate() in the design view of the table.

Auto numbers

How can I make my form to create an autonumber to my table when I insert a new record. Is there any field in the table similar to autonumber field in MSACCESS. Please help!You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks I am going to try it.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||Thanks you really help me. Now I find another problem. When I am in my main page I got links for administrator, clients, ect... this links are for entering data, delete and update. If I am in the clients area for example, if I view a record information and then go to anoter record the page does not displays, I got to go to my home page and then go to the page that I was looking and it displays the information. Hope you can help me whis this one.

Originally posted by Patrick Chua
You have to set your colum field as an "identity" field.

You can do it in Enterprise Manager.

At the Design Table form, select "int" or a numeric field, I guess. And set your identity to "Yes not for replication"

Identity Seed=1 (Identity seed is your starting number)
Identity Increment=1 ( is the incremental number , auto inserted everytime there is a new record).

If you want T-SQL command , read it under "identity" in BOL.|||well, you have to tell us how you wrote the code to your application,
else we will be firing blank gueses to your question.

Although I do that often :)

What programing language are u using? ASP vbscript? and how do you query your database ? via ADO ?

Things like this will help us help u.

Auto Number Data Type?

Is there a data type like Access's auto number that will automatically assign a record a number? I transfered a database from Access 2000 to SQL 2000 and it did not preserve the autonumber datatype.Identity is the SQL Server equivalent to the Access autonumber.|||When I open a table in design view and I change a datatype I do not see Identity in the list.|||Identity is property not a data type.
Chose a INT type for instance, then (in the bottom of the design screen - columns panel) specify if that's an identity column, the seed and increment eventually.

Originally posted by aaronshover
When I open a table in design view and I change a datatype I do not see Identity in the list.|||Thanks, that helped. I got it working now.

Auto insert a variable number of records?

I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?

Thanks in advance,

Sunny Guam

Can't you put all operations in a trigger on tblA? Put the SELECT command to tblC before INSERT to tblB so that you can dynamically build INSERT commands to tblB.|||

Thanks for the advice, but at the risk of sounding dumb; I'm not familiar with triggers and how to set them up within SQL 2005. Can you give me an example perhaps?

|||

Here is an example:http://forums.asp.net/thread/1281234.aspx

You can start from hereEnforcing Business Rules with Triggers

auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
fill
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
David Portas
SQL Server MVP
--

auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
--
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> > I have a table that has teh primary key set so it auto increments.
> >
> > How can I make it to where if I delete a record, the next entry will
fill
> > the spot that was vacated?
> >
> > For example;
> >
> > I have 5 records,
> > 1
> > 2
> > 3
> > 4
> > 5
> >
> > If I delete record 3 I will have
> >
> > 1
> > 2
> > 4
> > 5
> >
> > Then if I add another reocrd, I will have
> > 1
> > 2
> > 4
> > 5
> > 6
> >
> > I would like 6 to actually be 3 to fill in the space.
> > or, when I delete a record, have everythign shift locations, or at least
> > have teh last record fill the spot of the deleted one.
> >
> >
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
--
David Portas
SQL Server MVP
--