[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

Passing value to a stored procedure

I am trying to build the drop of cars by passing the car number to a stored procedure. It works fine for the individual cars, but by default if no car selected I would like to show all car by passing the list of cars to the stored procedure but getting no results.

<script>
				  $(function() {
					 	$("#cars").change(function(){


					 		//alert('Hello');
					 		if ($(this).val().length == 0){
					 			var carKey = '<cfoutput>#ValueList(qGetCars.carKey)#</cfoutput>';
					 		}else{
					 			var carKey =$(this).val();
					 		}
				           $.get('/includes/car.cfc?method=getCarForServise&carKey=' + carKey, function (result) {
				           		$("#carAlert").html(result);
				           });
				        }).change();
				          })
	        	</script>

Open in new window


 <cffunction name="getCarForServise"
        access="remote"
        output="true"
        returnformat="plain"
        displayname="Get Task description"
        description="Return the list of tasks.">

       <cfargument name="carKey"
            type="string"
            required="no"
            displayname="owner of the task"
            default=""
            hint="The ID of the owner,or assined to the task" />

         <cfset var outputCars = "">

         <cfstoredproc procedure="sp_carReminders" datasource="#request.datasource#">
         	<cfif StructKeyExists(arguments,"carKey") and len(arguments.carkey)>
         		<cfprocparam cfsqltype="CF_SQL_integer" value="#arguments.carkey#" >
         	</cfif>

        	<cfprocresult name="qGetCarsAlert" >
    	</cfstoredproc>


         <cfsavecontent variable="outputCars" >
         
         	<cfif qGetCarsAlert.recordcount>
			<cfloop query="qGetCarsAlert" group="activityID">
	   	    	<ul><b>#activityName#</b>
	   	    	<cfloop>
	   	    	   <li>
	   	    	   	#carNumber# - #make# - #model# -#modelYear# <cfif len(dueOnDate)>Due Date: #DateFormat(dueOnDate,"mm/dd/yyyy")#<cfelse><cfif len(milageDue)>Due: #milageDue# miles</cfif> &nbsp;</cfif>
	   	    	    <b>Location:</b> &nbsp;#locationName#  &nbsp;<cfif not len(dueDate)><span class="history" data-car="#carKey#">[History]</span></cfif>
	   	    	   </li>
	   	    	</cfloop>
	   	    	</ul>
	   	    </cfloop>
	   	    <cfelse>
	   	    	NO RECORDS FOUND
	   	    </cfif>
         </cfsavecontent>
				<cfreturn outputCars>

	 </cffunction>

Open in new window


Stored Procedure
ALTER PROCEDURE [dbo].[sp_carReminders] (
	@carKey INT  = null
) AS

BEGIN

SELECT dbo.fn_getReminderDate (ca.carKey,ca.activityID) as dueOnDate,L.name as locationName,
isNUll(C.oilChangeMilage,0) + isNULL(CA.repeatNumber,0) as milageDue,C.carnumber,C.make,C.model,C.modelyear,CA.activityID,LCA.activityName,
(case when CA.remindCycle = 1 THEN DATEadd(DAY,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID)) 
						   when CA.remindCycle = 2 THEN DATEadd(WEEK,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
						   when CA.remindCycle = 3 THEN DATEadd(MONTH,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
						   when CA.remindCycle = 4 THEN DATEadd(YEAR,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
						   END) as dueDate,
						   CA.carKey
  FROM Car C
	 inner join carActivities CA on ca.carKey = C.carKey
	 left outer join Location L on L.locationKey = C.locationkey
	 inner join lkup_carActivity LCA on LCA.activityID = CA.activityID  
where (dbo.fn_getReminderDate (ca.carKey,ca.activityID) <= getDate() 
	 
	 OR (
	 CA.activityID = 1 AND 
		isNUll(C.oilChangeMilage,0)-isNUll(Ca.remindNumber,0) + isNULL(CA.repeatNumber,0) <= (
		
		
			SELECT top 1 CD.endmileage 
			from car_details CD 
			Inner join sessionUnit SU on SU.sessionKey = CD.sessionkey
			WHERE CD.carKey = C.carKey
			ORDER BY SU.sessionStart desc
		)
	 
	 ))
	 
	
		AND (@carKey is  null OR CA.carKEy in (@carKey))
	
GROUP BY CA.activityID,CA.carKEy,L.name,C.oilChangeMilage,CA.remindNumber,CA.repeatNumber,C.carnumber,C.make,C.model,C.modelyear,LCA.activityName,CA.remindCycle
OPTION (RECOMPILE)

Open in new window

0
erikTsomik
Asked:
erikTsomik
1 Solution
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
You may want to use Table Valued Parameters. It will allow you to pass a "table" of values (CarId) and then join to that input table in all your queries.

I have written about these in the past, which you can refer:
- https://nakulvachhrajani.com/2011/02/21/table-valued-parameters-to-a-table-valued-function-underappreciated-features-of-microsoft-sql-server/
- https://nakulvachhrajani.com/2013/04/04/0254-sql-server-2000-alternate-to-table-valued-parameters-share-temporary-tables-with-a-stored-procedure/
0
 
Chris LuttrellSenior Database ArchitectCommented:
Are you wanting to pass in a list of keys? Or just either one key or null so you get either one result or all cars in the results?  
It must be something else in your query and data that causes that because I created this test setup just to test your logic for passing in a NULL and it works as expected:
CREATE PROCEDURE [dbo].[spTestingcarReminders] (
	@carKey INT  = null
) AS

BEGIN
CREATE TABLE #CarActivities (carKey INT, activityID INT);
INSERT INTO #CarActivities ( carKey, activityID )
VALUES  (100, 1),(200, 0),(300, 1);
SELECT *
FROM #CarActivities CA
WHERE CA.activityID = 1 
AND (@carKey is  null OR CA.carKEy in (@carKey));
--SELECT dbo.fn_getReminderDate (ca.carKey,ca.activityID) as dueOnDate,L.name as locationName,
--isNUll(C.oilChangeMilage,0) + isNULL(CA.repeatNumber,0) as milageDue,C.carnumber,C.make,C.model,C.modelyear,CA.activityID,LCA.activityName,
--(case when CA.remindCycle = 1 THEN DATEadd(DAY,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID)) 
--						   when CA.remindCycle = 2 THEN DATEadd(WEEK,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
--						   when CA.remindCycle = 3 THEN DATEadd(MONTH,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
--						   when CA.remindCycle = 4 THEN DATEadd(YEAR,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
--						   END) as dueDate,
--						   CA.carKey
--  FROM Car C
--	 inner join carActivities CA on ca.carKey = C.carKey
--	 left outer join Location L on L.locationKey = C.locationkey
--	 inner join lkup_carActivity LCA on LCA.activityID = CA.activityID  
--where (dbo.fn_getReminderDate (ca.carKey,ca.activityID) <= getDate() 
	 
--	 OR (
--	 CA.activityID = 1 AND 
--		isNUll(C.oilChangeMilage,0)-isNUll(Ca.remindNumber,0) + isNULL(CA.repeatNumber,0) <= (
		
		
--			SELECT top 1 CD.endmileage 
--			from car_details CD 
--			Inner join sessionUnit SU on SU.sessionKey = CD.sessionkey
--			WHERE CD.carKey = C.carKey
--			ORDER BY SU.sessionStart desc
--		)
	 
--	 ))
	 
	
--		AND (@carKey is  null OR CA.carKEy in (@carKey))
	
--GROUP BY CA.activityID,CA.carKEy,L.name,C.oilChangeMilage,CA.remindNumber,CA.repeatNumber,C.carnumber,C.make,C.model,C.modelyear,LCA.activityName,CA.remindCycle
--OPTION (RECOMPILE)
END
GO

EXEC dbo.spTestingcarReminders @carKey = NULL -- int
EXEC dbo.spTestingcarReminders @carKey = 100 -- int
EXEC dbo.spTestingcarReminders @carKey = 200 -- int
EXEC dbo.spTestingcarReminders @carKey = 300 -- int



DROP PROCEDURE dbo.spTestingcarReminders;

Open in new window

output
on another note, it is not good practice (or at least used to not be) to name your stored procedures with the "sp_" prefix as that is what MS uses for its system stored procedures and the optimizer will look in master and other system locations first and not  in your local database.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
The case when I am passing 1 or nothing works fine, sometimes I will wan to pass a list of cars (100,200 for example) that case does not work for me.

I wan to do something like this
EXEC dbo.spTestingcarReminders @carKey = 100,200
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris LuttrellSenior Database ArchitectCommented:
EXEC dbo.spTestingcarReminders @carKey = 100,200
that will not work as written, it will treat the 100 as the value for @carKey and 200 as a second parameter that your SP may or may not expect.  You will either have to pass those in as a string '100,200' and then use some parse function to split them before using in your query inside your stored procedure or do as Nakul suggested and change to using a Table Valued Parameter.
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
How can do that
0
 
_agx_Commented:
You will either have to pass those in as a string '100,200' and then use some parse function to split them before using in your query inside your stored procedure

That'd probably be the easiest from CF.  Search the archives on parsing comma separated values.  There are different options depending on the dbms. For SQL Server use a table valued UDF and add the returned table to your JOIN.

https://www.experts-exchange.com/articles/1536/Delimited-list-as-parameter-what-are-the-options.html
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
I tried to using the split but it does not work for me.
SELECT dbo.fn_getReminderDate (ca.carKey,ca.activityID) as dueOnDate,L.name as locationName,
isNUll(C.oilChangeMilage,0) + isNULL(CA.repeatNumber,0) as milageDue,C.carnumber,C.make,C.model,C.modelyear,CA.activityID,LCA.activityName,
(case when CA.remindCycle = 1 THEN DATEadd(DAY,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID)) 
						   when CA.remindCycle = 2 THEN DATEadd(WEEK,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
						   when CA.remindCycle = 3 THEN DATEadd(MONTH,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
						   when CA.remindCycle = 4 THEN DATEadd(YEAR,CA.remindNumber,dbo.fn_getReminderDate (ca.carKey,ca.activityID))
						   END) as dueDate,
						   CA.carKey
  FROM Car C
	 inner join carActivities CA on ca.carKey = C.carKey
	 left outer join Location L on L.locationKey = C.locationkey
	 inner join lkup_carActivity LCA on LCA.activityID = CA.activityID 
	 
	 OUTER  APPLY (
	   SELECT item AS splitCarKey from DelimitedSplit8K(@carKey, ',')
	)as ca2
	inner JOIN car AS L2 ON CA2.splitCarKey = L2.carKey
	 
where (dbo.fn_getReminderDate (ca.carKey,ca.activityID) <= getDate() 
	 
	 OR (
	 CA.activityID = 1 AND 
		isNUll(C.oilChangeMilage,0)-isNUll(Ca.remindNumber,0) + isNULL(CA.repeatNumber,0) <= (
		
		
			SELECT top 1 CD.endmileage 
			from car_details CD 
			Inner join sessionUnit SU on SU.sessionKey = CD.sessionkey
			WHERE CD.carKey = C.carKey
			ORDER BY SU.sessionStart desc
		)
	 
	 ))
	 
	
		--AND (@carKey is  null OR CA.carKEy in (@carKey))
	
GROUP BY CA.activityID,CA.carKEy,L.name,C.oilChangeMilage,CA.remindNumber,CA.repeatNumber,C.carnumber,C.make,C.model,C.modelyear,LCA.activityName,CA.remindCycle

Open in new window

0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Great solution
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now