XML parameter is NULL to indicate all values should be returned

[Craftor]

Hi all,

I'm passing a list of values to a stored procedure using an XML parameter.

The stored procedure is used to search a table for matching values so this list can contain one or many values, or could be NULL to indicate all values should be returned.

How can I phrase my query to cope with the scenario of the parameter being NULL to select all values?

I currently have the following code to cope with one or many values being passed:

SELECT * FROM dbo.MyTable
WHERE idField IN 
(SELECT paramvalues.id.value('.', 'int') FROM @testxml.nodes('/rootnode/idfieldvalue') as paramvalues(id))

Thanks as always


Craftor