December 19, 2017 at 11:33 am
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?
December 19, 2017 at 12:15 pm
How about I point you here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
December 19, 2017 at 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.
December 19, 2017 at 2:37 pm
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.
December 21, 2017 at 6:23 pm
wendy elizabeth - Tuesday, December 19, 2017 2:02 PMHow 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
Change is inevitable... Change for the better is not.
December 22, 2017 at 4:31 am
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
December 22, 2017 at 5:07 pm
I was able to use your split function without issue based on what you posted. What is happening when you use it?
December 23, 2017 at 9:12 am
Jeff Moden - Thursday, December 21, 2017 6:23 PMwendy elizabeth - Tuesday, December 19, 2017 2:02 PMHow 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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply