Simple proc question 2

[commatom]

Ok, Oracle guy here looking for some help.

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

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?

 

[RiverGuy]

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.

 

[commatom]

Thanks but that wasn't it.

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

 

[snitin78]

Could you show us how you are executing this stored proc. along with the example of the data you are trying to delete?

 

[commatom]

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

 

[snitin78]

See if this works

create proc [proddb].[dbo].[maint_rows] 
 @id varchar(50)
AS
BEGIN
delete maint_tabl where entityid = @id
END

 

[gmmastros]

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

 

[markros]

For your varchar problem check this link

SQL Server Stored Procedure with nvarchar parameter

 

[commatom]

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.