Passing mulitple Values to Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing mulitple Values to Stored Procedure

Author

Topic

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2006-02-16 : 16:28:45

I am passing a variable from a Coldfusion page to a Store Procedure and the value is formatted like this:

1234','5678','9012

but I am not returning any records. If I paste just one of the values into the stored procedure I return records. Any thoughts? Any and all help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 16:29:12

Please post your code.

Tara Kizer
aka tduggan

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2006-02-16 : 16:39:13

Coldfusion form:
<form name="frmDateRange" action="reports.cfm?qry=<cfoutput>#qry#</cfoutput>" target="_self" method="post">
<textarea name="inpTrackingNum" rows="10" ><cfoutput>#inpTrackingNum#</cfoutput></textarea>
<input type="submit" class="submit" value="Run Report" name="submitExcel">
</form>

Coldfusion stored procedure:

<cfstoredproc procedure="sp_MRS_DA_BPTN" datasource="1017_SQL_IE_MRS">
<cfprocparam
type="in"
variable="retTrackingNum"
dbVarName="@inpTrackingNum"
value="#inpTrackingNum#"
CFSQLType="cf_sql_varchar">
<cfprocresult name="procBptn">
</cfstoredproc>

SQL Stored Procedure:

CREATE PROCEDURE [dbo].[sp_MRS_DA_BPTN]
@inpTrackingNum varchar(200)
AS
SELECT Distinct(TRACKINGNUMBER), EMPLOYEENAME, StationNum, time, LOCATION, where trackingnumber IN (@inpTrackingNum)
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 16:54:12

Here you go:
http://www.sqlteam.com/item.asp?ItemID=11499

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-17 : 01:44:21

Also refer Where in @MYCSV here
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2006-02-17 : 10:10:30

Thanks for all of the help guys the UDF worked pretty well for me...