DECLARE Variable and using it.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a stored procedure that deletes the contents of my table
[tblTableName]. In this table I have a variable named [sysDoNotDelete] that
if set to TRUE then the record is not to be deleted. The code below deletes
all records, except thouse records indicated. The code below is not 100%
correct because I cannot create the stored procedure as I need it. The intent
is to show what I'm trying to accomplish.

In this example, records 1 to 4 are marked as [sysDoNotDelete] = TRUE, and
thus will not be deleted. Once the other records have been deleted I want to
reseed the table, so the next RecordID will be 5.

Here is my code (will not compile because its not valid, but I don't
understand what's wrong).

DECLARE @intTableCount int
DELETE tblTableName WHERE [sysDoNotDelete] = FALSE
SET @intTableCount = SELECT COUNT(*)
FROM tblTableName.RecordID;
DBCC CHECKIDENT ('tblTableName',RESEED, @intTableCount);

I'm assuming the SET statement is what's wrong, because if I manually set it
to "SET @intTableCount = 5" the procedure runs fine. But, I need the value of
@intTableCount to equal the number of records in the table.

Also, I want to verify what the maximum RecordID already is, to make sure
its not greater than the number of records in the table. For example, if the
table had 100 records and 30 were deleted, but the maximum RecordID = 81,
thus the count of the table would result in a reseed that is too low.
Somehow, I need to be able to evaluate for this condition so that I reseed
the table properly.

Can anyone help me with getting this Stored Procedure to achive the results
I'm looking for.

Thanksin advance.
 
The correct syntax is:
SET @intTableCount = (SELECT COUNT(*) FROM tblTableName.RecordID)

or:
Select @intTableCount = COUNT(*) FROM tblTableName.RecordID


Reseeding your table, why would you want to do that? In a multi-user
environment, your code is calling for trouble.
 
In addition to the problems Sylvain pointed out, your Delete statement is
also wrong (very common mistake for those coming from Access). Assuming
sysDoNotDelete is a bit field, it should read:

DELETE tblTableName WHERE [sysDoNotDelete] = 0



Rob
 
Shouldn't the DELETE... ststement be (note: the keyword: FROM):

DELETE FROM tblTableName WHERE...

Robert Morley said:
In addition to the problems Sylvain pointed out, your Delete statement is
also wrong (very common mistake for those coming from Access). Assuming
sysDoNotDelete is a bit field, it should read:

DELETE tblTableName WHERE [sysDoNotDelete] = 0



Rob

Greg said:
I have a stored procedure that deletes the contents of my table
[tblTableName]. In this table I have a variable named [sysDoNotDelete]
that
if set to TRUE then the record is not to be deleted. The code below
deletes
all records, except thouse records indicated. The code below is not 100%
correct because I cannot create the stored procedure as I need it. The
intent
is to show what I'm trying to accomplish.

In this example, records 1 to 4 are marked as [sysDoNotDelete] = TRUE,
and
thus will not be deleted. Once the other records have been deleted I want
to
reseed the table, so the next RecordID will be 5.

Here is my code (will not compile because its not valid, but I don't
understand what's wrong).

DECLARE @intTableCount int
DELETE tblTableName WHERE [sysDoNotDelete] = FALSE
SET @intTableCount = SELECT COUNT(*)
FROM tblTableName.RecordID;
DBCC CHECKIDENT ('tblTableName',RESEED, @intTableCount);

I'm assuming the SET statement is what's wrong, because if I manually set
it
to "SET @intTableCount = 5" the procedure runs fine. But, I need the
value of
@intTableCount to equal the number of records in the table.

Also, I want to verify what the maximum RecordID already is, to make sure
its not greater than the number of records in the table. For example, if
the
table had 100 records and 30 were deleted, but the maximum RecordID = 81,
thus the count of the table would result in a reseed that is too low.
Somehow, I need to be able to evaluate for this condition so that I
reseed
the table properly.

Can anyone help me with getting this Stored Procedure to achive the
results
I'm looking for.

Thanksin advance.
Click to expand...
Click to expand...
 
Yeah, you're right. I hadn't noticed that and just cut & pasted his
statement (except for the FALSE).


Rob

Norman Yuan said:
Shouldn't the DELETE... ststement be (note: the keyword: FROM):

DELETE FROM tblTableName WHERE...

Robert Morley said:
In addition to the problems Sylvain pointed out, your Delete statement is
also wrong (very common mistake for those coming from Access). Assuming
sysDoNotDelete is a bit field, it should read:

DELETE tblTableName WHERE [sysDoNotDelete] = 0



Rob

Greg said:
I have a stored procedure that deletes the contents of my table
[tblTableName]. In this table I have a variable named [sysDoNotDelete]
that
if set to TRUE then the record is not to be deleted. The code below
deletes
all records, except thouse records indicated. The code below is not 100%
correct because I cannot create the stored procedure as I need it. The
intent
is to show what I'm trying to accomplish.

In this example, records 1 to 4 are marked as [sysDoNotDelete] = TRUE,
and
thus will not be deleted. Once the other records have been deleted I
want to
reseed the table, so the next RecordID will be 5.

Here is my code (will not compile because its not valid, but I don't
understand what's wrong).

DECLARE @intTableCount int
DELETE tblTableName WHERE [sysDoNotDelete] = FALSE
SET @intTableCount = SELECT COUNT(*)
FROM tblTableName.RecordID;
DBCC CHECKIDENT ('tblTableName',RESEED, @intTableCount);

I'm assuming the SET statement is what's wrong, because if I manually
set it
to "SET @intTableCount = 5" the procedure runs fine. But, I need the
value of
@intTableCount to equal the number of records in the table.

Also, I want to verify what the maximum RecordID already is, to make
sure
its not greater than the number of records in the table. For example, if
the
table had 100 records and 30 were deleted, but the maximum RecordID =
81,
thus the count of the table would result in a reseed that is too low.
Somehow, I need to be able to evaluate for this condition so that I
reseed
the table properly.

Can anyone help me with getting this Stored Procedure to achive the
results
I'm looking for.

Thanksin advance.
Click to expand...
Click to expand...
Click to expand...
 
Regarding the FALSE indicator, I should note it is ZERO instead becase
sysDoNotDelete is a BIT field. I've found much of my ACCESS code has had to
be updated since it does not recognize FALSE. That really disappoints me.

The DELETE statements and RESEED statements are NOT being run against a
production database. I agree that that would not be a good idea. I am only
running these against a database prior to shipment to our customers. I have
no doubt there are better approaches than this. These DELETE statements will
run once to remove test data used during development.

Now, my approach down the road is to build the SQL Server Database using SQL
Scripts and then populating specific tables with the records I need at that
time. I suspect this is the better approach, vs running DELETE statements.
Plus, I'm sure running SQL Scripts will ensure all the KEYS to every table
start at 1, plus will guarantee the database is shipped in the cleanest state
possible.

Robert Morley said:
Yeah, you're right. I hadn't noticed that and just cut & pasted his
statement (except for the FALSE).


Rob

Norman Yuan said:
Shouldn't the DELETE... ststement be (note: the keyword: FROM):

DELETE FROM tblTableName WHERE...

Robert Morley said:
In addition to the problems Sylvain pointed out, your Delete statement is
also wrong (very common mistake for those coming from Access). Assuming
sysDoNotDelete is a bit field, it should read:

DELETE tblTableName WHERE [sysDoNotDelete] = 0



Rob

I have a stored procedure that deletes the contents of my table
[tblTableName]. In this table I have a variable named [sysDoNotDelete]
that
if set to TRUE then the record is not to be deleted. The code below
deletes
all records, except thouse records indicated. The code below is not 100%
correct because I cannot create the stored procedure as I need it. The
intent
is to show what I'm trying to accomplish.

In this example, records 1 to 4 are marked as [sysDoNotDelete] = TRUE,
and
thus will not be deleted. Once the other records have been deleted I
want to
reseed the table, so the next RecordID will be 5.

Here is my code (will not compile because its not valid, but I don't
understand what's wrong).

DECLARE @intTableCount int
DELETE tblTableName WHERE [sysDoNotDelete] = FALSE
SET @intTableCount = SELECT COUNT(*)
FROM tblTableName.RecordID;
DBCC CHECKIDENT ('tblTableName',RESEED, @intTableCount);

I'm assuming the SET statement is what's wrong, because if I manually
set it
to "SET @intTableCount = 5" the procedure runs fine. But, I need the
value of
@intTableCount to equal the number of records in the table.

Also, I want to verify what the maximum RecordID already is, to make
sure
its not greater than the number of records in the table. For example, if
the
table had 100 records and 30 were deleted, but the maximum RecordID =
81,
thus the count of the table would result in a reseed that is too low.
Somehow, I need to be able to evaluate for this condition so that I
reseed
the table properly.

Can anyone help me with getting this Stored Procedure to achive the
results
I'm looking for.

Thanksin advance.
Click to expand...
Click to expand...
Click to expand...
 
Hello Robert,

I agree with you that creating a script and populating data to tables
during deployment is a better idea under this situation. Instead of using
insert statements to insert new records, you may also consider use bcp
utility or bulk insert statment to do the job. You could put the data you
want in data files and use script to put data into tables in a more
efficient manner. You could keep or not keep identity values when importing
data from files

Keeping Identity Values When Bulk Importing Data
http://msdn2.microsoft.com/en-us/library/ms186335.aspx

BULK INSERT
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
ba-bz_4fec.asp

If you'd like to further discuss this or you have any comments, please feel
free to let's know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Robert,

I think you meant "Hello Greg"; it's not me you're responding to. :)



Rob
 
Hi Robert & Greg,

Sorry for this . My fault. :-)

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Back
Top