sql query problem when reporting

Question

I have this sql query that it's all working fine but i want to parse the 'where part' using a parameter (@ged) when i try this in sql it work fine..but i can't get it working in crystal report or sql reporting service in this query ..@J_Sec is a parameter and @ged is the rest of the where statment

CREATE proc [dbo].[con]
@J_Sec as nvarchar(255),
@ged as varchar(max) 
as
declare @sql as varchar(max)

set @sql='
select 
(case when c.Age_18_24=1 then ''18-24'' when c.Age_25_29=1 then ''25-29'' when c.Age_30_39=1 then ''30-39'' 
 when c.Age_40_Above=1 then ''40-above'' else null  end)AS "Age",c.status 

from consumer c 
inner join dbo.Journey j on c.JOURNEY_SEQUENCE=j.JOURNEY_SEQUENCE
inner join Teams t on j.Team_id=t.Team_id where c.journey_sequence= '+@J_Sec+' and '+@ged;

exec(@sql)
go

Answer 1

You may find that Crystal Reports sniffs the SP for it's output signature. As it doesn't acutally have one (as it's hidden inside the @sql string), Crystal isn't showing anything.

That, however, is a guess, as I haven't used Crystal in many years. You can test it by binding to this SP and checking to see if you ever get any results...

CREATE PROCEDURE [dbo].[test] @mode AS INT
AS

DECLARE
  @sql AS VARCHAR(MAX)

IF (@mode = 1)
  SET @sql = 'SELECT ''This is mode one'' AS message'
ELSE
  SET @sql = 'SELECT ''You may only specify mode one'' AS error'

EXEC(@sql)

GO

If you're getting nothing from this, you appear to have little choice but to refactor your design in a more traditional way; Treating SP's like you would a method in an Object - An input signature taking values, not code, and having a fixed output signature.

Answer 2

If your @ged parameter equals what it says in your comment, then why not abandon the sql string approach and use:

EDIT for extra parameters

CREATE proc [dbo].[con]
@J_Sec as nvarchar(255),
@male int,
@age_18_24 int,
@student int,
@main_lmg int,
@main_Price int,
@alt_lmg int,
@alt_price int,
@source_ka INT

as

select 
    (case 
        when c.Age_18_24=1 then '18-24' 
        when c.Age_25_29=1 then '25-29'
        when c.Age_30_39=1 then '30-39' 
        when c.Age_40_Above=1 then '40-above' 
        else null  
    end)
    AS "Age"
    , c.status 

from consumer c 
    inner join dbo.Journey j on c.JOURNEY_SEQUENCE = j.JOURNEY_SEQUENCE
    inner join Teams t on j.Team_id = t.Team_id 

where 
    c.journey_sequence= @J_Sec 
    and male != @male 
    and Age_18_24 != @age_18_24 
    and Student != @student 
    and Main_LMG != @main_lmg 
    and Main_Price != @main_Price 
    and ALT_LMG != @alt_lmg
    and ALT_Price != @alt_price 
    and Source_Ka != @source_ka

go

Alternatively you could try using exec sp_executesql.