Dynamic SQL and IN Operator
-
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.
-
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 -
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.
-
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
Consultant - Straight Path Solutions
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 -
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)
-
bubs (4/13/2011)
I use a sql function called Split that handles the string so it can be used using the IN operatorAs 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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
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.
IdData112
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.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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
-
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!!
-
What is the type of the ClosedDate column of the PerformanceReviewHistory table?
If the type is DATE, try using
....PRH.ClosedDate BETWEEN + cast(' + convert(nchar(8), @pStartDate, 112) + 'AS date) AND cast (' + covert(nchar(8), @pEndDate, 112) + ' AS date) AND....
If it isn't DATE, try changing the cast to match it - if it's datetime do something similar to the above with datetime instead of date, similarly if it's datetime2.
If it's nvarchar(N) (where's the nvarchar in the error message come from?) you can work out what string format it is, and it may be that the format is one where string comparisons work for dates in which case it's straightforwards but it may be that its one where they don't in which case it's horrible (you probably end up converting the ClosedDate column before doing the comparison - bang goes efficiency because your condition isn't sargable).
Tom
-
declare @test-2 varchar(300)
set @test-2 = '10,11,12'
set @test-2 = REPLACE(@test,',',''',''')
set @test-2 = ''''+@test+''''
print @test-2
It will create for the IN operator
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBAMy Blog
www.aureus-salah.com -
Tom.Thomson (4/18/2011)
What is the type of the ClosedDate column of the PerformanceReviewHistory table?If the type is DATE, try using
....PRH.ClosedDate BETWEEN + cast(' + convert(nchar(8), @pStartDate, 112) + 'AS date) AND cast (' + covert(nchar(8), @pEndDate, 112) + ' AS date) AND....
If it isn't DATE, try changing the cast to match it - if it's datetime do something similar to the above with datetime instead of date, similarly if it's datetime2.
If it's nvarchar(N) (where's the nvarchar in the error message come from?) you can work out what string format it is, and it may be that the format is one where string comparisons work for dates in which case it's straightforwards but it may be that its one where they don't in which case it's horrible (you probably end up converting the ClosedDate column before doing the comparison - bang goes efficiency because your condition isn't sargable).
Yes the ClosedDate is of type date.
Getting a different conversion error: "Explicit conversion from data type int to date is not allowed."
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 (Select EmpSplit.Data from dbo.Split(@pEmployeeIds, '','') AS EmpSplit) AND
PRH.ClosedDate BETWEEN cast(' + convert(nchar(8), @pStartDate, 112) + 'AS date) AND cast (' + convert(nchar(8),@pEndDate,112) + ' AS date) 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
-
bubs (4/18/2011)
Yes the ClosedDate is of type date.Getting a different conversion error: "Explicit conversion from data type int to date is not allowed."
OK, it's absolutely clear what is going on - some quote marks are needed, it was stupid of me to leave them out when I added the casts. 20040101 for example is an integer, not the same thing as the string '20040101'
In place of
cast(' + convert(nchar(8), @pStartDate, 112) + 'AS date)
you can use
cast(''' + convert(nchar(8), @pStartDate, 112) + ''' AS date)
and add 4 extra 's to the other cast in the same way.
In fact you I think you can do it with just extra quote marks and no casts, like this
....BETWEEN '''+convert(nchar(8), @pStartDate, 112) + ''' AND '''+ convert(nchar(8),@pEndDate,112) + ''' AND....
(I can't check because I don't have an SQL system with me just now; but if implicit conversion to date can happen as seems to be indicated by your original error the casts should not be needed).
Tom
-
What if you use the same convert for the value you are checking. Like ... convert(nvarchar(20), PRH.ClosedDate, 112) ?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply