Custom sort error

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

Break up your order by clause into multiple CASE expressions like this:

ORDER BY 
	CASE WHEN ISNULL(@IsDesc,'ASC') = 'ASC' AND @ColumnNumber = 1 THEN jobnumber END,
	CASE WHEN ISNULL(@IsDesc,'ASC') = 'ASC' AND @ColumnNumber = 5 THEN customername END,
...
1 Like

Thanks JamesK - worked great
I would like to try and understand why the error occurs only sometimes when I attempted my version of the custom sort

Could I also ask-using my example I have a heap of parameters to use in my where clause
is it possible to supply the where clause as a big string,then concatenate it with the main body of the proceure.That way I can build the where clause in my front end application and would only require 1 parameter,and the query would never have to check for null parameter values

You might want to look into table values parameters if your parameters are getting too big

The amount of parameters is always approx. 12 in any search form on the front end
users typically would only pick 3 or 4 data filters at any one time to view the resultant query
records being searched in most tables are max 250,000 rows,with lots of related table joins
Access back end had been working ok for 10yrs-until m/soft and windows10 updates broke Acess to Access connections,and its been 10 months and still waiting for the fix
So I am looking for
1.the most practical way - that's not to complicated to implement SQL from my front end application
2.it needs to be as efficient as possible-again without to much complication
I intend to just use ADO connections for most of the front end functions,but think search forms will benefit from passing it to SQL server to do the processing
Over time as I learn and understand SQL better I can gradually migrate tasks to SQL
that's my plan anyway
I am certainly open to suggestions

A case expression can return only one data type. If the various clauses in the case expression return different data types, then SQL Server implicitly tries to convert those to the data type with the highest precedence. If it is unable to convert, then you get the type of error message that you saw complaining about failure when trying to convert.

See here for details on implicit conversion

Concatenation of one long parameter is doable but not a good idea.
In your where clause just do this

Where (@parm1 is null or column_name = @parm1)

Etc for 12 parms.

Is it not a good idea because it exposes it to SQL injection ?

that is one very important danger. the best I have used is User-defined Table Type

-- Create the data type
CREATE TYPE <schema_name,sysname,dbo>.<type_name,sysname,TVP> AS TABLE 
(
	<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, 
	<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, 
	<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, 
    PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

That's way above my knowledge at this stage
You create a table based on the query ?
then view that table data ?

:slight_smile: I would encourage you not to fear young jedi. let go of fear you must .
It is super powerful, will make your life a living paradise! basically it is a type of table you can refer from your ADO or .net code. And it sorts of becomes a means of vehicle for your parameters to go from your application to the back end.
we are here to assist you

:sunglasses: - I haven't been to referred to as young for quite some time
time is what I fear when it comes to this stuff - it needs to get operational ASAP
so where to start ?
it seems you are creating a variable temp table ? using what data ?
bear in mind-I am new to this-so unless I can relate to your examples ,I will struggle to get there
I have no formal qualifications-just school of hard knocks

Young at heart. Let me down this beer and get back to you ASAP

would you be ok with using a special kind of function that splits your "array" of parameters?

I am open to whatever is good practice ,works well and is effient over a network-providing I can follow it
I figure once given the right direction on 1 search form - the rest will get easier
at the moment I use a pass through query in Access to talk to Sql-it can be an array of anything
I have one search form that works great-I pass a commer delimited array to a stored proc
its not a complex search form,but it works quite well -the recordset is maybe 1500 records
it may be open to sql injection,i am not sure,i use a long password as the first element of the array,and test that in the stored procedure at the beginning-if it doesn't match the proc ends (I think)

good practice what you currently have. another way you could do it is TVP but with the time constraint you have that might not be the route to go. I will go ahead and demo that to you. It makes your code a bit cleaner to look at as well just passing one tvp parameter, it is as the name suggests a table valued parameter. instead of individual parameters you have one table like array you pass as one clean parameter.

--1 create table type
CREATE TYPE JobScheduleType AS TABLE ( 
 JobNumber VARCHAR(20) ,
Customername VARCHAR(100),
OrderNumber VARCHAR(100),
CustomerId INT ,
RoomTypeId INT,
JobTypeId INT ,
FinishTypeId INT,
painterid INT,
JobStatus INT,
startdate date,
enddate date ,
IsDesc VARCHAR(5) ,
ColumnNumber INT 
)
GO

ALTER PROCEDURE dbo.Search_Job_Schedule_SP
@jobsheduletype JobScheduleType READONLY
AS
--rest of your proc code
--here you probably would left join to this @jobsheduletype  as if it was a
--table which it is 
inner join dbo.staff on  ON stored_jobs.user_id = staff.staffid 
left join @jobsheduletype jsp on stored_jobs.jobnumber = jsp.jobnumber 
left join @jobsheduletype jsp on stored_customers.customername = jsp.customername

--etc


--This is from your application side, .net or vbnet
you declare the tvp and populate it from your application
--declare @jobsheduletype JobScheduleType
--stuff data into it and call procedure as follows

EXEC dbo.Search_Job_Schedule_SP @jobsheduletype 
GO

So a bit complex indeed but doable? That is why I Am saying what you have is good.

well,best way I guess is dive in,so I copied your code and pasted into my stored proc
I commented out lines inner join,left join for the moment
I commented out the line you declare the type for the moment
when I execute I get error
"The type 'JobScheduleType' already exists, or you do not have permission to create it."
I am logged in as admin so it should not be a permission thing

Edit
ok - so you only create the table once and its stored under Types in the object tree
Would that table data get emptied each time I call my stored proc

1 Like

just trying to grasp this-its an interesting concept
it appears that by joining existing SQL tables to the new table using the passed parameters,thats what filters the data returned as there will only be matching records in both tables.How do you handle null values ?
Also,what / where does jsp come from-is that a declared parameter

Edit
I take it back - after more reading I am confused-so we may need to take this one step at a time
if that's ok
I think I will use my way(well its not my way but the way that's working at the moment) but learn your way with your help-if you don't mind being a teacher:slightly_smiling_face:
at least that will take the pressure of getting this operational

Sure we are here to help. Once you fully implement it of course it will make total sense. It is just a means of passing parameter in the form of a table. Let me know what help you need