Sql Query Problem When Reporting

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 cr

Solution 1:

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),
@maleint,
@age_18_24 int,
@studentint,
@main_lmg int,
@main_Price int,
@alt_lmg int,
@alt_price int,
@source_ka INTasselect 
    (casewhen c.Age_18_24=1then'18-24'when c.Age_25_29=1then'25-29'when c.Age_30_39=1then'30-39'when c.Age_40_Above=1then'40-above'elsenullend)
    AS "Age"
    , c.status 

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

where 
    c.journey_sequence=@J_Sec 
    and male !=@maleand Age_18_24 !=@age_18_24 
    and Student !=@studentand 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.

Solution 2:

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...

CREATEPROCEDURE [dbo].[test] @modeASINTASDECLARE@sqlASVARCHAR(MAX)

IF (@mode=1)
  SET@sql='SELECT ''This is mode one'' AS message'ELSESET@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.