Not able to check null character in T-SQL

Question

I have a class with following property:

public char Status{get;set;}

This value is passed to T-SQL stored procedure to insert into a table.

The problem arises when no values is assigned to this property. It passes \0 to SP. In SP I am not able to figure out if value is present or not. Both of the following checks fail.

@value = '' OR @value IS NULL

What is the right way to check if passed parameter (@value CHAR(1)) contains a valid value or not?

The answers to question What is the Null Character literal in TSQL? resolved my issue. Hence closing this question.

Seems related? http://stackoverflow.com/questions/2828333/what-is-the-null-character-literal-in-tsql — Soner Gönül, Jun 13 '14 at 06:08

Mike Hixson · Answer 1 · 2014-06-13T16:44:57.497

0

char can not be null, because it is a value type. So something is setting your Status property to '\0'. You should check for that value before calling your stored procedure. If it is found send DBNull.Value in its place to you stored procedure.

if(Status == '\0')
    command.Parameters.AddWithValue("@value", DBNull.Value);
else
    command.Parameters.AddWithValue("@value", Status);

Then in your stored procedure you can simply check for

@value IS NULL

If you are not able to change the code, then inside the stored procedure you can check for the character value of 0 like so:

@value = CHAR(0)

edited Jun 13 '14 at 16:44

answered Jun 13 '14 at 05:08

Mike Hixson

5,071
1
19
24

Exactly this is happening but the problem is that I cannot change the code and need to handle this in T-SQL only. – TheVillageIdiot Jun 13 '14 at 11:27
I updated my answer to show how to check for that value in the stored procedure. – Mike Hixson Jun 13 '14 at 16:45
No only this will not be enough, as explained in the answer linked by @Soner Gonul in comment to question. – TheVillageIdiot Jun 14 '14 at 04:01

Not able to check null character in T-SQL

1 Answers1