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?

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


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)

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

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

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


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)

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