A simple delete stored proc not working..Why??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

A simple delete stored proc not working..Why???

Author

Topic

iamguyster
Starting Member

4 Posts

Posted - 2007-08-07 : 18:11:31

Hi,

I have writtena very simple stored proc as follows:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spPurgeTrainee4] 
	@PersonNumber char(5)
AS
BEGIN
	SET NOCOUNT ON;
        DELETE FROM [QTS].[dbo].[tblTrainee]
        WHERE[PersonNumber] = @PersonNumber
END

Dead simple yeah? I have almost identical SPs that do the same type of thing and they work a treat. When I run this one it does nothing..absolutely nowt! If I hard code a PersonNumber into the SP, it works, but when using the parameter it doesn't. I have been banging my head over this for hours and can't seem to find what the problem is. I suspect it is something so simple that I'll be sick when I find out. Can anyone suggest anything? It always helps to have a fresh look and I reckon it's a case of me not seeing the wood for ther trees on this.

many thanks for any help,
Guy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 18:15:22

DROP/CREATE the PROC to see if that resolves the issue.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

iamguyster
Starting Member

4 Posts

Posted - 2007-08-07 : 18:23:03

Nope...it didn't work.
I had tried that before and had no luck. It's confusing me to no end!!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-07 : 18:44:11

How many records do you get if you run this query in query analyzer:


Declare @PersonNumber char(5)
Set @PersonNumber = '' -- <-- Set a value here
Select Count(*)
FROM [QTS].[dbo].[tblTrainee]
WHERE[PersonNumber] = @PersonNumber

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

iamguyster
Starting Member

4 Posts

Posted - 2007-08-07 : 18:46:41

Aha!

Know if I'd have sat here just looking at it I would never have spotted my mistake, but because I as thinking of ways to explain it and was looking at smaple data to show you I spotted it...

I'm a numpty!!

The parameter was set to

PersonNumber char(5)

it should have been

Person Number char(6)

No-one here would have spotted that...tho you may have suggested I check the param is defined correctly, but just the acty of asking for help helped me see my mistake...so thanks for being here for me to ask :)

Now I can go to bed..it's not healthy to be working on a DB at midnight!

iamguyster
Starting Member

4 Posts

Posted - 2007-08-07 : 18:48:14

quote:
Originally posted by dinakar

How many records do you get if you run this query in query analyzer:

Declare @PersonNumber char(5)
Set @PersonNumber = '' -- <-- Set a value here
Select Count(*)
FROM [QTS].[dbo].[tblTrainee]
WHERE[PersonNumber] = @PersonNumber

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

to be fair...if I hadn't have spotted my error before, this would have made me see where things were wrong. So thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources