CODEWITHSUNDEEP
t-sql
htm11h
htm11h

Reputation: 1779

Update Sql db with a NULL value passed from Parameter

I need to store a value with Null which is passed from a parameterized query using VB.net into SSEE 2008 R2.

The value maybe either 'Null' or a blank string "". How can I test for this and properly UPDATE the field in my Stored Procedure?

EDIT: Added declarations.

@ID int,    
@currTable varchar(150),
@prev_LangString nvarchar(max),
@brief_Descrip nvarchar(max)

BEGIN
 IF @brief_Descrip IS NULL OR @brief_Descrip = 'Null'
 SET @brief_Descrip = 'Null';
END

BEGIN
 SET @sql = 'UPDATE ' + @currTable + ' SET [date_Changed] = ''' +  convert(varchar(20), @submitDate1) + ''', [prev_LangString] = ''' + @prev_LangString + ''',  [brief_Descrip] = ''' + @brief_Descrip + '''
       WHERE (ID = ' + CAST(@ID as nvarchar(10)) + '); '

       EXECUTE(@sql);
END

Thanks for any help on this.

Upvotes: 0

Views: 905

Answers (2)

ssarabando
ssarabando

Reputation: 3517

Another option would be to use a parameterized query. It would even make your problem go away. Like this:

DECLARE @sql NVARCHAR(4000), @params NVARCHAR(4000)
SET @sql = 'UPDATE ' + @currTable + ' SET date_changed = @p0, prev_langstring = @p1, brief_descrip = @p2 WHERE id = @p3'
SET @params = '@p0 VARCHAR(20), @p1 VARCHAR(???), @p2 VARCHAR(???), @p3 NVARCHAR(10)'

DECLARE @sd VARCHAR(20), @sid NVARCHAR(10)
SET @sd = CONVERT(VARCHAR(20), @submiteDate1)
SET @sid = CAST(@ID AS NVARCHAR(10))
EXEC sp_executesql @sql, @params, @p0 = @sd, @p1 = @prev_langstring, @p2 = @brief_descrip, @p3 = @sid

I don't know the datatype of @prev_langstring and of @brief_descrip, hence the VARCHAR(???) you see above; replace it by the real datatype. You can read about sp_executesql here.

Upvotes: 0

mikerobi
mikerobi

Reputation: 20878

The problem is that you are converting @brief_Descript to a string. This will also fix your injection vulnerability.

BEGIN
 IF @brief_Descrip = 'Null'
 SET @brief_Descrip = NULL;
END

UPDATE TABLE table_name
SET
  date_Changed = convert(varchar(20), @submitDate1),
  prev_LangString = @prev_LangString,
  brief_Descrip =  @brief_Descrip,
WHERE
  ID = CAST(@ID as nvarchar(10))

EDIT

The best way to fix this is to convert the string null to DBNull in vb.net, and use a parameterized query with an update statement. You should not convert the date to a string. Change the column type to a date time.

Upvotes: 2

Related Questions