My 1st post here and very new to sql
I am using an access 2010 front end and SQL backend application (access because this is what I know)
trying to get a pass through query on a search form to work
don't quite understand the error I get in the stored procedure-which works until I add an order by statement-and even that works until I add a date field that needs sorting
"Conversion failed when converting date and/or time from character string."
I don't know where to start looking as to why I get this error-any directions would be appreciated
USE [CVDATA_SQL_BE]
GO
/****** Object: StoredProcedure [dbo].[Search_Job_Schedule_SP] Script Date: 23/04/2019 8:39:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[Search_Job_Schedule_SP]
-- Add the parameters for the stored procedure here
@JobNumber VARCHAR(20) = NULL,
@Customername VARCHAR(100) = NULL,
@OrderNumber VARCHAR(100) = NULL,
@CustomerId INT = NULL,
@RoomTypeId INT = NULL,
@JobTypeId INT = NULL,
@FinishTypeId INT = NULL,
@painterid INT = NULL,
@JobStatus INT = NULL,
@startdate date = null,
@enddate date = null,
@IsDesc VARCHAR(5) = NULL,
@ColumnNumber INT = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET nocount ON;
-- Insert statements for procedure here
SELECT dbo.stored_jobs.job_id,
dbo.stored_jobs.jobnumber,
dbo.customers.customername,
dbo.stored_jobs.customer_order_number,
dbo.stored_jobs.order_id,
dbo.job_types_local.jobtype_id,
dbo.stored_jobs.job_notes,
dbo.stored_jobs.default_jobcolour AS DefaultColour,
dbo.job_finishes_local.finish_type,
dbo.job_types_local.job_type,
Iif([default_painter_id] = -1, 'N/a', [paint_supplier]) AS Painter,
Dbo.stored_jobs.order_created,
Dbo.stored_jobs.to_production,
Dbo.stored_jobs.to_painter,
Dbo.stored_jobs.back_painter,
Dbo.stored_jobs.ready_to_deliver,
Dbo.stored_jobs.required_by,
dbo.room_types.room,
dbo.staff.initials,
dbo.stored_jobs.rated_cab_qty,
dbo.stored_jobs.schedule_note,
dbo.stored_jobs.job_status,
Dbo.stored_jobs.delivered_date
FROM ((paint_suppliers RIGHT JOIN (job_types_local INNER JOIN (job_finishes_local INNER JOIN (customers INNER JOIN (orders INNER JOIN stored_jobs
ON orders.id = stored_jobs.order_id)
ON customers.row_id = stored_jobs.customer_id)
ON job_finishes_local.finishtype_id = stored_jobs.job_colour_type)
ON job_types_local.jobtype_id = stored_jobs.job_type)
ON paint_suppliers.paint_supplier_id = stored_jobs.default_painter_id)
INNER JOIN room_types
ON stored_jobs.room_id = room_types.room_id)
INNER JOIN staff
ON stored_jobs.user_id = staff.staffid
WHERE ( ( ( stored_jobs.order_id ) IS NOT NULL ) )
AND stored_jobs.jobnumber = ISNULL(@JobNumber , stored_jobs.jobnumber)
AND customers.customername LIKE ISNULL(@Customername , customers.customername) + '%'
AND stored_jobs.customer_order_number LIKE ISNULL(@OrderNumber ,stored_jobs.customer_order_number) + '%'
AND Room_Types.Room_ID = ISNULL(@RoomTypeId , Room_Types.Room_ID)
AND job_types_local.jobtype_id = ISNULL(@JobTypeId ,job_types_local.jobtype_id)
AND job_finishes_local.FinishType_ID = ISNULL(@FinishTypeId , job_finishes_local.FinishType_ID)
ORDER BY CASE isnull(@IsDesc, 'ASC')
WHEN 'ASC' THEN CASE @ColumnNumber
WHEN 1 THEN jobnumber
WHEN 5 THEN customername
WHEN 7 THEN customer_order_number
WHEN 11 THEN stored_jobs.default_jobcolour
WHEN 13 THEN finish_type
WHEN 15 THEN job_types_local.job_type
WHEN 17 THEN Iif([default_painter_id] = -1, 'N/a', [paint_supplier])
WHEN 19 THEN dbo.stored_jobs.order_created
END ELSE NULL
END ASC,
CASE @IsDesc
WHEN 'DESC' THEN CASE @ColumnNumber
WHEN 1 THEN jobnumber
WHEN 5 THEN customername
WHEN 7 THEN customer_order_number
WHEN 11 THEN stored_jobs.default_jobcolour
WHEN 13 THEN finish_type
WHEN 15 THEN job_types_local.job_type
WHEN 17 THEN Iif([default_painter_id] = -1, 'N/a', [paint_supplier])
WHEN 19 THEN dbo.stored_jobs.order_created
END ELSE NULL
END DESC;
--WHEN 5 THEN customername
-- WHEN 19 THEN dbo.stored_jobs.order_created
-- WHEN 21 THEN Dbo.stored_jobs.to_production
-- WHEN 23 THEN Dbo.stored_jobs.to_painter
-- WHEN 25 THEN back_painter
-- WHEN 31 THEN room
-- WHEN 33 THEN rated_cab_qty
-- WHEN 7 THEN customer_order_number
-- WHEN 11 THEN stored_jobs.default_jobcolour
-- WHEN 13 THEN finish_type
-- WHEN 15 THEN job_types_local.job_type
END
I would encourage you not to fear young jedi. let go of fear you must .
- I haven't been to referred to as young for quite some time