Valbuenito (7/16/2014)
Hi Eirikur,Thanks for your reply.
But, I do not think that answers my problem because I don't master on my parameter.
This request comes of Reporting, and the parameter is a choice of many propositions.
That is why I would have a condition in the when condition.
My table :
ID CODE VALUE1
----- -------- ------------
01 C001 ABC
02 C002 DEF
03 NULL NULL
04 NULL JKL
05 NULL NULL
My request of my Reporting:
SELECT ID, Code, Value1
FROM Table1 T1
WHERE T1.[Code] IN (@Param)
The ALL Choice Of @Param of my Reporting : ALL, C001, C002, 'NULL'
If @Param = ('C001','C002), it's OK. The result is :
ID CODE VALUE1
----- -------- ------------
01 C001 ABC
02 C002 DEF
But If @Param = ALL (ALL = ('C001','C002','NULL'), it son't work. The NULL type isn't returned :
ID CODE VALUE1
----- -------- ------------
01 C001 ABC
02 C002 DEF
So, I search a means to look my lines with an NULL Code when my parameter is filter on ALL.
There is a flaw in the code
😎
SELECT ID, Code, Value1
FROM Table1 T1
WHERE T1.[Code] IN (@Param)
Unless the @Param is a table variable used in either a nested query or a join, it will only work with a single parameter.
Here is a more correct example
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @PARAM TABLE (PARAM_VAL VARCHAR(50));
DECLARE @test-2 TABLE (ID INT NOT NULL, CODE VARCHAR(50) NULL, VALUE1 VARCHAR(50) NULL);
INSERT INTO @test-2(ID,CODE,VALUE1) VALUES (1,'C001','ABC'),(2,'C002','DEF'),(3,'NULL','NULL'),(4,'NULL','JKL'),(5,'NULL','NULL');
INSERT INTO @PARAM(PARAM_VAL) VALUES ('C001'),('C002'),('NULL');
SELECT
T.ID
,T.CODE
,T.VALUE1
FROM @test-2 T
INNER JOIN @PARAM P
ON T.CODE = P.PARAM_VAL;