How do you convert a stored procedure using dynamic SQL into a non-dynamic SQL one(SSRS)?

Question

I've been working on SSRS reports, and for one of my stored procedures , it uses dynamic SQL suchthat i'm getting this error:

An item with the same key has already been added.

So I've googled that error a bit, but it's still hazy to me..

My stored-procedure takes in a few parameters that are toggle-type, like the followin:

@CompleteType INT = NULL,
/*
 * 0 - Routed
 * 1 - Targeted
 * 2 - Offerwall
 */

@SourceType BIT = NULL,
/*
 * Works if @AccountID is not null
 * (should only be used if @AccountID has a single value)
 *
 * 0 - Owned by @AccountID
 * 1 - External (not owned by @AccountID)
 */

@SurveyStatus INT = NULL,
/*
 * NULL - All Surveys
 * 0 - Completes Approved Surveys
 * 1 - Invoiced Surveys
 */

@IsSupplierUser BIT = 0
/*
 * used to decide whether to display FEDSurveyName or SupplierSurveyName

The problem occurs in these two code blocks here(they're not contiguous):

-- Initial Survey Name
    IF @IsSupplierUser = 0
        SET @SQL += CHAR(13) + CHAR(9) + N' ,ts.FEDSurveyName as ''Initial Survey Name'''

    ELSE
        SET @SQL += CHAR(13) + CHAR(9) + N' ,ts.SupplierSurveyName as ''Initial Survey Name'''

then this one:

-- Complete Survey Name
IF @IsSupplierUser = 0
    SET @SQL += CHAR(13) + CHAR(9) + N' ,cs.FEDSurveyName as ''Complete Survey Name'''
ELSE
    SET @SQL += CHAR(13) + CHAR(9) + N' ,cs.SupplierSurveyName as ''Complete Survey Name'''

We have both a ts.FEDSurveyName and a cs.FEDSurveyName , and the same for SupplierSurveyName respectively. I appreciate any tips! thanks

Answer 1

DECLARE
@IsSupplierUser BIT = 0,
@SQL NVARCHAR(MAX) ='init'
-- Initial Survey Name
SELECT @SQL += CASE @IsSupplierUser
    WHEN 0 THEN CHAR(13) + CHAR(9) + N' ,ts.FEDSurveyName'
    ELSE CHAR(13) + CHAR(9) + N' ,ts.SupplierSurveyName'
END + N' as ''Initial Survey Name'''
SELECT @SQL 

http://www.sqlfiddle.com/#!3/d41d8/10397