Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Dynamic SQL and IN Operator Expand / Collapse
Author
Message
Posted Wednesday, April 13, 2011 11:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 11:24 PM
Points: 43, Visits: 183
I am using dynamic sql to create a pivot table. The dynamic function accepts a sql query as a parameter.

In the front end of my application I am calling a stored proc and passing in a string of multiple selected id’s. For example “’10’, ‘11’, ‘12’”.
I use a sql function called Split that handles the string so it can be used using the IN operator, like so:

@pEmployeeId varchar(1000)
SELECT *
FROM Employee E
WHERE E.ID IN (Select EmpSplit.Data from dbo.Split(@pEmployees, ',') AS EmpSplit)
This works as intended.

However, I can’t figure out how to create the query needed to pass to the dynamic sql using the Split function.

This is what I have so far:

CREATE PROCEDURE [dbo].[SEL_REVIEW_RATINGS_BY_EMPLOYEE]
@pEmployeeIds varchar(1000)
AS
BEGIN
DECLARE @sql as varchar(5000) = 'SELECT E.LastName + '', '' + E.FirstName AS Name, SM.MetricText, PRR.Rating
FROM
Employee E,
SkillsMetric SM,
PerformaceReviewHistory PRH,
PerformanceReviewRating PRR
WHERE
E.ID IN (Select EmpSplit.Data from dbo.Split(@pEmployeeIds, '','') AS EmpSplit) AND
E.ID = PRH.EmployeeID AND
SM.ID = PRR.SkillsMetricID AND
PRH.ID = PRR.PerformanceID '

EXEC DYNAMIC_PIVOT @sql, 'YEAR(PRH.ClosedDate)', 'SUM(PRR.Rating)'
END

When I execute the query I get the error “Must declare the scalar variable "@pEmployeeIds"
Anyone know how I would go about creating the @sql query to pass to the DYNAMIC_PIVOT function?

I can get it to work if I just use one id as an int, like so:
CREATE PROCEDURE [dbo].[SEL_REVIEW_RATINGS_BY_EMPLOYEE]
@pEmployeeId int
AS
BEGIN
DECLARE @sql as varchar(5000) = 'SELECT E.LastName + '', '' + E.FirstName AS Name,
SM.MetricText,
PRR.Rating
FROM
Employee E,
SkillsMetric SM,
PerformaceReviewHistory PRH,
PerformanceReviewRating PRR
WHERE
E.ID = '+ CAST(@pEmployeeId AS nvarchar(25)) +' AND
E.ID = PRH.EmployeeID AND
SM.ID = PRR.SkillsMetricID AND
PRH.ID = PRR.PerformanceID '

EXEC DYNAMIC_PIVOT @sql, 'YEAR(PRH.ClosedDate)', 'SUM(PRR.Rating)'
END

Thanks in advance.
Post #1093317
Posted Thursday, April 14, 2011 4:05 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 4:17 PM
Points: 439, Visits: 1,094
Since you're already passing in a string list of values, and adding them to a dynamic (string) command, why bother splitting them?
DECLARE @sql as varchar(5000) = '
SELECT E.LastName + '', '' + E.FirstName AS Name, SM.MetricText, PRR.Rating
FROM
Employee E,
SkillsMetric SM,
PerformaceReviewHistory PRH,
PerformanceReviewRating PRR
WHERE
E.ID IN (' + @pEmployeeIds + ') AND
E.ID = PRH.EmployeeID AND
SM.ID = PRR.SkillsMetricID AND
PRH.ID = PRR.PerformanceID '



Eddie Wuerch
MCM: SQL
Post #1093862
Posted Thursday, April 14, 2011 8:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 11:24 PM
Points: 43, Visits: 183
Sorry, I made a mistake in my post. The string passed into the stored proc doesn't contain the single quotes. It is passed like this "10,11,12"

That is what the split function is for, it adds in the single quotes so I can use it with th IN operator.

Post #1093884
Posted Friday, April 15, 2011 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 10,910, Visits: 12,545
Without seeing the dynamic_pivot procedure I can't give you the solution you probably want. I'm going to agree with Eddie.

If the ID column is int you don't need to split the string. Try this:

DECLARE @ids VARCHAR(100), @sql NVARCHAR(1000)

SET @ids = '4,5,6'

SET @sql = 'Select * from sys.objects as o where o.object_Id in (' + @ids + ')'

EXEC (@sql)





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1094371
Posted Friday, April 15, 2011 3:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 11:24 PM
Points: 43, Visits: 183
here is the Dynamic_Pivot Procedure

CREATE procedure [dbo].[DYNAMIC_PIVOT]
(
@select varchar(max),
@PivotCol varchar(max),
@Summaries varchar(max)
) as
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')


create table #pivot_columns (pivot_column varchar(max))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'

exec(@sql)
Post #1094416
Posted Friday, April 15, 2011 10:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,956, Visits: 30,245
bubs (4/13/2011)
I use a sql function called Split that handles the string so it can be used using the IN operator


As a bit of a sidebar, you should post that Split function. Most folks find one on the internet that works and they find out the hard way just how bad it is. I'd be happy to take a peak at it and offer an alternative if I see the potential for a performance problem.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1094461
Posted Saturday, April 16, 2011 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 11:24 PM
Points: 43, Visits: 183
Here is the Split function:

CREATE FUNCTION [dbo].[Split]
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(2000)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END
Post #1094506
Posted Saturday, April 16, 2011 12:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,956, Visits: 30,245
Thanks for posting the splitter code. There are several problems with that splitter code that you may not be aware of.

1. That splitter doesn’t work as advertised according to the code itself. The input variables suggest that it will allow a 5-character delimiter but if we feed it a string with even just a 2-character delimiter…

SELECT * FROM dbo.Split('12|-|34|-|56|-|78','|-|');


… here’s what you get.

Id	Data
1 12
2 |34
3 |56
4 |78


2. The splitter code uses RBAR to do the split. In this case, it’s what I call a “Nibbler” splitter which finds the first element, stores it, and the “bites” the first element and first delimiter off of the string. The process continues until no delimiters are left and it does a final insert with everything that’s left as the final element. Because of the multiple inputs and the fact that the function is necessarily an mTVF (Multi-line Table Valued Function which has all of the overhead of a Scalar Function), this function is relatively slow.

3. The splitter uses artificial limits on the size of the string that it can be fed. With MAX data-types being the exception, one would always expect an NVARCHAR splitter to be able to split up to and including the full width of an NVARCHAR(4000).

4. The splitter has a built in LTRIM/RTRIM which could be a real problem if leading or trailing spaces are ever required to be preserved. My recommendation would be to do such trimming outside of the function if such a need arises. For those items not needing to be trimmed, having trimming done inside the function doesn’t do anything except slow things down.

Here’s the code that I recommend you use. If you really need for the trimming to be done inside the splitter, do that at the final assignment of “Item = “. Of course, you can also rename the splitter and the column names it returns to match your current splitter.

Sorry folks... the code I posted here previously works fine for commas but has a case problem with letters.  I'll replace the code here with the fix as soon as I can.

Just in case you’ve heard that “Tally Table” based splitters get slow as the number of elements increased or the width of the string increased, that used to be true. This is a new type of splitter that I just completed a new article for, which will be coming out soon. If you still have doubts, here’s a performance chart from the article for a very similar VARCHAR(8000) splitter. The heavy Blue line is your “Nibbler” splitter. The Red line is how old “Tally Table” based splitters used to act. The heavy Black line is the new splitter above.



--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems


  Post Attachments 
Splitter Performance 10-20 Characters Per Element.gif (290 views, 15.35 KB)
Post #1094521
Posted Saturday, April 16, 2011 12:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
Your problem is that @pEmployeeIds exists in the stored procedure, not in the batch executed by DYNAMIC PIVOT.
The solution: embed the value of @pEmployeeIds in the string passed in, and split that embedded value not the variable which exists outside.


Tom
Post #1094522
Posted Monday, April 18, 2011 10:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 11:24 PM
Points: 43, Visits: 183
Thanks for all the help. Almost got it working, except I wanted to add a start/end date filter which I thought would be easy, but am getting some conversion errors.

CREATE PROCEDURE [dbo].[SEL_REVIEW_RATINGS_BY_EMPLOYEE]
@pEmployeeIds varchar(1000),
@pStartDate date,
@pEndDate date
AS
BEGIN
DECLARE @sql as varchar(5000) = 'SELECT E.LastName + '', '' + E.FirstName AS Name, SM.MetricText, PRR.Rating
FROM
Employee E,
SkillsMetric SM,
PerformaceReviewHistory PRH,
PerformanceReviewRating PRR
WHERE
E.ID IN (' + @pEmployeeIds + ') AND
PRH.ClosedDate BETWEEN ' + @pStartDate + ' AND ' + @pEndDate + ' AND
E.ID = PRH.EmployeeID AND
SM.ID = PRR.SkillsMetricID AND
PRH.ID = PRR.PerformanceID '

EXEC DYNAMIC_PIVOT @sql, 'YEAR(PRH.ClosedDate)', 'SUM(PRR.Rating)'
END

This throws the error:
Error converting data type nvarchar to datetime.

I tried converting the dates like this, but that doesn't work either:
.....PRH.ClosedDate BETWEEN ' + convert(nvarchar(20), @pStartDate, 112) + ' AND ' + convert(nvarchar(20), @pEndDate, 112) + ' AND.....

This throws an error
Operand type clash: date is incompatible with int

How do I get the dates in there?

Thanks again for all the help!!
Post #1095148
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse