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

variable in dynamic pivot Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 6:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
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.
Post #1513431
Posted Tuesday, November 12, 2013 6:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
You are mixing string and integer data.

Try converting your integer variable to a string before you insert it into the code block.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1513437
Posted Tuesday, November 12, 2013 8:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
just the job, thanks.
Post #1513490
Posted Tuesday, November 12, 2013 6:32 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1513681
Posted Wednesday, November 13, 2013 2:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1513735
Posted Wednesday, November 13, 2013 2:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:53 PM
Points: 3,438, Visits: 5,390
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1513751
Posted Wednesday, November 13, 2013 3:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
thanks both. really interesting stuff.


Dwain Camps, How to Design, Build and Test a Dynamic Search Stored Procedure was really useful.

Post #1513767
Posted Wednesday, November 13, 2013 10:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
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 (
SELECT AccountId, 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 (
SELECT AccountId, 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
Post #1513983
Posted Wednesday, November 13, 2013 1:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:30 AM
Points: 121, Visits: 429
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)
Post #1514051
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse