CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2005stored-procedures
user1681514
user1681514

Reputation: 97

SQL Server : stored procedure IFNULL check

Solution_id (Primary key, Int)
Col1 (varchar)
Col2 (varchar)
Col3 (varchar)
Col4 (varchar)
Col5 (varchar)

I am writing a stored procedure to update this table. There are 6 input parameters for the above 6 columns.

@Attached_File1 VARCHAR(MAX),
@Attached_File2 VARCHAR(MAX),
@Attached_File3 VARCHAR(MAX),
@Attached_File4 VARCHAR(MAX),
@Attached_File5 VARCHAR(MAX),
@Ticket_ID BIGINT

I want to write a SQL query which will update the table with the values specified in the input parameters. BUT I must not overwrite the attachment columns with null. I mean I need to use only those parameters which contains data.

For example, if the table has a row

[10, "aaa", "bbb", "efg", null, null]

and the input parameters are 

(10, null, null, "mno", "ddd", null)

then after the update the row will become 

[10, "aaa", "bbb", "mno", "ddd", null]

How to check for null/empty strings and generate the update query accordingly to achieve this?

Upvotes: 2

Views: 4589

Answers (4)

Andomar
Andomar

Reputation: 238086

update  YourTable
set     col1 = isnull(@Attached_File1, col1)
,       col2 = isnull(@Attached_File2, col2)
,       col3 = isnull(@Attached_File3, col3)
,       ...
where   Solution_ID = @Ticket_ID

If the parameters can contain empty strings, consider @freefaller's answer. If it can contain whitepsace, try:

set     col1 = case 
               when @Attached_File1 like '%[^ \t\r\n]%' then @Attached_File1 
               else col1 
               end
,       col2 = ...

Upvotes: 2

freefaller
freefaller

Reputation: 19953

Is this something like you're after?

UPDATE mytable
SET Col1 = ISNULL(@Attached_File1, Col1),
    Col2 = ISNULL(@Attached_File2, Col2),
    Col3 = ISNULL(@Attached_File3, Col3),
    Col4 = ISNULL(@Attached_File4, Col4),
    Col5 = ISNULL(@Attached_File5, Col5)
WHERE Solution_id = @Ticket_ID

ISNULL takes two values, if the first one is not null then it is used, otherwise the 2nd value is used.

See MSDN for more information on ISNULL

Update

I've just noticed your comment at the end, which talks about empty strings...

How to check for null/empty strings and generate the update query accordingly to achieve this?

In which case, you could do the following...

UPDATE mytable
SET Col1 = ISNULL(NULLIF(@Attached_File1,''), Col1),
    Col2 = ISNULL(NULLIF(@Attached_File2,''), Col2),
    Col3 = ISNULL(NULLIF(@Attached_File3,''), Col3),
    Col4 = ISNULL(NULLIF(@Attached_File4,''), Col4),
    Col5 = ISNULL(NULLIF(@Attached_File5,''), Col5)
WHERE Solution_id = @Ticket_ID

This uses the NULLIF statement which takes two values, if the first value is the same as the second value, then NULL is returned, otherwise it returns the first value.

See MSDN for more information on NULLIF

Upvotes: 3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Checking only for Nulls, not for empty strings:

UPDATE 
    tableX
SET
    Col1 = COALESCE(@Attached_File1, Col1),
    ...
    Col5 = COALESCE(@Attached_File5, Col5)
WHERE
    Solution_id = @Ticket_ID ;

Upvotes: 0

Vikdor
Vikdor

Reputation: 24124

I would try this:

UPDATE Table
SET
    Col1 = ISNULL(@Attached_File1, Col1),
    Col2 = ISNULL(@Attached_File1, Col2),
    Col3 = ISNULL(@Attached_File1, Col3),
    Col4 = ISNULL(@Attached_File1, Col4),
    Col5 = ISNULL(@Attached_File1, Col5),
WHERE
    Solution_Id = @Ticket_ID

Upvotes: 0

Related Questions