Help: Query

Hi,
I have 2 tables like below
CREATE TABLE Location
(
LocationID int NOT NULL,
LocationName char (30) NOT NULL UNIQUE,
CONSTRAINT PK_Location PRIMARY KEY (LocationID)
)
CREATE TABLE Parts
(
PartID int NOT NULL,
LocationID int NOT NULL,
PartName char (30) NOT NULL,
CONSTRAINT PK_Parts PRIMARY KEY (PartID),
CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
REFERENCES Location (LocationID)
)
I have created a stored procedure to delete all of partid on a certain
location as shown below.
CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
BEGIN
DECLARE @.PartID int
DECLARE crs_Parts CURSOR FOR
SELECT p.PartID
FROM Parts AS p INNER JOIN Location AS 1
ON p.LocationID = @.LocName
WHERE l.LocationName = @.LocName
OPEN crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
DELETE Parts WHERE CURRENT OF crs_Parts
FETCH NEXT FROM crs_Parts INTO @.PartID
END
CLOSE crs_Parts
DEALLOCATE crs_Parts
DELETE Location WHERE LocationName = @.LocName
END
My Question is how to replace the cursor operation in the stored
procedure by using single DELETE statement?
Thanks
Robert LieHi Robert
Jusr the query this way,
DELETE Parts
FROM Parts INNER JOIN Location
ON ON Parts.LocationID = Location.LocationID
WHERE Location.LocationName = @.LocName
hope this is the one that you are looking for:
thanks and regards
Chandra
"Robert Lie" wrote:
> Hi,
> I have 2 tables like below
> CREATE TABLE Location
> (
> LocationID int NOT NULL,
> LocationName char (30) NOT NULL UNIQUE,
> CONSTRAINT PK_Location PRIMARY KEY (LocationID)
> )
>
> CREATE TABLE Parts
> (
> PartID int NOT NULL,
> LocationID int NOT NULL,
> PartName char (30) NOT NULL,
> CONSTRAINT PK_Parts PRIMARY KEY (PartID),
> CONSTRAINT FK_PartsLocation FOREIGN KEY (Location ID)
> REFERENCES Location (LocationID)
> )
>
> I have created a stored procedure to delete all of partid on a certain
> location as shown below.
> CREATE PROCEDURE sp_DeleteLocation @.LocName char(30) AS
> BEGIN
> DECLARE @.PartID int
> DECLARE crs_Parts CURSOR FOR
> SELECT p.PartID
> FROM Parts AS p INNER JOIN Location AS 1
> ON p.LocationID = @.LocName
> WHERE l.LocationName = @.LocName
> OPEN crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> DELETE Parts WHERE CURRENT OF crs_Parts
> FETCH NEXT FROM crs_Parts INTO @.PartID
> END
> CLOSE crs_Parts
> DEALLOCATE crs_Parts
> DELETE Location WHERE LocationName = @.LocName
> END
> My Question is how to replace the cursor operation in the stored
> procedure by using single DELETE statement?
> Thanks
> Robert Lie
>

Help: Prob handling SQL error in stored proc

Hi,

I a stored procedure that inserts a record into a table as
below.

The insert works OK, but if the insert violates a unique
indewx constraint on one of the columns, the proc terminates
immediately, and does NOT execute the 'if @.@.ERROR <> 0'
statement.

Am I doing something wrong, or do I need to set an attribute
somewhere?

tia,
Bill

begin tran

insert into Users
(UserName, UserPWD, Lname, Fname, UserDesc)
values (@.userName, @.userPWD, @.lname, @.fname, @.userDesc)

if @.@.ERROR <> 0
begin
rollback tran
set @.returnCode = -2
set @.errMsg = 'SQL error '
+ convert(varchar(6), @.@.ERROR)
+ ' occurred adding user '
+ @.userName
end[posted and mailed, please reply in news]

Bill S. (bill_sheets@.hotmail.com) writes:
> I a stored procedure that inserts a record into a table as
> below.
> The insert works OK, but if the insert violates a unique
> indewx constraint on one of the columns, the proc terminates
> immediately, and does NOT execute the 'if @.@.ERROR <> 0'
> statement.
> Am I doing something wrong, or do I need to set an attribute
> somewhere?
>
> begin tran
> insert into Users
> (UserName, UserPWD, Lname, Fname, UserDesc)
> values (@.userName, @.userPWD, @.lname, @.fname, @.userDesc)
> if @.@.ERROR <> 0
> begin
> rollback tran
> set @.returnCode = -2
> set @.errMsg = 'SQL error '
> + convert(varchar(6), @.@.ERROR)
> + ' occurred adding user '
> + @.userName
> end

First, @.@.error is set after each statement, so @.errMsg will never read
anything but "SQL Error 0 ...". Always save @.@.error in a local variable
before you do anything else with it.

So over to your question. Error handling in SQL Server is a messy topic,
and there are errors you cannot trap like this, because SQL Server
aborts the batch immediately. However, constraint violation as you
mention is not among those - unless the setting SET XACT_ABORT is ON.

So, assuming you are not using XACT_ABORT ON, the error handler should
be executed. But how do you know that it is not? What are @.returnCode
and @.errMsg? Local variables? Output parameters? If they are output
parameters, and run the procedure from Query Analyzer:

DECLARE @.ret int, @.errMsg varchar(200)
EXEC your_sp @.userName, ..., @.ret OUTPUT, @.errMsg OUTPUT
SELECT @.ret, @.errMsg

My guess is that you are running the procedure from some client library
which traps the error, before you get to read the output parameters.

As I mentioned, error handling is really a messy topic, but I have
an article on error handling of my web site that may be of interest,
http://www.sommarskog.se/error-handling-II.html. I don't know exactly
on what level you are on; if you are fairly unexperienced with SQL
Server, you may feel overwhelmed, but you could browse it now, and
save it for later reading.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp