CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
Caffeinated
Caffeinated

Reputation: 12484

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

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

Upvotes: 3

Views: 863

Answers (2)

revoua
revoua

Reputation: 2059

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

Upvotes: 4

AaronLS
AaronLS

Reputation: 38367

I assume these snippets are for generating your select list. To convert this to non-dynamic SQL:

SELECT
  CASE 
    When @IsSupplierUser = 0 Then ts.FEDSurveyName 
    Else ts.SupplierSurveyName 
  END as 'Initial Survey Name',
  ts.SomeOtherColumn,
  etc.
  ...
FROM ...

Of course "etc." just indicates you continuing with other columns, you can have another case statement for the other conditional column you showed.

Upvotes: 1

Related Questions