SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


t-sql 2012 split function


t-sql 2012 split function

Author
Message
wendy elizabeth
wendy elizabeth
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11474 Visits: 925

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?


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)

Group: General Forum Members
Points: 273097 Visits: 41199
How about I point you here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
wendy elizabeth
wendy elizabeth
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11474 Visits: 925
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
Lynn Pettis
SSC Guru
SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)

Group: General Forum Members
Points: 273097 Visits: 41199

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.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)

Group: General Forum Members
Points: 618337 Visits: 45200
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Thomas Rushton
Thomas Rushton
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: Moderators
Points: 13815 Visits: 7042
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

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)SSC Guru (273K reputation)

Group: General Forum Members
Points: 273097 Visits: 41199
I was able to use your split function without issue based on what you posted. What is happening when you use it?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)SSC Guru (618K reputation)

Group: General Forum Members
Points: 618337 Visits: 45200
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search