Assign NULL to all parameters containing empty string 1

[rgatrell]

I'd like to create a function (or perhaps it should be a stored procedure?) that I can call from any stored procedure.

It needs to loop through the values of all parameters, checking to see if any of them contain an empty string and if so, assign NULL to each.

I'm aware that you can assign NULL as a default value but this isn't suitable for my situation.

Many thanks.

 

[gmmastros]

I don't think you will be able to write a function like that because each stored procedure (or function) will have an unknown number of parameters.

There is a built-in function for this called NullIf

Ex:

Set @Parameter1 = NullIf(@Parameter1, '')
Set @Parameter2 = NullIf(@Parameter2, '')

1. If the value is NOT an empty string, the value will be unchanged.
2. If the value is already NULL, it will still be NULL
3. If the value is an empty string, it will be set to NULL.

Empty string comparisons are weird, too. Just a little heads up.

[COLOR=blue]Declare[/color] @Blah [COLOR=blue]varchar[/color](20)

[COLOR=blue]Set[/color] @Blah = [COLOR=#FF00FF]space[/color](20)

[COLOR=blue]Select[/color] @Blah
[COLOR=blue]Select[/color] [COLOR=#FF00FF]NullIf[/color](@Blah, [COLOR=red]''[/color])

@Blah is set to a string of spaces, but the NullIf check will set it to NULL. Point is... if you have a stored procedure where you want to store spaces, then you'll need to look in to another solution.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rgatrell]

Hi George,

I've tried what you said and that seemed to do the job. To be honest there are only 5 or so parameters that may need to be checked and that's quite a neat solution to my problem.

Thanks!