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

 

[gmmastros]

try:

WHERE @Location is null or L.LocationDWID IN (SELECT * FROM [!]dbo.[/!]SplitList(@Location,','))

-George

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

 

[Mack2]

Hi George,

I tried that and the following error comes up.

Invalid object name 'dbo.SplitList

It is like SQL does not recognize the splitlist as a function....

Thank you for your help!!!

 

[gmmastros]

Run this:

Select Routine_Schema, Routine_Name
From   Information_Schema.Routines As R
Where  Routine_Type = 'Function'
       And Routine_Name Like '%split%'

If this returns any data, post it here.

-George

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

 

[markros]

Are you sure you added this function into your database and not to the master database? The above query should give you the answer.

 

[Mack2]

I ran your code. It did not return anything. No errors, and no results.

When I type splitlist into the query, it is not highlighted like other functions.

THANK YOU FOR YOUR HELP!!!!!!

 

[markros]

Just add this function again to your database, e.g.

USE myDatabase

create function SplitList ...

 

[gmmastros]

When I type splitlist into the query, it is not highlighted like other functions.

When you use a built-in function, the query editor window will color it differently. There is no built-in function for splitting a list. You can create this function, but until you do, you will not be able to use it. You can't use something that does not exist.



-George

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

 

[Mack2]

So there is no built in function to do what I need?

If not, is there a site that has the split function available to copy?

Thanks!!!!

 

[gmmastros]

http://sqlblog.com/blogs/adam_macha...2/splitting-a-string-of-unlimited-length.aspx

-George

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

 

[Mack2]

Thanks. I copied code create the function. I get errors. I believe I am in over my head. I am using ssrs, my tsql is average.

 

[gmmastros]

try this...

open a query window. Copy/paste this:

Create Function [dbo].[SplitList]
	(
	@CommaDelimitedFieldNames Varchar(8000), 
	@CharToFind VarChar(10) 
	) 
Returns @Tbl_FieldNames Table (Position Integer Identity(1,1), FieldName VarChar(8000)) As 
Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @CharToFind
 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<DataLength(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@CharToFind, @CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(8000))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End

You only need to create the function once. You can reuse it as many times as you would like.

Then, change your query to:

WHERE @Location is null or L.LocationDWID IN (SELECT [!]FieldName[/!] FROM SplitList(@Location,','))

-George

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

 

[Mack2]

Bless you George!!! It is working. I have spent 1.5 days on this. I run the report from ssrs and it is passing multiple parameters!!!
Really stupid question, I can not find the function under the functions folder. Where is it keeping the user defined functions?

THANK YOU AGAIN!!!

 

[Mack2]

George,

The split function does not seem to be taking the parameter. Whatever value I give to be passed it always returns the same amount of records.

So when I execute the procedure and pass bad numbers like
'7777777,11111111' , it still returns all of the records. When it should not return any records.

Getting close

 

[Mack2]

It should not matter that I am sending it two parameter's should it?

L.LocationDWID IN (SELECT L.LocationDWID FROM SplitList(@Location,',')) and

EP.JobCodeDMID IN (SELECT EP.JobCodeDMID FROM SplitList(@Job_Code,','))

 

[gmmastros]

I can not find the function under the functions folder

Sometimes you need to refresh the list for it to show up. The easiest way is to right click the functions folder and then click refresh.

Under the functions folder (if you are using SQL Server Management Studio), there will be a sub-folder for "Table-Valued Functions". It should be listed there.

The split function does not seem to be taking the parameter.

Can you show the whole query? I suspect the problem has more to do with the way you are using it.

-George

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

 

[markros]

You're calling it incorrectly.

This is what you're trying

L.LocationDWID IN (SELECT L.LocationDWID FROM SplitList(@Location,',')) and

EP.JobCodeDMID IN (SELECT EP.JobCodeDMID FROM SplitList(@Job_Code,','))

and you should try instead

L.LocationDWID IN (SELECT FieldName FROM SplitList(@Location,',')) and
                       
EP.JobCodeDMID IN (SELECT FieldName FROM SplitList(@Job_Code,','))

Alternatively, you may want to join with this table instead.

But anyway, try subquery first.

 

[Mack2]

Here is the basics of it. Hopefully you can read it.

ALTER PROCEDURE [dbo].[TurnoverDetail]
@IncludeTemps varchar(10),
@Location nvarchar(50),
@Job_Code nvarchar(50),
@fromdate datetime,
@todate datetime


AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

SET NOCOUNT ON;

-- IF @IncludeTemps = 'true'

SELECT EP.JobCodeDMID, EP.EmployeeDWID, CONVERT(varchar, CONVERT(datetime, CONVERT(char(8), P.StartDate)), 101) AS EffectDate,
DP.PositionDescription, L.LocationDescription, CASE WHEN JobCode IN ('SINSTRUCT', 'SSPCRDEX', 'SINSSPED', 'SSPCLTEX', 'SSPCACAD')
THEN 1 ELSE 0 END AS 'Teacher?', CASE WHEN TT.TerminationTypeDescription = 'Voluntary' THEN 1 ELSE 0 END AS 'Voluntary Term',
CASE WHEN TT.TerminationTypeDescription = 'Involuntary' THEN 1 ELSE 0 END AS 'Involuntary Term', CASE WHEN JobCode IN ('SINSTRUCT',
'SSPCRDEX', 'SINSSPED', 'SSPCLTEX', 'SSPCACAD') AND TT.TerminationTypeDescription = 'Voluntary' THEN 1 ELSE 0 END AS 'Teach Voluntary Term',
CASE WHEN JobCode IN ('SINSTRUCT', 'SSPCRDEX', 'SINSSPED', 'SSPCLTEX', 'SSPCACAD') AND
TT.TerminationTypeDescription = 'Involuntary' THEN 1 ELSE 0 END AS 'Teach Involuntary Term', L.LocationDWID, J.JobCodeDescription,
CASE WHEN JobCode IN ('CPRINCPL', 'SADMINEX', 'SADMINNEX', 'SBLDGLEAD', 'SPRINCIP', 'SPRINASTN', 'SPRINEXEC') AND
TT.TerminationTypeDescription = 'Involuntary' THEN 1 ELSE 0 END AS 'Admin Involuntary Term', CASE WHEN JobCode IN ('CPRINCPL', 'SADMINEX',
'SADMINNEX', 'SBLDGLEAD', 'SPRINCIP', 'SPRINASTN', 'SPRINEXEC') AND
TT.TerminationTypeDescription = 'Voluntary' THEN 1 ELSE 0 END AS 'Admin Voluntary Term', CASE WHEN Jobcode IN ('Unknown', 'SSUBADMNE',
'SSMRINST', 'CSRCOORD', 'ZCONV', 'CTEAMLD', 'CADMSTRNE', 'CDIRECT', 'CSRANLYST', 'CTECHNCN', 'CSRASSOC', 'SSUBSTIT', 'STCHINST',
'CSUPERV', 'STCHNEW', 'STUTOR', 'SPSYCH', 'SCROSSGD', 'STCHDEV', 'CPRESDNT', 'CSRSUPERV', 'CCONSLT', 'CASSOCNEX', 'SADMSPNE',
'SCOUNS', 'SCOTA', 'CSRCONSLT', 'CVP', 'CADMINEX', 'SNURSE', 'SAIDEINSN', 'CASANALST', 'CREPEX', 'CINTERN', 'CADMSTORE', 'CBUYER',
'CSRACCNT', 'CMANAGE', 'STEMPAIDE', 'SDIRECT', 'SSWORKER', 'SREPNEX', 'CHDIRECT', 'CTEMP', 'SAIDLRE', 'SAIDLNCH', 'SSPEECH',
'SPROCTOR', 'SSECURTY', 'CADMINNEX', 'CTRAINER', 'SCOORDAR', 'CPARALGL', 'CWRITER', 'CEXASSIST', 'SCOORDLN', 'CANLYST', 'SPRGASSNT',
'CSPECIAL', 'CASSOCEX', 'CREPORT', 'SCOORDNEX', 'CSRTECHN', 'SAIDNINSN', 'CRECRUIT', 'CPRINEXEC', 'SCUSTOD', 'SSUBLT', 'SAIDSCWK',
'CCOORDNEX', 'CSRVP', 'SOCCTHER', 'CDEVELOP', 'CSRADMINS', 'CCOORDEX', 'CCLERK', 'SCOORDEX', 'CACCNT', 'COFFICER') AND
TT.TerminationTypeDescription = 'Voluntary' THEN 1 ELSE 0 END AS 'Support Staff Voluntary Term', CASE WHEN Jobcode IN ('Unknown',
'SSUBADMNE', 'SSMRINST', 'CSRCOORD', 'ZCONV', 'CTEAMLD', 'CADMSTRNE', 'CDIRECT', 'CSRANLYST', 'CTECHNCN', 'CSRASSOC', 'SSUBSTIT',
'STCHINST', 'CSUPERV', 'STCHNEW', 'STUTOR', 'SPSYCH', 'SCROSSGD', 'STCHDEV', 'CPRESDNT', 'CSRSUPERV', 'CCONSLT', 'CASSOCNEX',
'SADMSPNE', 'SCOUNS', 'SCOTA', 'CSRCONSLT', 'CVP', 'CADMINEX', 'SNURSE', 'SAIDEINSN', 'CASANALST', 'CREPEX', 'CINTERN', 'CADMSTORE',
'CBUYER', 'CSRACCNT', 'CMANAGE', 'STEMPAIDE', 'SDIRECT', 'SSWORKER', 'SREPNEX', 'CHDIRECT', 'CTEMP', 'SAIDLRE', 'SAIDLNCH', 'SSPEECH',
'SPROCTOR', 'SSECURTY', 'CADMINNEX', 'CTRAINER', 'SCOORDAR', 'CPARALGL', 'CWRITER', 'CEXASSIST', 'SCOORDLN', 'CANLYST', 'SPRGASSNT',
'CSPECIAL', 'CASSOCEX', 'CREPORT', 'SCOORDNEX', 'CSRTECHN', 'SAIDNINSN', 'CRECRUIT', 'CPRINEXEC', 'SCUSTOD', 'SSUBLT', 'SAIDSCWK',
'CCOORDNEX', 'CSRVP', 'SOCCTHER', 'CDEVELOP', 'CSRADMINS', 'CCOORDEX', 'CCLERK', 'SCOORDEX', 'CACCNT', 'COFFICER') AND
TT.TerminationTypeDescription = 'InVoluntary' THEN 1 ELSE 0 END AS 'Support Staff InVoluntary Term', J.JobCode
FROM vwCurrentPosition AS P INNER JOIN
Employee.DimEmployeePosition AS EP ON EP.EmployeeDWID = P.EmployeeDWID AND
EP.EmployeePositionEffectDateDMID = P.StartDate INNER JOIN
Employee.DimPosition AS DP ON EP.PositionDWID = DP.PositionDWID INNER JOIN
Employee.DimEmployeeTransaction AS ET ON ET.EmployeeDWID = P.EmployeeDWID INNER JOIN
Employee.DimDepartment AS D ON D.DepartmentDWID = EP.DepartmentDWID INNER JOIN
Employee.DimLocation AS L ON L.LocationDWID = D.LocationDWID INNER JOIN
Employee.DimJobCode AS J ON J.JobCodeDMID = EP.JobCodeDMID LEFT OUTER JOIN
Employee.DimEmployeeTermination AS ETM ON ETM.EmployeeDWID = P.EmployeeDWID LEFT OUTER JOIN
Employee.DimTerminationType AS TT ON ETM.TerminationTypeDMID = TT.TerminationTypeDMID
WHERE L.LocationDWID IN (SELECT L.LocationDWID FROM SplitList(@Location,',')) and
EP.JobCodeDMID IN (SELECT EP.JobCodeDMID FROM SplitList(@Job_Code,',')) and
(ET.TransactionIsCurrent = 1)
AND(CONVERT(varchar,CONVERT(datetime, CONVERT(char(8), P.StartDate)), 101) BETWEEN @fromdate AND @todate)
ORDER BY L.LocationDescription

 

[markros]

Ok, try

  WHERE          L.LocationDWID   IN (SELECT FieldName FROM SplitList(@Location,',')) and
                       EP.JobCodeDMID   IN (SELECT FieldName FROM SplitList(@Job_Code,','))    and
                     (ET.TransactionIsCurrent = 1)  
                      AND(CONVERT(varchar,CONVERT(datetime, CONVERT(char(8), P.StartDate)), 101) BETWEEN @fromdate AND @todate)
                      ORDER BY L.LocationDescription

the date comparison is also wrong, but let's deal with one problem at the time.

 

[Mack2]

This is what I use to test the procedure

EXECUTE dbo.TurnoverDetail
@IncludeTemps = TRUE, @Location = '1,3,6' , @Job_Code = '10,11,12', @fromdate = '11/01/2007', @todate = '01/01/2009'