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