CODEWITHSUNDEEP
sqlsql-serverstored-procedures
ace
ace

Reputation: 225

How to append a part to query based of if else conditions within a stored procedure

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

Answers (1)

Sharad Venkataraman
Sharad Venkataraman

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

Related Questions