Dynamic query parameter conversion

Question

Post reply

Dynamic query parameter conversion

spin

Hall of Fame

Points: 3271
More actions
January 22, 2019 at 3:22 am

#376864

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 23842 More actions · Answer 1

spin - Tuesday, January 22, 2019 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

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 follows
DECLARE @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;

How to post data/code on a forum to get the best help.

DesNorton SSC-Insane Points: 23842 More actions · Answer 2

spin - Tuesday, January 22, 2019 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

If you insist on building the string dynamically, you need to explicitly cast your int variable to a string
DECLARE @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);

How to post data/code on a forum to get the best help.

spin Hall of Fame Points: 3271 More actions · Answer 3

spin

Hall of Fame

Points: 3271

January 22, 2019 at 4:34 am

#2019609

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

spin Hall of Fame Points: 3271 More actions · Answer 4

spin

Hall of Fame

Points: 3271

January 22, 2019 at 6:18 am

#2019621

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

DesNorton SSC-Insane Points: 23842 More actions · Answer 5

spin - Tuesday, January 22, 2019 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

I am not sure how you plan to convert the BIGINT to an ALL indicator.
LIKE is for string comparisons, not numerics

How to post data/code on a forum to get the best help.

spin Hall of Fame Points: 3271 More actions · Answer 6

spin

Hall of Fame

Points: 3271

January 22, 2019 at 8:09 am

#2019645

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!

Jonathan AC Roberts SSCoach Points: 17601 More actions · Answer 7

spin - Tuesday, January 22, 2019 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!

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.

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 8

spin - Tuesday, January 22, 2019 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

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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Michael L John One Orange Chip Points: 26551 More actions · Answer 9

spin - Tuesday, January 22, 2019 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

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://qa.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/

spin Hall of Fame Points: 3271 More actions · Answer 10

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 ; '