CODEWITHSUNDEEP
sql
Nate Pet
Nate Pet

Reputation: 46222

SQL WHERE IN causing issue

I had the following portion of a where - note that @TRUCK is an input parameter of the stored proc:

WHERE TRUCK = COALESCE(@TRUCK, ATP.TRUCK) AND  ...

I like to pass 

SELECT value from dbo.fn_split(@TRUCK, '~')

in place of @TRUCK as I like to pass it a ~ delimited list.

I tried using:

WHERE TRUCK IN (COALESCE(SELECT value from dbo.fn_split(@TRUCK, '~')),ATP.TRUCK)

but got the following error:

Incorrect syntax near the keyword 'SELECT'.

Upvotes: 1

Views: 183

Answers (2)

Marc B
Marc B

Reputation: 360672

You need to surround the subquery with (), and you've got the middle ) misplaced:

WHERE TRUCK IN (COALESCE((SELECT value from dbo.fn_split(@TRUCK, '~')),ATP.TRUCK))

Upvotes: 1

Jay
Jay

Reputation: 435

not sure - but i would expect you would have to assign the 'select value from dbo.fn...' to a local variable and then reference the local variable in the coalesce(...)

Upvotes: 0

Related Questions