Replace blank('') as NULL in Stored Procedure

General
N/A

Replace blank('') as NULL in Stored Procedure

Hi,

     In SP if parameter has value as blank(''), then i have to change tat as NULL. Please help me.

7 REPLIES
N/A

Re: Replace blank('') as NULL in Stored Procedure

You can use the basic SQL NULLIF(parameter, '')

Dieter

N/A

Re: Replace blank('') as NULL in Stored Procedure

Hi Dieter,
Thanks for ur response..
The below query is working if 'Column1' is not null and parameter is blank('')..
Select * from table where COALESCE((Nullif(Parameter,'')), TRIM(Column1)) LIKE '%'|| TRIM(Column1) || '%'
If 'Column1' is null and prameter is blank('') its not working. Can u plz help me

Re: Replace blank('') as NULL in Stored Procedure

I think is not linked with the blank parameter, it is related with the value in Column1.

When the column 1 is null, then the comparison with like is a little complex. 

Can you please paste the error you are facing, or output when column1 is null?

Khurram
N/A

Re: Replace blank('') as NULL in Stored Procedure

When Column1 is NULL your calculation is: WHERE NULL LIKE NULL and this is NOT TRUE.

What do you want as result when one or both are NULLs?

Dieter

N/A

Re: Replace blank('') as NULL in Stored Procedure

For example I have 10records having column1 value  as NULL.

Select * from table where column1 IS NULL -- It'l return 10 rows

In my above query, the parameter value may be 'AAA'/blank('').. If its value based on that it has to return rows..in my case no rows will return...If it's  blank(''),it has to return 10 rows.....

If column1 is null, it didnt return any row

N/A

Re: Replace blank('') as NULL in Stored Procedure

Hi Selvi,

if "Parameter" is NULL or '' then return NULL rows else do the LIKE comparison?

WHERE (Parameter LIKE '%'|| TRIM(Column1) || '%')
OR (NULLIF(Parameter, '') IS NULL AND Column1 IS NULL)

Dieter

N/A

Re: Replace blank('') as NULL in Stored Procedure

Thanks Dieter!!