November 12, 2013 at 6:06 am
hi
i'm trying to filter a dynamic pivot using a variable. so i take in an int for the year (also an accountid) then attempt the following...
-- build the sql statement
set @query = 'SELECT AccountId, AccountName, StockId, StockCode, StockDescription, StockGroup, StockGroupDescription, ' + @cols + '
from
(
select a.AccountId, a.AccountName, a.StockId, a.StockCode, a.StockDescription, a.StockGroup, a.StockGroupDescription,
convert(CHAR(10), b.Date, 120) PivotDate, 0 as defaultValue
from #tempDates b
left join (select * from dbo.CustomerStockCrossJoin where AccountId = ' + @accountId + ') a
on Year(b.Date) = ' + @yr +
) x
pivot
(
sum(defaultValue)
for PivotDate in (' + @cols + ')
) p
order by AccountName, StockGroup, StockDescription, StockCode'
however, the @yr seems to be causing an issue. i'm getting the error: conversion failed when converting the nvarchar XXX value to data type int. (XXX is the whole select statement)
when i take out the @yr variable and use:
'on Year(b.Date) =2013'
.. it all works fine
can anyone help, thanks.
November 12, 2013 at 6:24 am
You are mixing string and integer data.
Try converting your integer variable to a string before you insert it into the code block.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 12, 2013 at 8:30 am
just the job, thanks.
November 12, 2013 at 6:32 pm
Excuse me for butting in here, on a job that you seem to indicate is complete.
I strongly advise you reconsider how you're constructing the @SQL string. Wherever you've concatenated in a local variable, you should instead be referring to a dynamic sql parameter which you can then assign when you use:
EXEC sp_executesql @SQL, N'@yr=@yr', @yr=1946
This avoids possible issues with SQL injection.
BOL has some good examples here: http://technet.microsoft.com/en-us/library/ms188001.aspx
I also show how to do this in a recent article published by SSC:
How to Design, Build and Test a Dynamic Search Stored Procedure [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 13, 2013 at 2:03 am
This avoids possible issues with SQL injection.
Hi Dwain, I did consider this. But as the variable in question had a numeric datatype, I could not see how there could be an injection risk. Am I wrong?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 13, 2013 at 2:43 am
Phil Parkin (11/13/2013)
This avoids possible issues with SQL injection.
Hi Dwain, I did consider this. But as the variable in question had a numeric datatype, I could not see how there could be an injection risk. Am I wrong?
Good point Phil. Hadn't really thought about it that way.
But as a general course people should probably try to use sp_executesql whenever they can because of the additional control it gives you. It even supports outputting variables.
I guess I've trained myself to always use it, so that makes me think that everyone should always use it. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 13, 2013 at 3:12 am
thanks both. really interesting stuff.
Dwain Camps, How to Design, Build and Test a Dynamic Search Stored Procedure was really useful.
November 13, 2013 at 10:24 am
i've created my query string but am getting another issue.
when i use
-- create the dynamic pivot
set @query = N'SELECT AccountId, AccountName, StockGroup, StockGroupDescription, StockId, StockCode, StockDescription, ' + @cols + '
FROM (
SELECTAccountId, AccountName, StockId, StockCode, StockDescription, StockGroup, StockGroupDescription, Quantity, convert(varchar(10), Date, 120) PivotDate
FROM#t1) x
PIVOT
(
sum(Quantity)
for PivotDate in (' + @cols + ')
) p
order by AccountId, StockGroup, StockDescription, StockCode'
.. i get this error :
Msg 203, Level 16, State 2, Line 85
The name 'SELECT AccountId, AccountName, StockGroup, StockGroupDescription, StockId, StockCode, StockDescription, [2013-01-06],[2013-01-13],[2013-01-20],[2013-01-27],[2013-02-03],[2013-02-10],[2013-02-17],[2013-02-24],[2013-03-03],[2013-03-10],[2013-03-17],[2013-03-24],[2013-03-31],[2013-04-07],[2013-04-14],[2013-04-21],[2013-04-28],[2013-05-05],[2013-05-12],[2013-05-19],[2013-05-26],[2013-06-02],[2013-06-09],[2013-06-16],[2013-06-23],[2013-06-30],[2013-07-07],[2013-07-14],[2013-07-21],[2013-07-28],[2013-08-04],[2013-08-11],[2013-08-18],[2013-08-25],[2013-09-01],[2013-09-08],[2013-09-15],[2013-09-22],[2013-09-29],[2013-10-06],[2013-10-13],[2013-1' is not a valid identifier.
but when i use PRINT @query and use the displayed @query string..
SELECT AccountId, AccountName, StockGroup, StockGroupDescription, StockId, StockCode, StockDescription, [2013-01-06],[2013-01-13],[2013-01-20],[2013-01-27],[2013-02-03],[2013-02-10],[2013-02-17],[2013-02-24],[2013-03-03],[2013-03-10],[2013-03-17],[2013-03-24],[2013-03-31],[2013-04-07],[2013-04-14],[2013-04-21],[2013-04-28],[2013-05-05],[2013-05-12],[2013-05-19],[2013-05-26],[2013-06-02],[2013-06-09],[2013-06-16],[2013-06-23],[2013-06-30],[2013-07-07],[2013-07-14],[2013-07-21],[2013-07-28],[2013-08-04],[2013-08-11],[2013-08-18],[2013-08-25],[2013-09-01],[2013-09-08],[2013-09-15],[2013-09-22],[2013-09-29],[2013-10-06],[2013-10-13],[2013-10-20],[2013-10-27],[2013-11-03],[2013-11-10],[2013-11-17],[2013-11-24],[2013-12-01],[2013-12-08],[2013-12-15],[2013-12-22],[2013-12-29]
FROM (
SELECTAccountId, AccountName, StockId, StockCode, StockDescription, StockGroup, StockGroupDescription, Quantity, convert(varchar(10), Date, 120) PivotDate
FROM#t1) x
PIVOT
(
sum(Quantity)
for PivotDate in ([2013-01-06],[2013-01-13],[2013-01-20],[2013-01-27],[2013-02-03],[2013-02-10],[2013-02-17],[2013-02-24],[2013-03-03],[2013-03-10],[2013-03-17],[2013-03-24],[2013-03-31],[2013-04-07],[2013-04-14],[2013-04-21],[2013-04-28],[2013-05-05],[2013-05-12],[2013-05-19],[2013-05-26],[2013-06-02],[2013-06-09],[2013-06-16],[2013-06-23],[2013-06-30],[2013-07-07],[2013-07-14],[2013-07-21],[2013-07-28],[2013-08-04],[2013-08-11],[2013-08-18],[2013-08-25],[2013-09-01],[2013-09-08],[2013-09-15],[2013-09-22],[2013-09-29],[2013-10-06],[2013-10-13],[2013-10-20],[2013-10-27],[2013-11-03],[2013-11-10],[2013-11-17],[2013-11-24],[2013-12-01],[2013-12-08],[2013-12-15],[2013-12-22],[2013-12-29])
) p
ORDER BY AccountId, StockGroup, StockDescription, StockCode
..it works fine. @query variable is declare @query AS NVARCHAR(MAX). The string len is 1798.
am i missing something? thanks
November 13, 2013 at 1:42 pm
got it....
just needed to add brackets to the @query variable when it's being executed to stop it being mistaken for a stored proc, so:
exec (@query)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply