Stored Procedure Syntax Error

0 pts.
Tags:
Desktops
Management
Microsoft Windows
OS
Security
Servers
SQL Server
I'm trying to write a sp that first checks to see if there is any data with one criteria and if not then use a second criteria and return the data. I get a syntax error on the If Exist(SELECT StateRateID .... line. -- *********************************************************** -- SELECT Stored Procedure for L_StateRate table. -- *********************************************************** GO if exists (select * from dbo.sysobjects where id = object_id(N'dbo.sp_L_StateRate_GetRate') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.sp_L_StateRate_GetRate GO -- 2006-07-27 VERSION: 0.1 -- Description: Returns the loan parameters from the StateRate table -- State is the statecode -- Ratecategory is 0 for Personal 1 for Commercial -- RatePolicyType is the optional and determines the policy type -- If a rate with PolicyType is not found then PolicyType is ignored CREATE PROCEDURE dbo.sp_L_StateRate_GetRate @State varchar(2), @Category int = 0, @PolicyType int, @AmtFinanced money = 0 AS SET NOCOUNT ON If Exist(SELECT StateRateID FROM L_StateRate WHERE RateState = @State And RateCatecory = @Category And RatePolicyType = @PolicyType And @AmtFinanced Between RateAFLow and RateAFHigh) Begin SELECT RateMinRate, RateMaxRate, RateDefauktRate, RateInterestCalc, RateAdjustNonStdDate, RateInterestEarn, RateChartCode, RateAgencyRebate, RateAgencyRebateRate, RateAgencyAddon, RateAgencyCalc FROM L_LStateRate WHERE RateState = @State And RateCatecory = @Category And RatePolicyType = @PolicyType And @AmtFinanced Between RateAFLow and RateAFHigh End Else Begin SELECT RateMinRate, RateMaxRate, RateDefauktRate, RateInterestCalc, RateAdjustNonStdDate, RateInterestEarn, RateChartCode, RateAgencyRebate, RateAgencyRebateRate, RateAgencyAddon, RateAgencyCalc FROM L_LStateRate WHERE RateState = @State And RateCatecory = @Category And @AmtFinanced Between RateAFLow and RateAFHigh End GO
ASKED: August 1, 2006  12:43 PM
UPDATED: August 1, 2006  1:56 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Try this:
.
IF (SELECT Count(StateRateID)
FROM L_StateRate WHERE
RateState = @State And RateCatecory = @Category And RatePolicyType = @PolicyType And
@AmtFinanced Between RateAFLow and RateAFHigh) > 0
– if there any records that match
Begin
SELECT
RateMinRate,
RateMaxRate,
RateDefauktRate,
RateInterestCalc,
RateAdjustNonStdDate,
RateInterestEarn,
RateChartCode,
RateAgencyRebate,
RateAgencyRebateRate,
RateAgencyAddon,
RateAgencyCalc

FROM
L_LStateRate

WHERE
RateState = @State And RateCatecory = @Category And RatePolicyType = @PolicyType And
@AmtFinanced Between RateAFLow and RateAFHigh

End
Else Begin
– if there no records that match
SELECT
RateMinRate,
RateMaxRate,
RateDefauktRate,
RateInterestCalc,
RateAdjustNonStdDate,
RateInterestEarn,
RateChartCode,
RateAgencyRebate,
RateAgencyRebateRate,
RateAgencyAddon,
RateAgencyCalc

FROM
L_LStateRate

WHERE
RateState = @State And RateCatecory = @Category And
@AmtFinanced Between RateAFLow and RateAFHigh
End

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Jrstrang
    Thanks. That got me going in the right direction. I also had to turn the SELECT in to a boolean response (see >0) such as: After I correct my typos it works great.... If (SELECT Count(RateID) FROM dbo.L_StateRate WHERE RateState = @State And RateCategory = @Category And RatePolicyType = @PolicyType And @AmtFinanced Between RateAFLow and RateAFHigh) >0
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following