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?
|