CODEWITHSUNDEEP
sqlsql-server
Amit
Amit

Reputation: 7035

variables in inline table valued function

I have a table valued function that requires three parameters.

If any of the passed in parameters are blank I need to assign the parameter to null. Please can anyone advise me on how/where can i put the below if conditions in the function.

IF @CLIENTPK_NEW = '' 
 BEGIN
 @CLIENTPK_NEW=NULL
 END

 IF @CGNEEPK= ''
 BEGIN
 @CGNEEPK=NULL
 END


CREATE FUNCTION Function_test
(      
 @CLIENTPK_NEW varchar(50),    
 @CGNEEPK varchar(50),    
 @type varchar(100)
)   
RETURNS TABLE      
AS      

RETURN   

SELECT   COl1,COl2
FROM Table1
where COL1 is not null AND
(@CLIENTPK_NEW IS NULL OR COL1 =@CLIENTPK_NEW ) AND
(@CGNEEPK IS NULL OR COL2 =@CGNEEPK)

Upvotes: 0

Views: 417

Answers (1)

AdaTheDev
AdaTheDev

Reputation: 147224

You can use NULLIF:

SELECT   COl1,COl2
FROM Table1
where COL1 is not null AND
(NULLIF(@CLIENTPK_NEW, '') IS NULL OR COL1 = NULLIF(@CLIENTPK_NEW, '') ) AND
(NULLIF(@CGNEEPK, '') IS NULL OR COL2 = NULLIF(@CGNEEPK, ''))

However, if this is a follow on from your previous question, please see my answer in there as I don't think you need this workaround if that is the source of this particular question as I demonstrate there, you can pass NULL straight in.

Upvotes: 1

Related Questions