I am in the process of writing stored procedures for MS SQL Server 2000 that essentially just need to select rows from a certain table, matching and sorting by various fields. I know a few tricks that supposedly should be able to allow me to do all this in one stored procedure, controlling its behavior by passing nulls for the various arguments, but I'm having some problems.
(All fields on the table are fixed-length CHAR fields, except for the ones with "Date" in the name, which are DATETIME.)
Code:
CREATE PROCEDURE dbo.LeadDetail_OrderedSelectBy
(
@OrderBy INT = NULL,
@Serial CHAR(9) = NULL,
@ShipDate DATETIME = NULL,
@SalesOrder CHAR(6) = NULL
)
AS
DECLARE @SerialMask AS VARCHAR(11)
DECLARE @SalesOrderMask AS VARCHAR(8)
SET @SerialMask = @Serial
SET @SalesOrderMask = @SalesOrder
IF (@SerialMask = '')
SET @SerialMask = NULL
ELSE IF (@SerialMask IS NOT NULL)
SET @SerialMask = '%' + RTRIM(LTRIM(@SerialMask)) + '%'
IF (@ShipDate = '')
SET @ShipDate = NULL
IF (@SalesOrderMask = '')
SET @SalesOrderMask = NULL
ELSE IF (@SalesOrderMask IS NOT NULL)
SET @SalesOrderMask = '%' +RTRIM(LTRIM(@SalesOrder)) + '%'
SELECT *
FROM RNew.dbo.LeadDetail
WHERE Serial LIKE COALESCE(@SerialMask, Serial) AND
ShipDate = COALESCE(@ShipDate, ShipDate) AND
SalesOrder LIKE COALESCE(@SalesOrderMask, SalesOrder)
ORDER BY
CASE WHEN @OrderBy = 1 THEN PC
WHEN @OrderBy = 2 THEN SalesOrder
WHEN @OrderBy = 3 THEN Office
WHEN @OrderBy = 4 THEN OfficeName
WHEN @OrderBy = 5 THEN JobName
WHEN @OrderBy = 6 THEN City
WHEN @OrderBy = 7 THEN State
WHEN @OrderBy = 8 THEN Model
WHEN @OrderBy = 9 THEN Serial
WHEN @OrderBy = 10 THEN CONVERT(VARCHAR(8), ShipDate)
WHEN @OrderBy = 11 THEN CONVERT(VARCHAR(8), StartupDate)
END
The @CodeBy argument decides which field to sort by. That works. The others, if null, have no effect. If the char arguments have values, they should restrict the results on the select, because of the LIKE matches. But I'm getting no results. For example if I pass in 'U' for @Serial, and null for the other two, and there's a serial field that is 'U92GHG', I should get that to match, but I get nothing.
My first question is, why do I have to convert my datetime fields to varchars for the dynamic sorting to work? If I don't convert, I get an error about out-of-range datetime values, that implies it's trying to convert the char fields in the table to datetimes for some reason.
Secondly, the LIKE statements aren't working. By examining the action in Query Analyzer I can see that the values are what I want them to be going into the select, but the LIKE doesn't seem to be recognizing the % signs when they come out of the COALESCE.
Any help would be
greatly appreciated.
ASP Free Forums Sponsor:
September 9th, 2004, 03:03 PM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
