I would like to only set the values if they are passed into the SP, i.

Can this be done? This question seems to suggest the only way is using completely separate queries with conditionals, but for 3 optional parameters this would obviously be a nightmare!

It might also be worth adding an extra part to the WHERE clause, if you use transactional replication then it will send another update to the subscriber if all are NULL, to prevent this. I think I remember seeing before that if you are updating to the same value SQL Server will actually recognize this and won't do an unnecessary write.

Or if you meant you only want to update individual columns you would use the post above mine. I read it as do not update if any values are null. By posting your answer, you agree to the privacy policy and terms of service.

By subscribing, you agree to the privacy policy and terms of service. Stack Overflow Questions Developer Jobs Documentation beta Tags Users. Sign up or log in to customize your list. Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us.

Log In Sign Up.

sql - How update Stored Procedure has Optional Parameter? - Stack Overflow

Join the Stack Overflow Community. Stack Overflow is a community of 7. Join them; it only takes a minute: Using UPDATE in stored procedure with optional parameters Ask Question.

Specifying a Parameter Default Value

I have an SP like so using SQL Server: It is not best practice, that is you subjective preference as COALESCE is ANSI. Both functions act differently, to say that you should always use one over the other as best practice is wrong.

optional parameters in SQL Server stored proc? - Stack Overflow

But in this case they act the same. If the parameter in the stored procedure is null, and the current value for the field is also null, you will get an error for not providing at least one non-null value to the COALESCE function.

So yes, you were correct in using ISNULL , but they do not act the same in this case. Noah i've just checked this out, the error you get from COALESCE NULL, NULL is that one of the arguments must be a typed null.

As the parameters are typed it should be fine. The way the two infer the return types are different which is one of the reasons why the behavior is different. MSDN has quite a bit of detail on it msdn. I got a question, if I have Null values is it required to identify the field as null in order to update it? Martin Smith k 45 Ian Jacobs 4, 1 16 FlyingStreudel 2, 2 22 Indeed, I even wrote that.

Sign up or log in StackExchange. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. In it, you'll get: The week's top questions and answers Important community announcements Questions that need answers.

Stack Overflow works best with JavaScript enabled. I read it as do not update if any values are null share improve this answer. This will never update unless all 3 parameters are provided. MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3.

Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.