January 22, 2019 at 3:22 am
Hi
I'm trying to execute a dynamic query which takes (or should take) a bigint value to filter the results.
Essentially the query is...
DECALRE @sqlStr varchar(8000)
DECLARE @PrincipalId bigint
SET @PrincipalId = 4
SET @sqlStr = N'
SELECT BrandName, Date, Spend
FROM [dbo].[myView]
WHERE PrincipalId = ' + @PrincipalId + ')
execute(@sqlStr)
'
PrincipalId is a bigint int in my table. The error i'm getting is Error converting data type nvarchar to bigint.
Thanks
January 22, 2019 at 3:38 am
spin - Tuesday, January 22, 2019 3:22 AMHiI'm trying to execute a dynamic query which takes (or should take) a bigint value to filter the results.
Essentially the query is...
DECALRE @sqlStr varchar(8000)
DECLARE @PrincipalId bigint
SET @PrincipalId = 4
SET @sqlStr = N'
SELECT BrandName, Date, Spend
FROM [dbo].[myView]
WHERE PrincipalId = ' + @PrincipalId + ')
execute(@sqlStr)
'PrincipalId is a bigint int in my table. The error i'm getting is Error converting data type nvarchar to bigint.
Thanks
You should not be injecting your variables directly into the string for execution, as this opens you up to SQL injection attacks.
A better method to exec dynamic SQL is as followsDECLARE @sqlStr varchar(8000);
DECLARE @PrincipalId bigint;
SET @PrincipalId = 4
SET @sqlStr = '
SELECT BrandName, Date, Spend
FROM [dbo].[myView]
WHERE PrincipalId = @PrincipalId';
EXECUTE sys.sp_executesql
@stmt = @sqlStr
, @params = N'@PrincipalId bigint'
, @PrincipalId = @PrincipalId;
January 22, 2019 at 3:42 am
spin - Tuesday, January 22, 2019 3:22 AMHiI'm trying to execute a dynamic query which takes (or should take) a bigint value to filter the results.
Essentially the query is...
DECALRE @sqlStr varchar(8000)
DECLARE @PrincipalId bigint
SET @PrincipalId = 4
SET @sqlStr = N'
SELECT BrandName, Date, Spend
FROM [dbo].[myView]
WHERE PrincipalId = ' + @PrincipalId + ')
execute(@sqlStr)
'PrincipalId is a bigint int in my table. The error i'm getting is Error converting data type nvarchar to bigint.
Thanks
If you insist on building the string dynamically, you need to explicitly cast your int variable to a stringDECLARE @sqlStr varchar(8000)
DECLARE @PrincipalId bigint
SET @PrincipalId = 4
SET @sqlStr = '
SELECT BrandName, Date, Spend
FROM [dbo].[myView]
WHERE PrincipalId = ' + CONVERT(varchar(20), @PrincipalId);
execute(@sqlStr);
January 22, 2019 at 4:34 am
I thought I was avoiding injection by having an SSRS drop down but have now changed the query to your suggestions.
Thanks Des
** and by the way, it's working as expected. cheers
January 22, 2019 at 6:18 am
Hi
Just one thing, if you can help. How would i adjust the script to include a like statement instead of an =?
so, something like...AND PrincipalId like ''% @PrincipalId %''
The reason behind it is I'd like the user to either select <All> or just one Principal
Thanks
January 22, 2019 at 7:59 am
spin - Tuesday, January 22, 2019 6:18 AMHiJust one thing, if you can help. How would i adjust the script to include a like statement instead of an =?
so, something like...
AND PrincipalId like ''% @PrincipalId %''The reason behind it is I'd like the user to either select <All> or just one Principal
Thanks
I am not sure how you plan to convert the BIGINT to an ALL indicator.
LIKE is for string comparisons, not numerics
January 22, 2019 at 8:09 am
Hi
I know, i've tried converting everything to varchar(6) (even the principalid in the underlying table) and all i get is an empty recordset returned.
confused!
January 22, 2019 at 12:19 pm
spin - Tuesday, January 22, 2019 8:09 AMHiI know, i've tried converting everything to varchar(6) (even the principalid in the underlying table) and all i get is an empty recordset returned.
confused!
Try changing:execute(@sqlStr);
to print @sqlStr
Then when you run it just paste the output of the print into SSMS and see why it's not returning anything.
January 22, 2019 at 12:29 pm
spin - Tuesday, January 22, 2019 4:34 AMI thought I was avoiding injection by having an SSRS drop down but have now changed the query to your suggestions.
Thanks Des** and by the way, it's working as expected. cheers
There is no reason for you to build a dynamic query - create a stored procedure with the parameter(s) and just pass the parameters. There are many ways to setup the 'all' parameter...the problem with most of them is that they may not generate an optimal plan. Example:
-- If you pass in a -1 reset to NULL for ALL
SET @PrincipalID = iif(@PrincipalID = -1, Null, @PrincipalID);
-- In the where clause, check the parameter
WHERE (PrincipalID = @PrincipalID Or @PrincipalID Is Null)
There are known problems with this approach - but you need to test and validate the performance yourself. Another way is to use a range:
-- Get min/max values
SELECT @minValue = min(PrincipalID), @maxValue = max(PrincipalID) FROM yourPrincipalTable;
WHERE @PrincipalID BETWEEN coalesce(@PrincipalID, @minValue) AND coalesce(@PrincipalID, @maxValue)
For this one - if the passed in parameter is -1 you set it to NULL and then use the min/max values. Again, test and validate performance...
Depending on how complex the actual query is - these techniques may not work...if that is the case then you can use dynamic SQL and sp_executeSql to build the appropriate query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 22, 2019 at 12:57 pm
spin - Tuesday, January 22, 2019 4:34 AMI thought I was avoiding injection by having an SSRS drop down but have now changed the query to your suggestions.
Thanks Des** and by the way, it's working as expected. cheers
You are actually creating the opportunity for injection.
If this is an SSRS report, there are a number of ways to handle this.
The first question I have is why are you passing in a list of columns dynamically? If the format of the report is such that, depending upon the values of the parameters, there may or may not be values in columns, then show or hide the columns in SSRS by using a formula. Test for the value or the parm, or the existence of the value in a column, and show or hide as appropriate. If you are trying to create a dynamic report, then you may be working a bit too hard. Unless there are an unlimited number of possibilities (there usually are not!), create separate reports.
Multi-select parameters are passed in as comma-separated lists by default in SSRS. You can parse these lists and use them in any number of manners in the proc.
I suggest you read up on sp_ExecuteSQL, and use that to dynamically build your queries.
Here are some links:
http://www.sqlservercentral.com/articles/T-SQL/106648/
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 23, 2019 at 3:38 am
Hi
the reason behind the dynamic query is I'm trying to create a pivot based on a start/end date but I want to create columns for dates where there are no values and found this script which creates my date columns. (apologies, I can't remember the source)
DECLARE @dtStart date
DECLARE @dtEnd date
DECLARE @cols NVARCHAR(MAX)
SET @dtStart = '2019-01-01'
SET @dtEnd = '2019-12-31'
-- drop the temp date table if it exists
IF OBJECT_ID('tempdb..#tempDates') IS NOT NULL
DROP TABLE #tempDates
-- select into a #temp table the list of dates that you want to turn to columns
;with cte (fcStart, fcEnd) as
(
SELECT DISTINCT [FirstDayOfMonth] fcStart, [LastDayOfMonth] fcEnd FROM [dbo].[DateTable] d WHERE Date between @dtStart and @dtEnd
union all
SELECT dateadd(MM, 1, fcStart), fcEnd FROM cte WHERE fcStart < fcEnd
)
SELECT c.fcStart, @dtStart StartDate, @dtEnd EndDate INTO #tempDates FROM cte c WHERE fcEnd < @dtEnd
-- generate the dynamic column range
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), fcStart, 120)) from #tempDates
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') ,1,1,'')
I this then get passed in as dynamic columns to the pivot query...
-- build the dynamic SQL query
set @sqlStr = '
SELECT PrincipalName, ' + @cols + '
from (
SELECT PrincipalName, DailySpend, convert(CHAR(10), fcStart, 120) PivotDate
FROM (select distinct * from #tempdates) d
LEFT JOIN (
SELECT PrincipalName, [Date], [FirstDayOfMonth], [LastDayOfMonth], DailySpend
FROM [dbo].[MyTable] s
WHERE s.Date BETWEEN @dtStart AND @dtEnd
AND PrincipalId = @PrincipalId
) b on d.fcStart between b.[FirstDayOfMonth] and b.[LastDayOfMonth]
) x
PIVOT (SUM([DailySpend]) for PivotDate in (' + @cols + ')) p
ORDER BY PrincipalName
;
'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy