SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic SQL and IN Operator


Dynamic SQL and IN Operator

Author
Message
bubs
bubs
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 196
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.
Eddie Wuerch
Eddie Wuerch
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 2203
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
bubs
bubs
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 196
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.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19284 Visits: 14900
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
bubs
bubs
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 196
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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89937 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
bubs
bubs
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 196
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89937 Visits: 41146
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Attachments
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14718 Visits: 12238
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

bubs
bubs
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 196
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!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search