How to handle NULL values for INT variables in ADO.NET?
At some point, I need to send NULL as the value of an INT parameter to a stored procedure but an Int32 variable seems to not allow this. What is the reccomended way to handle this scenario?Paulo Morgado
December 17th, 2010 10:20am

You could try using nullable types? http://msdn.microsoft.com/en-us/library/1t3y8s4s.aspx
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 10:22am

OK! I'll play along. How would I do it?Paulo Morgado
December 17th, 2010 10:44am

rather than declaring a variable as int a instead say int? a you can now set a to null and there are a few methods on the variable that you can call to check if its null etc
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 10:46am

No, this won't work. SSIS doesn't support nullable types for its variables. You'll just have to get creative with your variables and stored procedure call. You'll need to make two variables to represent your nullable INT: the INT itself, and a variable to indicate if it's null or not. DECLARE @intvalue INT DECLARE @intnull INT SET @intvalue = ? SET @intnull = ? DECLARE @int INT IF (@intnull == 1) SET @intvalue = NULL ENDIF EXEC SP @intvalue So instead of a plain "EXEC SP ?, ?, ?" type call, you'll need to declare some variables in your T-SQL, like: You'll then be able to map your SSIS variables to the intermediate variables in your statement, yet call the SP with the all-in-one value you need. Talk to me now on
December 17th, 2010 10:57am

As I suspected. :( This renders the usage of IsQueryStoredProcedure=true useless in many cases. If the .NET null is sent as the value of a parameter on stored procedure invocation, it will be assumed as DEFAULT. In .NET DBNull.Value is the NULL database value. I'm declaring a SSIS Object variable to hold the DBNull.Value and resisting to have every variable typed as Object.Paulo Morgado
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 11:21am

I'm not entirely sure what benefit the IsQueryStoredProcedure flag gets you, it's not described very well anywhere, but I found this old article on the SSIS Team Blog: Stored Procedures with Output Parameters... but that still doesn't tell me what the difference between using it and not using it does, aside from not needing to use the EXEC keyword. I would resist declaring every variable as an Object, simply because it would probably make using other tasks and components in your package harder... Talk to me now on
December 18th, 2010 2:51pm

I'm not entirely sure what benefit the IsQueryStoredProcedure flag gets you, it's not described very well anywhere, but I found this old article on the SSIS Team Blog: Stored Procedures with Output Parameters... but that still doesn't tell me what the difference between using it and not using it does, aside from not needing to use the EXEC keyword. I would resist declaring every variable as an Object, simply because it would probably make using other tasks and components in your package harder... Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
December 18th, 2010 2:51pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics