parse input string for usp. 1

[sparkbyte]

I need to be able to have users input an Integer string like.
1,66,84
This could be more or fewer values than the example.

This is the SP I am working with.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE dbo.usp_CentOps_UPS_Priority_Report 
	@DateParam1 As DateTime,
	@DateParam2 As DateTime,
	@UPSpriorityList As INT

AS
BEGIN
	SET NOCOUNT ON;

Select 
	FileNumber = 
		case 
			when ascii(substring(t.FileNumber,3,1))> 65 then Upper(FileNumber) [green]--If the 3-rd char is a letter, return the code[/green]
[green]--			when ascii(substring(t.FileNumber,2,1))> 65 then 'Bad File Number'[/green] [green]--If the 2-nd char is a letter, show error text[/green]
			when len(FileNumber)<10 then Upper(left(t.FileNumber,1)) + replicate('0', 10-len(t.FileNumber)) 
				+ right(t.FileNumber, len(t.FileNumber)-1) [green]-- If the First char is a letter, pad with zeroes[/green]
[green]--			when len(t.FileNumber)<10 then 'Too long: ' + cast(len(t.FileNumber) as varchar(3)) + ' CHARS' [/green]
			else Upper(t.FileNumber )
		end,
	Upper(t.BoxNumber) As [TrackingNumber],
	t.TrackingDate,
	s.UPS_ShippingNumberDescription

From dbo.tblTrackingTable t
	Left Outer Join dbo.tblUPSShippingMethods s On TrackingNumberShipping = UPS_ShippingNumber

Where		(t.TrackingDate Between Convert(DATETIME, @DateParam1, 102) -- Date format (2010-05-24 00:00:00.000)
			AND	DateAdd(dd,1,DateAdd(s, -1, Convert(DATETIME, @DateParam2, 102)))) -- (2010-05-28 23:59:59.000)
		AND (t.EmployeeID IS NOT NULL) 
		AND (t.FileNumber <> '') 
		AND (t.BoxNumber <> '') 
		AND (t.FileNumber <> '.BOX.END.') 
		AND (t.TrackingDate IS NOT NULL)
		[blue]AND	(t.TrackingNumberShipping IN (@UPSpriorityList))[/blue]
[green]--		AND	((t.TrackingNumberShipping = 1) OR (t.TrackingNumberShipping = 66) OR (t.TrackingNumberShipping = 84))[/green]

END
GO

The result set is used to create an excel spreadsheet for operations.

What i can't seem to figure out is how to parse the incoming string so that it will work in this part of the WHERE clause.
(t.TrackingNumberShipping IN (@UPSpriorityList))

So that the Where clause would become.
(t.TrackingNumberShipping IN (1,2,66))

EXECUTE [dbo].[usp_CentOps_UPS_Priority_Report] [red] '3/1/2011'[/red] ,[red]'3/3/2011'[/red],[red] '1,2,66' [/red]

[tt][red]
Msg 8114, Level 16, State 1, Procedure usp_CentOps_UPS_Priority_Report, Line 0
Error converting data type varchar to int.
[/red][/tt]

No mystery here, the comma seperated values are being passed in as a varchar.

So how would I parse the varchar value back into an Int for the Where Clause??



Thanks

John Fuhrman

 

[gmmastros]

You need to find yourself a good SPLIT function and then use it in your query.

From dbo.tblTrackingTable t
     Inner Join dbo.YourSplitFunction(@YourCommaDelimitedList) As SplitValues
        On t.TrackingNumberShipping = SplitValues.ColumnName
     Left Outer Join dbo.tblUPSShippingMethods s 
       On TrackingNumberShipping = UPS_ShippingNumber

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

HOOOORRRAAAAA

http://WWWWW!!!

You did it again!!

From dbo.tblTrackingTable t
     Inner Join (Select items From fn_Split(@UPSpriorityList,',')) As SplitValues
        On t.TrackingNumberShipping = SplitValues.items
     Left Outer Join dbo.tblUPSShippingMethods s 
       On TrackingNumberShipping = UPS_ShippingNumber

Never thought to use a join for the filter... duh...

Thanks!!!!!!!

Thanks

John Fuhrman

 

[gmmastros]

Never thought to use a join for the filter

Don't forget it. I use this all the time. It applies to more than just splitting comma delimited values.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom