Stored procedure optional parameter insert

Stored procedure optional parameter insert

Posted: b-o-n-y On: 10.06.2017

Let's define the table like so:. I've greatly simplified the data structures I am dealing with but I hope this serves my point. My question is in regards to how the parameters are processed. Is there a way to determine within the procedure if Sort was passed in as NULL or set NULL by the default declaration in the parameter list?

The purpose of this is that I don't want NULL parameters to override any columns in the UPDATE statement unless they are explicitly passed in that way. No, you can't detect how Sort became NULL. If your goal is to capture when it is explicitly set versus it being set by the default, I would suggest using a different default value maybe one that wouldn't normally be used, like Then you can assume that if Sort is NULL, it was explicitly passed in, but if it is -1, you know it was set by default.

I think this is what your looking for.

If one of the parameters is null, it will updatedit with the value in the database. The other option is update one column at a time. Take out the default value and and then the code calling the proc must provide a value either a real value or NULL.

By posting your answer, you agree to the privacy policy and terms of service. Stack Overflow Questions Recognized stock exchange sfc Jobs Documentation beta Tags Users. Sign up or log in to customize your list.

How to share data between stored procedures

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 Stored procedure optional parameter insert Us Learn more about Stack Overflow the company Business Learn more about hiring developers or options trading ppt stored procedure optional parameter insert with us.

Log In Sign Up. Join the Stack Overflow Community.

sql - Stored Procedure NULL Parameter - Stack Overflow

Stack Overflow is a community of 7. Join them; it only takes a minute: Stored Procedure NULL Parameter Ask Question. Let's define the table like so: CREATE TABLE Foo Id INT IDENTITY 1, 1Name VARCHAR NOT NULL, ShortName VARCHAR 32Sort INT ; I have written a stored procedure similar to the following: Id FROM Foo AS F WHERE F.

Why don't you just make the columns not have defaults?

Tony Casale 1, 7 7. That could work but I wanted to be able to generalize out to multiple procedures and parameters. The end goal being I don't want NULL values that aren't explicitly passed in that way to override columns if the UPDATE is reached.

If SQL supported overloading procedures you could create 2 versions of the sproc, 1 with the param, and the other without. Alas, it's not possible. Can you give any more context as to what you're trying to accomplish?

sql - using insert in stored procedure with optional parameters - Stack Overflow

Detecting how the value was set isn't really a viable option. Andrew Neely 6 I don't want to always have to pass the parameter in though.

Stored Procedure | gepahotalefi.web.fc2.com

Why would it override it if it created a Foo? Sign up or log in StackExchange. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name.

Stack Overflow works best with JavaScript enabled. Take out the default value and and then the code calling the proc must provide a value either a real value or NULL share improve this answer.

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.

Rating 4,7 stars - 281 reviews
inserted by FC2 system