t-sql 2012 split function

Question

Post reply

t-sql 2012 split function

wendy elizabeth

SSCoach

Points: 16915
More actions
December 19, 2017 at 11:33 am

#409877

In t-sql 2012, I want to setup a stored procedure instead of having the sql direcdtly in the ssrs 2012 report.
When I execute the following SQL , nothing is located and there should be;
select * from Enrollment
From TEST.dbo.Enrollment Enrollment
JOIN #ParsedSSGrades AS ParsedSSGrades
ON ParsedSSGrades.grade = Enrollment.Grade
declare @ssGrade VARCHAR(8000) = 'PK,KG,01,02'
The following is the sql for the function and the #ParsedSSGrades table.
CREATE FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)
IF OBJECT_ID('tempdb..#ParsedSSGrades') IS NOT NULL DROP TABLE #ParsedSSGrades
CREATE TABLE #ParsedSSGrades (grade VARCHAR(4))
INSERT INTO #ParsedSSGrades
SELECT [_id]
FROM TEST.dbo.fn_splitString(@ssGrade)
Would you show me what is wrong so that I can understand where my problem Lies?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

How about I point you here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

How about what is wrong with what I just submitted? The splitter function works for everything else at my company. I would like to know why it does not work for me.
I am suppose to use the splitter logic I should you since that is a company standard to use it.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Not sure, the following works for me:
declare @ssGrade VARCHAR(8000) = 'PK,KG,01,02'; SELECT * FROM fn_splitString(@ssGrade);

Doing the insert into a temp table works as well.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

wendy elizabeth - Tuesday, December 19, 2017 2:02 PM
How about what is wrong with what I just submitted? The splitter function works for everything else at my company. I would like to know why it does not work for me.
I am suppose to use the splitter logic I should you since that is a company standard to use it.

It depends. Can you post the code for the fn_splitString function, please because there might actually be something very wrong with it, especially if it's the function I'm thinking of.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

Hmm.

What do you get from the following queries?

SELECT * FROM Enrollment WHERE Grade IN ('PK','KG','01','02') SELECT Grade, COUNT(*) FROM Enrollment GROUP BY Grade

Thomas Rushton
blog: https://thelonedba.wordpress.com

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I was able to use your split function without issue based on what you posted. What is happening when you use it?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Jeff Moden - Thursday, December 21, 2017 6:23 PM
wendy elizabeth - Tuesday, December 19, 2017 2:02 PM
How about what is wrong with what I just submitted? The splitter function works for everything else at my company. I would like to know why it does not work for me.
I am suppose to use the splitter logic I should you since that is a company standard to use it.
It depends. Can you post the code for the fn_splitString function, please because there might actually be something very wrong with it, especially if it's the function I'm thinking of.

Ah... sorry... I missed where you've already posted it. If it were a snake, it would have bitten me.

As Lynn says,

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)