November 7, 2016 at 8:13 am
I always used a query with an(one) integer parameter, but now it has to change to more integers in the parameter.
Before:
DECLARE @Param INT SET @Param = 1
SELECT * FROM Trips WHERE TripID = @Param
Future (a csv of Integers):
DECLARE @Param VARCHAR(MAX) SET @Param = ‘1, 2, 3’ -- 1 or more integers
This of course does’nt work:
SELECT * FROM Trips WHERE TripID IN (@Param).
I could use:
DECLARE @sql NVARCHAR(MAX)
SET @sql= 'SELECT * FROM Trips WHERE TripID IN ('+@Param+')'
EXEC sp_executesql @sql
But, as the param comes from internet, it is vulnerable for SQL injection attacks, I think.
I would like to use a solution like:
DECLARE @Param VARCHAR(MAX) SET @Param = '1, 2, 3'
DECLARE @Tbl TABLE(ID INT)
--need a trick to get the numbers in the Param
SELECT * FROM Trips R INNER JOIN @Tbl T ON R.TripID = T.ID
There is a way using XML (I have Sql2014), but I couldn’t figure out how to do it.
Or perhaps there is another way to solve this problem.
Any help?
November 7, 2016 at 8:20 am
November 7, 2016 at 8:31 am
(expanding on what Phil said...)
You need a "splitter" function - the one Phil posted a link to is the best one for the job. This query:
DECLARE @param varchar(1000) = '1,2';
SELECT * FROM dbo.DelimitedSplit8K(@param,',')
Returns:
ItemNumber Item
---------- ------
1 1
2 2
You can use it against a table like this:
-- sample data
IF OBJECT_ID('tempdb..#test') IS NOT NULL DROP TABLE #test;
SELECT * INTO #test
FROM (VALUES (1, 'xxx'), (2, 'yyy'), (3, 'zzz')) x(col1,col2);
-- Solution
DECLARE @param varchar(1000) = '1,2';
SELECT t.*
FROM #test t
JOIN dbo.DelimitedSplit8K(@param,',') ON col1 = item;
col1 col2
----------- ----
1 xxx
2 yyy
-- Itzik Ben-Gan 2001
November 7, 2016 at 9:43 am
I can't place a UDF in the Database. I am a developer, not the owner. So the "shown solution" is not possible for me.
But I can use the idea (make a Tally table on the fly and use it for finding the positions of the number(s) in the CSV) 🙂
I'll give it a try, and if I not succeed I'll ask for your help again.
@Phil: Sorry, I thought that I made a correct description of the problem. Even now I don't know how to do it better 🙁
November 7, 2016 at 9:45 am
One option is to use XML, here is a simple example
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @PARAXML XML = '<X>1</X><X>2</X><X>3</X><X>4</X><X>5</X>';
SELECT
P.DATA.value('(./text())[1]','INT') AS PARAM_VALUE
FROM @PARAXML.nodes('/X') P(DATA);
Output
PARAM_VALUE
-----------
1
2
3
4
5
Quick question, how many parameter values do you expect in a single call?
November 7, 2016 at 10:06 am
@"Quick question, how many parameter values do you expect in a single call?"
Not more then 5. It's the count of the Themes of the Trip.
The number of trips is also not so big, max 1000.
Thank you for your solution and the example.
I'll ask for the possibility of a XML parameter list in stead of a CSV list.
November 7, 2016 at 10:13 am
You are very welcome
😎
Another question, how frequently will this be called?
November 7, 2016 at 10:26 am
@ "Another question, how frequently will this be called?"
Not so often:
From March till August at max a 100 times per hour. Rest of the year almost never.
November 7, 2016 at 10:58 am
If you can't add a new UDF you can just apply the logic to a SQL query like so:
DECLARE
@pString VARCHAR(8000) = '11,15,200',
@pDelimiter CHAR(1) = ',';
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY N1),
Item = SUBSTRING(@pString, N1, L1)
FROM
(
SELECT N1, L1 = ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,N1),0)-N1,8000)
FROM
(
SELECT * FROM
(
SELECT 1 UNION ALL SELECT t.N+1
FROM
(
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(x),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(x),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c(x),
(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(x)
) t(N)
WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
) cteStart(N1)
) cteLen(N1)
) cteLen;
If there are no more than 5 delimiters then you could split these values more efficiently using CROSS APPLY like so:
DECLARE
@pString VARCHAR(8000) = '11,15,200',
@pDelimiter CHAR(1) = ',';
WITH splitString AS
(
SELECT
col1 = SUBSTRING(a.string, 0, d1.d),
col2 = SUBSTRING(a.string, d1.d + 1, ISNULL((d2.d - d1.d - 1),1000)),
col3 = SUBSTRING(a.string, d2.d + 1, ISNULL((d3.d - d2.d - 1),1000)),
col4 = SUBSTRING(a.string, d3.d + 1, ISNULL((d4.d - d3.d - 1),1000)),
col5 = SUBSTRING(a.string, d4.d + 1, ISNULL((d5.d - d4.d - 1),1000))
FROM (VALUES (@pString)) a(string)
CROSS APPLY (SELECT NULLIF(CHARINDEX(@pDelimiter, a.string, 0), 0)) d1(d)
CROSS APPLY (SELECT NULLIF(CHARINDEX(@pDelimiter, a.string, d1.d + 1), 0)) d2(d)
CROSS APPLY (SELECT NULLIF(CHARINDEX(@pDelimiter, a.string, d2.d + 1), 0)) d3(d)
CROSS APPLY (SELECT NULLIF(CHARINDEX(@pDelimiter, a.string, d3.d + 1), 0)) d4(d)
CROSS APPLY (SELECT NULLIF(CHARINDEX(@pDelimiter, a.string, d4.d + 1), 0)) d5(d)
)
SELECT
item
FROM splitString ss
CROSS APPLY (VALUES (ss.col1), (ss.col2), (ss.col3), (ss.col4), (ss.col5)) unpvt(item)
WHERE item IS NOT NULL;
edit: minor formatting fix
-- Itzik Ben-Gan 2001
November 7, 2016 at 12:22 pm
Henk Schreij (11/7/2016)
@ "Another question, how frequently will this be called?"Not so often:
From March till August at max a 100 times per hour. Rest of the year almost never.
Then this shouldn't be a problem, gets troublesome when one has 1-10M calls an hour
😎
November 7, 2016 at 12:56 pm
Wow, . . .
Thank you both Alan and Eirikur.
I'll try your solutions, after having analysed them, and choose one of them.
They are a bit to sophisticated to me to understand them at this very moment. Butt I'll manage ;-). Learned a lot today.
In the future (beginning next year) you can choose more then one Theme, Country or Travel-week in https://www.vinea.nl/zoeken?
November 8, 2016 at 1:56 pm
I always used a query with an integer parameter, but now it has to change to more integers in the parameter.
Before:
DECLARE @Param INTEGER;
SET @Param = 1
SELECT *
FROM Trips
WHERE TripID = @Param;
>> Future (a csv of Integers): <<
Please, please, please read any book in RDBMS and the relational model! This would make Dr. Codd ill. The whole relational model is based on tables made of rows, rows made of columns, and columns that store only scalar values (no structured data! No lists, no arrays, etc.)
>> But, as the param comes from internet, it is vulnerable for SQL injection attacks, I think. <<
Oh yeah! Big time!
You can use XML in the front end before you get to the database. I do not like it because it means I have a system that mixes SQL and XML together. This means you need a programmer who knows both and you have to maintain both at all times. After 30+ years with SQL. I much, much prefer to keep my SQL is pure as possible.
I have two articles on using the long parameter list to do this sort of thing.
https://www.simple-talk.com/sql/learn-sql-server/values-and-long-parameter-lists/
https://www.simple-talk.com/sql/learn-sql-server/values-and-long-parameter-lists-part-ii/
Decide if you want accurate results, portability and data integrity, or if you just want to "get her done", to quote Larry the cable Guy
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 8, 2016 at 11:47 pm
Henk Schreij (11/7/2016)
@"Quick question, how many parameter values do you expect in a single call?"Not more then 5. It's the count of the Themes of the Trip.
The number of trips is also not so big, max 1000.
Thank you for your solution and the example.
I'll ask for the possibility of a XML parameter list in stead of a CSV list.
It's also one of the slowest and more resource intensive of splitters not to mention that it treats adjacent delimiters differently. If you use it for something other than numbers, you must also either remove special symbols or use TYPE to de-entitize the data and allow for special characters.
Also, you need to ask people if they'd put the function in the database. Just because you're a developer doesn't mean that your hands are tied when it comes to making a difference.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2016 at 2:09 am
Are you able to change the parameter to a table-parameter? If so, you can add your array of values to it and use it in an inner join.
November 10, 2016 at 3:14 am
Are you able to change the parameter to a table-parameter?
I wish 🙂
I get the parameters from a outsite source: the people who build the website.
I have some influence on what they pass on, but they came with the solution of a comma separated list of integers (or a space separated list, xml list, etc.).
I'll give it a try again.
@celko/jeff:
I tried the xml solution (I transformed the csv in xml), the performance is weird:
98 % is for the xml reader, 1% for the xml path filter and 1% for the sql excecution (a join of 10 small tables).
The total execution time is 660 msec (the first time with a new combination of parameters) or 15 msec (when parameters used again).
Not so happy with it.
It's the company policy that the DB is used for storing the tables (with FK's including cascades, defaults). But no stored procedures or functions.
I'll have a look, if they will make an exception for the SplitDelimiter function: much better performance I expect.
@celko: I have read your articles. Very good at explaining and well readable, but ...
I feel a bit bullied about the way you describe people who try to unfold a csv string.
I'm not so stupid as you write there, know something about normalization.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply