Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple proc question 2

Status
Not open for further replies.

commatom

Programmer
Aug 30, 2007
54
US
Ok, Oracle guy here looking for some help.

I have a proc that I wrote to clean up some data.

Code:
ALTER PROC maint_rows

@id varchar

AS

BEGIN

BEGIN TRAN
delete maint_table where entityid = @id

COMMIT TRAN

END

This compiles and runs but after it completes, the record is still there??? Anyone know what I am missing?
 
My guess is that your @id variable has the incorrect value. You haven't given it a size, so by default it will be up to 1 character long.

Try giving it a size:
@id varchar(50)

Also, you don't need to declare a transaction for a single delete command.
 
Thanks but that wasn't it.

I also tried remove the declaration of the transaction and that didnt help either.

 
Could you show us how you are executing this stored proc. along with the example of the data you are trying to delete?
 
I have tried to run it 3 ways.

1. maint_rows 'C1264'

2. execute maint_rows 'C1264'

3.
Declare @RC int
Declare @id varchar(50)
SELECT 2id = '''C1264'''
EXEC 2rc = [proddb].[dbo].[maint_rows] @id
DECLARE @prnline nvarchar(4000)
print 'Stored Procedure: proddb.dbo.maint_rows'
select @prnline = ' return code = '+ convert(nvarchar, @Rc)
print 2prnline


All 3 had the same result
 
See if this works

Code:
create proc [proddb].[dbo].[maint_rows] 
 @id varchar(50)
AS
BEGIN
delete maint_tabl where entityid = @id
END
 
Check to see if there is a trigger on the table. TRiggers can prevent data from being deleted.

sp_helptrigger 'maint_table'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
So it looks like it was the declaration of variable that was causing me an issue.

When it was suggested that I put in a length, I had a space in between the variable and the length.

When I removed the space. Everything worked.

Thanks for all the help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top