Please help me find out the best solution for my query. I have dynamic sql with coalesce in where clause but it did not function well.
SET @sql = 'SELECT * FROM tbl1 WHERE ID = COALESCE(''' + @ID + ''', ID)'
and when I run this SP and i call
Exec sp_GetID @ID = null,
it did not return a value.
But when i tried something like this
SELECT * FROM tbl1 WHERE ID = COALESCE(@ID , ID)
it works fine as expected.