Problem using the syntax "delete from"

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

Transact-SQL (2000)

Problem using the syntax "delete from"

Author

Topic

hemanth
Starting Member

1 Post

Posted - 2002-10-24 : 08:27:02

Hi all
Iam trying to run a stored procedure written in an script file as below

/****** Object: Stored Procedure dbo.CATEGORY_DELETE Script Date: 07/22/2002 1:36:45 PM ******/
CREATE PROCEDURE CATEGORY_DELETE
@resource_struct_id numeric(9),
@status_ok smallint=0 output
as
DECLARE @resource_id numeric
--FETCH THE RESOURCEID AND RESOURCETYPE

DECLARE CUR_RESOURCE CURSOR LOCAL FOR select resource_id from resource_manager where resource_struct_id=@resource_struct_id
OPEN CUR_RESOURCE
FETCH CUR_RESOURCE INTO @resource_id
WHILE(@@FETCH_STATUS=0)
BEGIN
--SELECT THE RESOURCEID AND TYPE FROM THE RESOURCEMANAGER TABLE

FETCH CUR_RESOURCE INTO @resource_id
END
CLOSE CUR_RESOURCE
DEALLOCATE CUR_RESOURCE
IF EXISTS(SELECT RESOURCE_STRUCT_ID FROM RESOURCE_STRUCT WHERE resource_struct_prev=@resource_struct_id)
BEGIN
SET @status_ok=0
END
ELSE
BEGIN
delete from resource_manager where resource_id=@resource_id
delete from resource_struct where resource_struct_id=@resource_struct_id
SET @status_ok=1

END

GO

The above stored procedure shows an syntax error at line numbers where i have used the "DELETE FROM" to delete the rows in a table.

Also i would like to know the impact/difference of using
"Delete from tablename where col_name='abc'
and
"Delete tablename where col_name='abc'

Do help me in this regard!!!
with regards
Hemanth

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-24 : 08:58:02

FROM BOL:

FROM

Is an optional keyword that can be used between the DELETE keyword and the target table_name, view_name, or rowset_function_limited.

Can you post the error your getting.
I don't think you need a cursor at all, can you post your table structure, some sample data and expected results and someone can help you transform this cursor solution to a set based solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources