July 22, 2009 at 7:15 am
If I run the following statement:
Select sum(case I.IT_Code when 'SRIP' then 1 else 0 end) as SRIP,
sum(case I.IT_Code when 'TA' then 1 else 0 end) as TA,
sum(case I.IT_Code when 'Kicker' then 1 else 0 end) as Kicker,
sum(case I.IT_Code when 'SLRP' then 1 else 0 end) as Loan
FROM dbo.tblContracts C WITH(NOLOCK)
INNER JOIN dbo.tblSoldiers S WITH(NOLOCK) ON C.SSN = S.SSN
INNER JOIN dbo.tblSoldier_Status_Actions SA WITH(NOLOCK) ON S.SSN = SA.SSN And SA.Contract_ID = C.Contract_ID
INNER JOIN dbo.tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
INNER JOIN dbo.tLookup_SRIP_Programs P WITH(NOLOCK) ON P.BonusType_ID = C.Rate_Code
WHERE SA.ACTION_CODE='SOLD_000003'
AND C.ContractStatus = 'A' AND S.UnitState = 'CA'
I get :
36, 0, 0, 0
But I put the same statement in a stored procedure and run
Execute dbo.[p_MTop_Count_Losses_With_Active_Contracts] 'CA',
I get
Null, Null, Null, Null
My Stored procedure is:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[p_MTop_Count_Losses_With_Active_Contracts]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [p_MTop_Count_Losses_With_Active_Contracts]
GO
CREATE proc [dbo].[p_MTop_Count_Losses_With_Active_Contracts]
@UnitStateChar(2)
AS
Select sum(case I.IT_Code when 'SRIP' then 1 else 0 end) as SRIP,
sum(case I.IT_Code when 'TA' then 1 else 0 end) as TA,
sum(case I.IT_Code when 'Kicker' then 1 else 0 end) as Kicker,
sum(case I.IT_Code when 'SLRP' then 1 else 0 end) as Loan
FROM dbo.tblContracts C WITH(NOLOCK)
INNER JOIN dbo.tblSoldiers S WITH(NOLOCK) ON C.SSN = S.SSN
INNER JOIN dbo.tblSoldier_Status_Actions SA WITH(NOLOCK) ON S.SSN = SA.SSN And SA.Contract_ID = C.Contract_ID
INNER JOIN dbo.tLookup_Incentive I WITH(NOLOCK) ON I.IT_ID = C.IT_Code
INNER JOIN dbo.tLookup_SRIP_Programs P WITH(NOLOCK) ON P.BonusType_ID = C.Rate_Code
WHERE SA.ACTION_CODE='SOLD_000003'
AND C.ContractStatus = 'A' AND S.UnitState = '@UnitState'
What is the problem?
July 22, 2009 at 7:30 am
You need to remove the single quotes from the variable in your stored procedure.
Change this...
AND C.ContractStatus = 'A' AND S.UnitState = '@UnitState'
To this...
AND C.ContractStatus = 'A' AND S.UnitState = @UnitState
July 22, 2009 at 7:31 am
Thanks. I will try it.
July 22, 2009 at 7:39 am
It worked.
Thank you very much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply