Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Passing value to a stored procedure

Posted on 2016-10-25
8
106 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 13

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Dump exec output to table 3 21
changing page verifacation 1 26
Managing DSNs in CF 11 5 17
Accessing variables in MySQL query 4 28
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

856 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