SQLTeam.com | Weblogs | Forums

Tsql separate values


#1

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?


#2

Please provide insert statement(s) with example data and desired output.


#3

SSRS does not like temp tables in stored procedures - you should eliminate that part of the process. You can avoid that by using a better string split (google: DelimitedSplit8K for one of the better implementations).

SSRS also does not do well when returning the counts - so you want to disable it.

Using your split version - rewrite the stored procedure as:

SET NOCOUNT ON;

DECLARE @ssGrade varchar(8000) = 'PK,KG,01,02';

select * from Enrollment
From TEST.dbo.Enrollment Enrollment
Where Enrollment.Grade In (Select [_id] From dbo.fn_splitString(@ssGrade))

#4

I didn't know that ... and its something which is about to crop up in a project.

Can you give me a pointer to what the problem with #TEMP in SProc, called from SSRS, is please?

Thanks.


#5

It has to do with how SSRS (and SSIS) bind to the procedure - when trying to build the report in SSDT where you have temp tables, instead of being able to parse the procedure it has to be run which can cause performance issues.

Deployed reports don't have an issue - as far as I recall - because they just execute the procedure.


#6

Ah, I see, thanks.

That's great, I can "fake" the #TEMP during DEV :slight_smile: