September 28, 2010 at 9:28 am
I have a storedproc
ALTER PROCEDURE [dbo].[ABCD]
AS
BEGIN
UPDATE dbo.XXX
SET Has_Errors = 1
WHERE ( LEN(Col1) > 10 OR LEN(Col1) < 10)
END
If I run the update statement alone i.e just execute
UPDATE dbo.XXX
SET Has_Errors = 1
WHERE ( LEN(Col1) > 10 OR LEN(Col1) < 10)
then it works fine . But doesn't have any effect from the stored prc.
What am I missing ?
September 28, 2010 at 9:54 am
The statements appear identical, so maybe you're overlooking the obvious...?
Are you running this against the right database? (Maybe the table dbo.XXX exists in both so it's not throwing an error?)
Are you sure you're invoking the stored proc correctly?
Rob Schripsema
Propack, Inc.
September 28, 2010 at 10:13 am
Alter this to match your object names, then run it.
If you are experiencing the same problem then it's probably a SP settings issue, check datatype of Col1.
UPDATE dbo.XXX
SET Has_Errors = 0
WHERE LEN(Col1) <> 10
GO
SELECT COUNT(*) FROM dbo.XXX WHERE Has_Errors = 1 -- record the count
GO
ALTER PROCEDURE [dbo].[ABCD]
AS
BEGIN
UPDATE dbo.XXX
SET Has_Errors = 1
WHERE LEN(Col1) <> 10
END
GO
EXEC [dbo].[ABCD]
GO
SELECT COUNT(*) FROM dbo.XXX WHERE Has_Errors = 1 -- record the count
GO
UPDATE dbo.XXX
SET Has_Errors = 0
WHERE LEN(Col1) <> 10
GO
UPDATE dbo.XXX
SET Has_Errors = 1
WHERE LEN(Col1) <> 10
GO
SELECT COUNT(*) FROM dbo.XXX WHERE Has_Errors = 1 -- record the count
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 28, 2010 at 10:17 am
Yes, I am sure that I am using the right database .
September 28, 2010 at 10:22 am
Thanks .
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply