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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy