When values are provided to the following script then executed using a setup in C like below or in SQL Server environment the values do not update in the database.

After running an execute via C code or SQL Server environment it does not update dbo. I'm guessing that it might be because dbo.

SelectedOptionID is non-nullable and the parameter I'm using to set it is nullable despite that when setting it shouldn't ever be null. Upon execution the return value is 0. If I run one of the Updates outside of the procedure they work perfectly, hence my suspicion that it has to do with null-able types. Could this be because the parameters to the stored procedure are nullable and the fields I'm setting aren't?

It looks like you're passing in Null for every argument except for PropertyValueID and DropDownOptionID, right? I don't think any of your IF statements will fire if only these two values are not-null. In short, I think you have a logic error.

Second, add a meaningful PRINT statement before each UPDATE. That way, when you run the sproc in MSSQL, you can look at the messages and see how far it's actually getting.

Stored procedures with optional parameters that can be null

By posting your answer, you agree to the privacy policy and terms of service. Stack Overflow Questions Work from home without investment in delhi Jobs Documentation beta Tags Users.

Sign up or log in to customize your forex factory trade explorer. 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. Join the Stack Overflow Community. Stack Overflow is a community of 7.

CREATE PROCEDURE (Transact-SQL) | Microsoft Docs

Join them; it only takes a minute: SQL Server stored procedure Nullable parameter Ask Question. IF Value IS NOT NULL BEGIN UPDATE [dbo]. T sql stored procedure not null parameter IF DropDownOptionID IS NULL BEGIN UPDATE [dbo]. Add new SqlParameter " PropertyValueID", System. Add new SqlParameter " T sql stored procedure not null parameter, System.

NULL Input Parameter to Stored Procedure

Add new SqlParameter " UnitValue", System. Add new SqlParameter " UnitOfMeasureID", System. Add new SqlParameter " DropDownOptionID", System. If not, what could it be? Shelby 1, 1 17 I updated my question, added "If I run one of the Updates outside of the procedure they work perfectly, hence my suspicion that it has to do with null-able types.

I'm not trying to do output parameter. I'm not sure what you mean by "via sql block". Does your SP executes if you exexut it via sql block? In your case, since you have hardcoded Cmd.

tsql - How can I use optional parameters in a T-SQL stored procedure? - Stack Overflow

Value;you might as well just omit it. Feb 20 '14 at Other than that, I would suggest two things First, instead of testing for NULL, use this kind syntax on your if statements it's safer ELSE IF ISNULL UnitValue, 0! Ash 2 Wow, I feel dumb. It should be ELSE IF DropDownOptionID IS NOT NULL like the rest of them: How is using ISNULL It seems like your suggestion is less safe because, for one reason at least, now you have a magic value zero that is treated as equivalent to NULL.

If you're going to use or allow null values, testing for NULL and only NULL is the safest thing to do. Add " Param", SqlDbType. Pang 5, 14 50 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. If you change those Insert and Updates to Selects, do you return data for the supplied parameters?

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.