Different resultsets between a script by A and a sp by B that has same context via A

Question

Post reply

Different resultsets between a script by A and a sp by B that has same context via A

Cem EFENDIOGLU

SSCrazy

Points: 2702
More actions
November 25, 2008 at 7:13 am

#128850

When run script A there is 2 rows in resultset but although sp B run by using same paremeters and same context, there is no results.
A.
DECLARE @customerID int,@campaignId varchar(300),@SDate datetime,@EDate datetime,@ActivePasive char(1)
DECLARE @Date VARCHAR(8)
SELECT @Date = CONVERT(VARCHAR(8), GETDATE(),112)
SELECT @customerID = 998, @campaignId = '13088', @ActivePasive='H'
SELECT * FROM (
SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY c.campaign_id DESC) Kayit_no,
c.ca_name Kampanya_Adi, c.campaign_id Kampanya_Numarasi, ra.agent_name Ajans_Adi, cu.customer_name Musteri_Adi,
ra.agent_id Ajans_Numarasi, cu.customer_id Musteri_Numarasi, c.default_date_start Baslangic_Tarihi,
c.default_date_end Bitis_Tarihi, SUM(ISNULL(ak_cita.events_scheduled, 0)) Satilan_Gosterim,
SUM(ISNULL(p.events_served, 0)) Gerceklesen_Gosterim, SUM(ISNULL(p.clicks_served, 0)) Tiklanma_Sayisi, ISNULL(uv.UV_Count, 0) AS Tekil_Gosterim,
CASE WHEN CONVERT(varchar(8),c.default_date_end,112) >= @Date AND CONVERT(varchar(8),c.default_date_start,112) <= @Date THEN 'A' ELSE 'P' END as CampaignStatus
FROM
ak_AdUVReport(NOLOCK) uv RIGHT OUTER JOIN
Report.ak_agent ra
INNER JOIN ak_campaign(NOLOCK) c ON ra.agent_id = c.agent_id
INNER JOIN ak_customer(NOLOCK) cu ON c.customer_id = cu.customer_id ON uv.UV_Id = c.campaign_id LEFT OUTER JOIN
ak_cita(NOLOCK) RIGHT OUTER JOIN
ak_campaignitem(NOLOCK) ci ON ak_cita.campaign_item_id = ci.campaign_item_id
LEFT OUTER JOIN
(SELECT target_id,campaign_item_id,Sum(events_served) events_served,Sum(clicks_served) clicks_served FROM ak_performance(NOLOCK)
WHERE (@SDate is null or performance_datetime>=@SDate) AND (@EDate is null or performance_datetime<=@EDate)
GROUP BY target_id,campaign_item_id) p ON ak_cita.target_id = p.target_id AND ak_cita.campaign_item_id = p.campaign_item_id ON c.campaign_id = ci.campaign_id
WHERE cu.customer_id = @customerID
AND (@campaignId='-1' OR c.campaign_id in (select Convert(int,Items) from dbo.SplitWithCols(@campaignId,',')))
AND ((@SDate is null or default_date_start>=@SDate) AND (@EDate is null or default_date_end<=@EDate) OR
(@SDate is null or default_date_end>=@SDate) AND (@EDate is null or default_date_start<=@EDate))
AND ((@SDate is null or ci.date_start>=@SDate) AND (@EDate is null or ci.date_end<=@EDate) OR
(@SDate is null or ci.date_end>=@SDate) AND (@EDate is null or ci.date_start<=@EDate))
GROUP BY c.campaign_id,c.ca_name,ra.agent_id,ra.agent_name,cu.customer_id,cu.customer_name,uv.UV_Count,c.default_date_start,c.default_date_end,c.date_modified
ORDER BY c.date_modified DESC
) DerTab where (@ActivePasive='H' OR DerTab.CampaignStatus =@ActivePasive)
B.
ALTER PROCEDURE [Report].[CustomerBasicReport](@customerID int,@campaignId varchar(300)='-1',@SDate datetime=null,@EDate datetime=null,@ActivePasive char(1)='H')
as
--exec Report.CustomerBasicReport 998, '13088', null, null, 'H'
DECLARE @Date VARCHAR(8)
SELECT @Date = CONVERT(VARCHAR(8), GETDATE(),112)
SELECT * FROM (
SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY c.campaign_id DESC) Kayit_no,
c.ca_name Kampanya_Adi, c.campaign_id Kampanya_Numarasi, ra.agent_name Ajans_Adi, cu.customer_name Musteri_Adi,
ra.agent_id Ajans_Numarasi, cu.customer_id Musteri_Numarasi, c.default_date_start Baslangic_Tarihi,
c.default_date_end Bitis_Tarihi, SUM(ISNULL(ak_cita.events_scheduled, 0)) Satilan_Gosterim,
SUM(ISNULL(p.events_served, 0)) Gerceklesen_Gosterim, SUM(ISNULL(p.clicks_served, 0)) Tiklanma_Sayisi, ISNULL(uv.UV_Count, 0) AS Tekil_Gosterim,
CASE WHEN CONVERT(varchar(8),c.default_date_end,112) >= @Date AND CONVERT(varchar(8),c.default_date_start,112) <= @Date THEN 'A' ELSE 'P' END as CampaignStatus
FROM
ak_AdUVReport(NOLOCK) uv RIGHT OUTER JOIN Report.ak_agent ra INNER JOIN
ak_campaign(NOLOCK) c ON ra.agent_id = c.agent_id INNER JOIN
ak_customer(NOLOCK) cu ON c.customer_id = cu.customer_id ON uv.UV_Id = c.campaign_id LEFT OUTER JOIN
ak_cita(NOLOCK) RIGHT OUTER JOIN
ak_campaignitem(NOLOCK) ci ON ak_cita.campaign_item_id = ci.campaign_item_id LEFT OUTER JOIN
(SELECT target_id,campaign_item_id,Sum(events_served) events_served,Sum(clicks_served) clicks_served FROM ak_performance(NOLOCK)
WHERE (@SDate is null or performance_datetime>=@SDate) AND (@EDate is null or performance_datetime<=@EDate)
GROUP BY target_id,campaign_item_id) p ON ak_cita.target_id = p.target_id AND ak_cita.campaign_item_id = p.campaign_item_id ON c.campaign_id = ci.campaign_id
WHERE cu.customer_id = @customerID
AND (@campaignId='-1' OR c.campaign_id in (select Convert(int,Items) from dbo.SplitWithCols(@campaignId,',')))
AND ((@SDate is null or default_date_start>=@SDate) AND (@EDate is null or default_date_end<=@EDate) OR
(@SDate is null or default_date_end>=@SDate) AND (@EDate is null or default_date_start<=@EDate))
AND ((@SDate is null or ci.date_start>=@SDate) AND (@EDate is null or ci.date_end<=@EDate) OR
(@SDate is null or ci.date_end>=@SDate) AND (@EDate is null or ci.date_start<=@EDate))
GROUP BY c.campaign_id,c.ca_name,ra.agent_id,ra.agent_name,cu.customer_id,cu.customer_name,uv.UV_Count,c.default_date_start,c.default_date_end,c.date_modified
ORDER BY c.date_modified DESC
) DerTab where (@ActivePasive='H' OR DerTab.CampaignStatus =@ActivePasive)
--exec Report.CustomerBasicReport 998, '13088', null, null, 'H'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

Do you get any values when you run

exec Report.CustomerBasicReport 998,'-1', null, null, 'H'

Alex S

Cem EFENDIOGLU SSCrazy Points: 2702 More actions · Answer 2

No, but I solved the problem. due to Report schema there is a securty right error.

when i created sp B by owner dbo. Script and sp have same resulsets.