August 31, 2015 at 2:55 pm
Hi,
I am trying to write a procedure where either the user could type the loan number and the name of the dealership comes up or they type some part of the dealership name and all the loannumber come up. Since there are some null values under the loanNumber column that is what I believe messing up, can somebody review the following below procedure and see how can we make it work
ALTER PROCEDURE USP_Loan_Dealership_Reference
(
@LoanNumber AS INT = NULL
,@DealershipName AS NVARCHAR(50) = NULL
)
AS
BEGIN
SELECT
RIGHT (C.loannumber,7) AS [Loan Number]
,CH.OldDealerID AS [Old Dealer Id]
,CH.NameLong AS [Full Dealership Name]
,CH.MailingStreet AS [Mailing Street]
,CH.MailingCity AS [Mailing City]
,CH.MailingZip AS [Zip Code]
FROM contracts AS C
INNER JOIN Channels AS CH
ON CH.channelid = C.branchid
WHERE
C.loannumber IS NULL OR C.loannumber = @LoanNumber
AND CH.NameLong IS NULL OR CH.NameLong LIKE '%' + @DealershipName + '%'
Order by [Full Dealership Name]
END
EXEC USP_Loan_Dealership_Reference @DealershipName = 'Dream'
August 31, 2015 at 3:06 pm
Are you looking for something more like this in the where clause?
WHERE
(@LoanNumber IS NOT NULL AND C.loannumber = @LoanNumber) OR
(@LoanNumber IS NULL AND @DealershipName IS NOT NULL AND CH.NameLong LIKE '%' + @DealershipName + '%')
August 31, 2015 at 3:21 pm
I am looking where user either enters the loan number and gets the name of the dealership or enters the name of the dealership and gets all the loans associated to it. Thanks
August 31, 2015 at 3:38 pm
Did you try that where clause?
August 31, 2015 at 3:40 pm
Personally I'd use an IF statement:
IF @LoanNumber IS NULL AND @DealershipName IS NULL
BEGIN
PRINT 'Both @LoanNumber and @DealershipName cannot be Null, a value must be entered for one of them.'
END
IF @LoanNumber IS NOT NULL
BEGIN
<SELECT statement to return dealership info>
END
ELSE IF @DealershipName IS NOT NULL
BEGIN
<SELECT statement to return all the loan numbers for the dealership>
END
-SQLBill
August 31, 2015 at 4:03 pm
IT works Awsum!!
September 1, 2015 at 2:59 am
Before you implement that, please read this: http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply