Solved

Passing value to a stored procedure

Posted on 2016-10-25
8
68 Views
Last Modified: 2016-11-02
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
Comment
Question by:erikTsomik
8 Comments
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41859018
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41863158
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 41864317
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41864485
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 19

Author Comment

by:erikTsomik
ID: 41864490
How can do that
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 41866774
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
 
LVL 19

Author Comment

by:erikTsomik
ID: 41870639
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
 
LVL 19

Author Closing Comment

by:erikTsomik
ID: 41870745
Great solution
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now