CODEWITHSUNDEEP
sql-serversql-server-2008xquery
Rubens Farias
Rubens Farias

Reputation: 57966

How to compare sql:variable with NULL within XQuery

I have this stored procedure which receives a @Value parameter. I need to use that value to filter some data, or to ignore the filtering, if the parameter value is NULL.

Using DML only would be as simple as 

WHERE @Value IS NULL OR Value = @Value

But I don't know how to use the same idea inside the XQuery expression. Right now, I have a code similar to this:

DECLARE @Value  AS VARCHAR(100) = 'f7fc6b6c-bb1b-4961-bb94-1053500436ac'

SELECT          Form.FormType,
                FormData         .value('(/*/Created)[1]'   , 'date'        ) Created,
                FormDataItem.this.value('@Source'           , 'varchar(100)') Source,
                FormDataItem.this.value('@Value'            , 'varchar(100)') Value,
                FormDataItem.this.value('@Text'             , 'varchar(100)') Text
FROM            Form    WITH    (NOLOCK)
CROSS APPLY     Form.FormData.nodes('//*[
                (
                    (sql:variable("@Value")  = "" and @Value != "") or
                    (sql:variable("@Value") != "" and @Value  = sql:variable("@Value"))
                )]') FormDataItem(this)

I could avoid this problem by:

but I would like to avoid that.

Can you advise how am I supposed to compare a sql:variable to a NULL value inside a XQuery?

Upvotes: 2

Views: 413

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

If @Value is a T-SQL variable, empty(sql:variable("@Value")) is true precisely when @Value is NULL (empty strings don't qualify).

Upvotes: 1

Related Questions