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-25 : 16:42:20

I need to alter the below stored procedure such that whenever the asgto value is being inserted to the database as Null
It needs to be changed to 10

ALTER PROCEDURE [dbo].[Assignment]
(

@RDID as int=null,
@AsgTo as int=null,
@AgnDt datetime = null
)
AS
UPDATE SELECTED_REPORT
SET assignto =@AsgTo,
Confirmdt = @AgnDt
WHERE reqid = @RQID

need help

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-25 : 16:47:03

Use the COALESCE function on the @AsgTo variable.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-01-25 : 17:29:32

Or

@RDID as int=null,
@AsgTo as int=10,
@AgnDt datetime = null

And I would do a drop and create and grant, instead of alter

Tara's suggestion is

SET assignto = COALESCE(@AsgTo,10)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-01-26 : 10:19:53

It actually did not work for me.It is still inserint null values to the table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-26 : 10:45:34

Is it inserting the word NULL or an actual NULL value?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-01-26 : 12:03:45