Concatenate query string in procedure having case statement with parameter

Question

I need to append below select query set with another set query based on condition

    DECLARE @queryString VARCHAR(1000);
    -- Insert statements for procedure here
    Set @queryString ='SELECT 
                CASE 
                                WHEN d.sno IS NOT NULL THEN 'y'
                                ELSE NULL 
                END 
                amendment_type, 
                u.user_login_id [User], 
                role_name [Role], 
                u.user_name [Name], 
                a.companyname        +':<br>('+b.branch+')' [Agent/Branch], 
                u.last_login_pc+'('+u.ip_address+')<br> Login Time: '+cast(u.last_login AS varchar(50)) [Last Login], 
                u.lock_status [Lock/Unlock], 

                CASE 
                                WHEN u.lock_status='y' THEN 'Locked - '+ u.lock_by
                                WHEN datediff(d,u.last_login,getdate())>u.lock_days 
                                AND             isnull(u.lock_days,0)>0 THEN 'Locked - Day Exceed'
                                WHEN u.active_session IS NULL 
                                AND             isnull(u.lock_status,'n')='n' THEN 'Not Login'
                                ELSE 'Active'
                END 
                [Status], 
                u.*, 
                a.agentcan, 
                b.branch, 
                b.branchcodechar, 
                NULL branchcan
FROM            agentsub u 
JOIN            agentbranchdetail b 
ON              u.agent_branch_code=b.agent_branch_code 
JOIN            agentdetail a 
ON              b.agentcode=a.agentcode
LEFT OUTER JOIN application_role_agent_user r 
ON              u.user_login_id=r.user_id 
LEFT OUTER JOIN application_role p 
ON              r.role_id=p.role_id 
LEFT OUTER JOIN agentsub_amendment d 
ON              d.agent_user_id=u.agent_user_id
WHERE           1=1'

IF (@loginId !=null)
BEGIN
 SET @queryString =@queryString + 'and u.user_login_id like ('+ @loginId +')'
END


SET @queryString =@queryString +'order by u.user_login_id,a.companyname,b.branch'

EXEC @queryString

*Don't* concatenate anything. Create a view with the query (except the `WHERE 1=1` part). After that you can easily create queries on top of that view and probably won't need a stored procedure at all. — Panagiotis Kanavos, Sep 27 '19 at 09:10
Use views then and query them from one or two stored procedures - one for all resutls, one for filtered results. The syntax coloring alone should show you that the query contains a lot of single quotes that result in a bad string. Do the people that mandate stored procedures also mandate that there should be sql script errors, conversion problems and SQL injection attacks? — Panagiotis Kanavos, Sep 27 '19 at 09:48
If you really don't want vies (*why?*), you can still create two separate stored procedures, one with the unfiltered query, on with the filtered query. Using views avoids both errors and code duplication though — Panagiotis Kanavos, Sep 27 '19 at 09:49

score 0 · Answer 1 · answered Sep 27 '19 at 09:44

First... Your query as it stands (via notepad++) is 1,731 characters, so it wont fit into a varchar(1000)

Second... You need to address some escaping issues with your query; e.g.

WHEN d.sno IS NOT NULL THEN 'y'

should probably be

WHEN d.sno IS NOT NULL THEN ''y''

Third... google/investigate sp_executesql and parameters

Finally... as @Panagiotis Kanavos says, Don't concatenate anything (see Third...); you need to look up "sql injection"

score 0 · Answer 2 · edited Oct 01 '19 at 06:29

@uberbloke is correct regarding query length.

EXEC sp_executesql with multiple parameters link give you solution for dynamic query to pass parameter values.

Please check below updated query code.

DECLARE @queryString NVARCHAR(MAX);

    -- Insert statements for procedure here
Set @queryString =
    'SELECT 
        CASE 
            WHEN d.sno IS NOT NULL THEN ''y''
            ELSE NULL 
        END 
        amendment_type, 
        u.user_login_id [User], 
        role_name [Role], 
        u.user_name [Name], 
        a.companyname + '':<br>('' + b.branch) [Agent/Branch], 
        u.last_login_pc + ''('' + u.ip_address + '')<br> Login Time: '' + cast(u.last_login AS varchar(50)) + '' [Last Login], 
        u.lock_status [Lock/Unlock], 
        CASE 
            WHEN u.lock_status = ''y'' THEN ''Locked - '' + u.lock_by
            WHEN datediff(d,u.last_login,getdate()) > u.lock_days AND (u.lock_days,0) > 0 THEN ''Locked - Day Exceed''
            WHEN u.active_session IS NULL AND isnull(u.lock_status,''n'') = ''n'' THEN ''Not Login''
            ELSE ''Active''
        END [Status], 
        u.*, 
        a.agentcan, 
        b.branch, 
        b.branchcodechar, 
        NULL branchcan
    FROM agentsub u 
    JOIN agentbranchdetail b ON u.agent_branch_code = b.agent_branch_code 
    JOIN agentdetail a ON b.agentcode = a.agentcode
    LEFT OUTER JOIN application_role_agent_user r ON u.user_login_id = r.user_id
    LEFT OUTER JOIN application_role p ON r.role_id = p.role_id
    LEFT OUTER JOIN agentsub_amendment d ON d.agent_user_id = u.agent_user_id
    WHERE 1 = 1 '

IF (@loginId !=null)
BEGIN
    SET @queryString = @queryString + 'and u.user_login_id like ('+ @loginId + ')'
END

SET @queryString = @queryString + ' order by u.user_login_id,a.companyname,b.branch'

EXEC sp_executesql @queryString

I am getting below error:

getting error on procedure execution. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'. Unclosed quotation mark after the character string ' END [Status], u.*, a.agentcan, b.branch, b.branchcodechar, NULL branchcan FROM agentsub u JOIN agentbranchdetail b ON u.agent_branch_code = b.agent_branch_code JOIN agentdetail a ON b.agentcode = a.agentcode LEFT OUTER JOIN application_role_agent_user r ON u.user_login_id = r.user_id — J_Shrestha, Oct 01 '19 at 06:48

Concatenate query string in procedure having case statement with parameter

2 Answers2