August 5, 2010 at 3:42 pm
I am trying to update a 2 columns on a table where the value in the column 'IS NULL'
Both columns (P1_ID and S1_ID) Data Type are INT.
I think missing something very obvious but can see it
Any help is appreciated!!!:w00t:
This my current procedure and the error I get
----
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE WCPDB_DST
GO
---Drop procedure if it exist
IF EXISTS(SELECT name FROM sysobjects where name='usp_WCPP_up_ID_3' AND type='P')
DROP PROC usp_WCPP_up_ID_3
GO
USE WCPDB_DST
GO
CREATE PROCEDURE usp_WCPP_up_ID_3--Updating WCPP for S1_ID/P1_ID
--Parameters
@P1_ID INT,
@S1_ID INT
AS
BEGIN
SELECT @P1_ID=P1_ID,@S1_ID=S1_ID
FROM dbo.tmp_WCPP;
IF @P1_ID IS NULL
BEGIN
UPDATEtmp_WCPP.P1_ID
SETP1_ID=0
END
SELECT @S1_ID=S1_ID
FROM dbo.tmp_WCPP;
IF @P1_ID IS NULL
BEGIN
UPDATEtmp_WCPP.S1_ID
SETS1_ID=0
END
END;
GO
--Error from server
--Msg 201, Level 16, State 4, Procedure usp_WCPP_up_ID_3, Line 0
--Procedure or function 'usp_WCPP_up_ID_3' expects parameter '@P1_ID', which was not supplied.
--
--(1 row(s) affected)
August 5, 2010 at 7:31 pm
The error is telling you that the stored procedure expects 2 parameters to be passed in to it when you call it and you aren't passing the parameters when you call the stored procedure. Your options are:
1. Remove the parameters and replace them with local variables in the body of the procedure
2. Get the parameter values from outside the procedure and pass them in when you call the procedure
3. Set default values for the parameters, then check to see if the parameters are at the default value (I'd use NULL) and if they are set them in the procedure as you are now.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 6, 2010 at 2:51 am
Hi
I'd go with Jack's first option - i.e to use local variables, since the first thing you do in the stored procedure is overwrite the parameter values anyway. So that would give you something like this (I haven't tested it):
CREATE PROCEDURE usp_WCPP_up_ID_3--Updating WCPP for S1_ID/P1_ID
AS
BEGIN
--Variables
DECLARE @P1_ID INT
DECLARE @S1_ID INT
SELECT @P1_ID=P1_ID,@S1_ID=S1_ID
FROM dbo.tmp_WCPP;
IF @P1_ID IS NULL
BEGIN
UPDATE tmp_WCPP.P1_ID
SET P1_ID=0
END
SELECT @S1_ID=S1_ID
FROM dbo.tmp_WCPP;
IF @P1_ID IS NULL
BEGIN
UPDATE tmp_WCPP.S1_ID
SET S1_ID=0
END
END;
GO
Duncan
August 6, 2010 at 1:14 pm
I think you may have a problem with both the 'select' and update statements. With no 'where' clause, the selects will return the whole table, and the variables will end up with the values of the last row only. The update with no 'where' clause will update every row in the table. Is that what you intended.
August 10, 2010 at 7:35 am
Thanks, I am trying to determinate the right syntax to execute the code but with out a good example I am trashing around.;-)
August 10, 2010 at 7:37 am
Thanks Duncan, I did try your code but did not manage the the nulls correctly, I will tinker with it further.;-)
August 10, 2010 at 7:41 am
Thanks Celko, I will work with your suggestions and let you know
August 10, 2010 at 9:32 pm
Declare a Local Variable in the Procedure.
No need to get Two Variables as parameter
August 11, 2010 at 1:17 am
neil.ordiers (8/10/2010)
Thanks Duncan, I did try your code but did not manage the the nulls correctly, I will tinker with it further.;-)
No problem - good luck. As others have suggested, do use your "tinkering" time 😉 to see if you can accomplish what you want to do in a completely different, more declaritive way. I don't know what it is you need to do, but I do reckon that a stored procedure might not be necessary.
Duncan
August 11, 2010 at 10:21 am
I try based on your script updating a single entry that has NULL using CASE to get a better understanding but I ran into another error. Here is the code and the message.
--
USE WCPDB_DST
GO
CREATE PROCEDURE usp_Cpay_Stop_ID_3
--Parameters
@Cpay_Stop DATETIME
AS
BEGIN
SELECT @Cpay_Stop = Cpay_Stop
FROM dbo.tbl_Cpay;
UPDATEdbo.tbl_Cpay.Cpay_Stop
SET @Cpay_Stop = CASE WHEN @Cpay_Stop IS NULL
THEN CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ELSE Cpay_Stop END;
END
--Msg 201, Level 16, State 4, Procedure usp_Cpay_Stop_ID_3, Line 0
--Procedure or function 'usp_Cpay_Stop_ID_3' expects parameter '@Cpay_Stop', which was not supplied.
??
Any help will be appreciate, since I would like to get a firm understanding of the errors and how to work with the language.:w00t:
August 11, 2010 at 10:37 am
You still have a problem with both the select and update statements.
This statement:
SELECT @Cpay_Stop = Cpay_Stop
FROM dbo.tbl_Cpay;
will select every row in the table because there is no 'where' clause. It will set @cpay_stop to each value of the cpay_stop column of those rows as it runs through them. The value of @cpay_stop will be the value of the last cpay_stop column returned by the server.
The update statement:
UPDATE dbo.tbl_Cpay.Cpay_Stop
SET @Cpay_Stop = CASE WHEN @Cpay_Stop IS NULL
THEN CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ELSE Cpay_Stop END;
has an incorrect form. What I think you want is
UPDATE dbo.tbl_Cpay
SET Cpay_Stop = CONVERT(DATETIME, '1900-01-01 00:00:00', 102) WHERE Cpay_Stop IS NULL;
This will update every row where cpay_stop is null to January 1 of 1900.
You need to take a look at the basic forms for 'select' and 'update' in Books Online or any introductory SQL book. Otherwise, you're going to end up updating all your data when you only mean to update specific rows.
END
August 11, 2010 at 10:47 am
Thanks David, I agree with you that the WHERE clause will right away update correctly the row. I am trying to use the CASE statement with out he Where Clause, since since I would add other CASE statement to have a very long formula be execute at the server level instead of the client side. I DO NEED to grasp the concept of using CASE either by another piece of code or with in the one I have.
August 11, 2010 at 1:25 pm
If you have to use a case statement, then this is what you're going for, I think:
UPDATE dbo.tbl_Cpay
SET Cpay_Stop = CASE WHEN Cpay_Stop IS NULL
THEN CONVERT(DATETIME, '1900-01-01 00:00:00', 102) ELSE Cpay_Stop END;
But be aware of what this is doing. It's hitting every row in the table and either updating the column to 1 Jan 1900 or to itself. This is a terribly inefficient way to accomplish this. You're issuing updates to rows that don't need to be updated.
August 11, 2010 at 3:04 pm
Thanks, this is what I was looking for 🙂 I do realize that every row/column will affected but I will be building other CASE statement for when Cpay_Stop IS NOT NULL but rather another date on which I will need to update a different column within the same row.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply