February 22, 2019 at 10:14 am
There is this a stored procedure that joins table to retrieve the required results. It needs to filter it by a @cha_id, and where it is not provided by the user, it will take whatever is in tablea accordingly. How do we handle COALESCE in a dynamic SQL query? I couldn't get it as CONVERT should also be undergoing for @cha_id. For example, if the COALESCE or ISNULL wasn't required, this would work
WHERE tablea.chan_id = '''+CONVERT(VARCHAR,@chan_id)+'''
would work. But I couldn't figure it out in the case I require the COALESCE/ISNULL.
WHERE tablea.chan_id = COALESCE(CONVERT(VARCHAR,@chan_id), tablea.chan_id)
I've been researching in vain. Any help will be much appreciated.
would work. But I couldn't figure it out in the case I require the COALESCE/ISNULL. WHERE tablea.changelogtype_id = COALESCE(@cha_id, tablea.chan_id)I've been researching in vain. Any help will be much appreciated.
February 22, 2019 at 10:42 am
How do we handle COALESCE in a dynamic SQL query?
In the WHERE clause, don't.
WHERE (@chan_id IS NULL OR tablea.chan_id = CAST(@chan_id AS varchar(30)))
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply