Reputation: 225
What I have to achieve is something like appending string to existing variable for e.g. :
string abc = "good";
abc += "afternoon";
so that the final output will be "good afternoon"
I have written the following stored procedure :
CREATE OR ALTER PROCEDURE [dbo].[sp_getPropertyContactUsDetails]
(@isDeleted CHAR(1),
@cName VARCHAR(MAX))
AS
BEGIN
SET NOCOUNT ON;
--DECLARE @mainQ varchar(max)
SELECT
pcd.col1, pcd.col2,.....
CASE
WHEN pcd.col1 = 'abc'
THEN (SELECT pname FROM tbl_pd1 WHERE id = pcd.id)
WHEN pcd.col2 = 'def'
THEN (SELECT pname FROM tbl_pd2 WHERE id = pcd.id)
END AS 'pname',
FROM
tbl_pcd pcd
WHERE
pcd.isDeleted = @isDeleted
Now I want to append a part to the query based on if else condition as following:
IF @cName IS NOT NULL
THEN
append following part to the select query :
ORDER BY ID DESC
IF @cName IS NULL then don't append the part!
I have tried with declaring a parameter as :
DECLARE @mainQ VARCHAR(MAX)
Then I am doing something like :
SET @mainQ = 'The select statement as specified above in the stored procedure'
T hen for append part :
IF @cName IS NOT NULL
SET @mainQ = @mainQ + ' ORDER BY ID DESC'
END (END statement of stored procedure)
Can anyone help me out with the following situations, how to append a part to original query based on if else condition??
Note that stored procedure may contain multiple if statements!
Upvotes: 0
Views: 1611
Reputation: 114
If you just want add to order by clause then you can use something like -
ORDER BY
CASE WHEN @cName IS NOT NULL
THEN ID
END DESC
Upvotes: 1