SplitList function not working 2

[Mack2]

I am passing A muli select paramater from ssrs to a stored procedure.

Within my stored procedure I am using the splitlist function to separate the values.

However the error I get is Invalid object name 'SplitList'.

WHERE @Location is null or L.LocationDWID IN (SELECT * FROM SplitList(@Location,','))

Thanks in advance for your help!!!

 

[Mack2]

Should'nt the fieldname by L.LocationDWID ??? If not, I guess I do not understand what it is looking for

 

[Mack2]

If I changed the values for the date parameters, then the results change. If I change the values for the @jobcode and @location, then the results do not change.

Thanks for your help

 

[gmmastros]

There are two types of functions, scalar, and table-valued. Scalar functions are those you are *probably* already familiar with. For example, the ABS function returns the absolute value of a number ABS(10) = 10, ABS(-10) = 10.

SplitList is a table-valued function. This means.... it's a function that returns a table of values. In this case, it returns two columns and many rows. The columns are Position and FieldName.

[tt][blue]Create Function [dbo].[SplitList]
(
@CommaDelimitedFieldNames Varchar(8000),
@CharToFind VarChar(10)
)
[!]Returns @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(8000)) As [/!][/blue][/tt]

Position is really just an incrementing number. FieldName is the actual value separated by your delimiter.

Ex:

Select * From Split('a,b,c,d', ',')

[tt]
Position FieldName
----------- ---------
1 a
2 b
3 c
4 d
[/tt]

So, it is the FieldName column of the SplitList function that you want to compare on.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I wish I had the same great way of explaining things!!!

 

[Mack2]

It is working.

I did not have the word fieldname
(IN (SELECT FieldName FROM ) after the select. I thought you wanted the actual field name. Thanks Markos !!!!!!

Thanks again George.