Altering Stored procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Altering Stored procedure

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-14 : 10:37:50

Hi Everyone
Need help altering a stored procedure based on some logic

Below is my stored procedure

ALTER PROCEDURE [dbo].[StepFourFive_Cmplt_Rpt]
(
@RQId as numeric = null,
@RDID as numeric = null,
@deliverydate datetime=null,
)
AS
SET NOCOUNT ON
---
--- Retrieve the next detail_id
---
update Slctdrep
set
deliverydate = @deliverydate
where requestid=@RQID and
rptdesc=@RDID

The deliverydate needs to be updated to the database only when the date is not equal to 1/1/1900 else it should have the same value as it was.I was not able to acheive this at the code level

need help

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 12:22:49

COALESCE(NULLIF(@date,'19000101'),deliverydate)

Poor planning on your part does not constitute an emergency on my part.

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-14 : 12:37:50

I tried the below and get an error

ALTER PROCEDURE [dbo].[StepFourFive_Cmplt_Rpt]
(
@RQId as numeric = null,
@RDID as numeric = null,
@deliverydate datetime=null,
)
AS
SET NOCOUNT ON
---
--- Retrieve the next detail_id
---
update Slctdrep
set
deliverydate = COALESCE(NULLIF(@date,'19000101'),deliverydate)

where requestid=@RQID and
rptdesc=@RDID

Error Message

Msg 137, Level 15, State 2, Procedure StepFourFive_Cmplt_Rpt, Line 38
Must declare the scalar variable "@date".

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 12:49:11

I presumed you would substitute the correct variable name. Change @date to @deliverydate

[i]Poor planning on your part does not constitute an emergency on my part.

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-14 : 14:14:05

Works great.Thank you much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources