September 12, 2017 at 7:42 am
Hi All,
I have a problem with a parameterized query.
I need to pass the conditions as following
exec sp_executesql N'select * from companies where comp_cod in (@comp_cod)',N'@comp_cod nvarchar(19)',@comp_cod=N'2,3'
comp_cod field is int but i need to use the in for the where condition to retrieve multiple rows.
When the parameter comes as on entry as following no problem happens.
exec sp_executesql N'select * from companies where comp_cod in (@comp_cod)',N'@comp_cod nvarchar(19)',@comp_cod=N'2'
Could you help me please with that.
Rgerads
Nader
September 12, 2017 at 8:40 am
I could be wrong, but I think you will need to build this as dynamic SQL. That is:DECLARE @query varchar(255)
DECLARE @comp_cod nvarchar(19) = N'2,3'
SELECT @query = 'SELECT * FROM COMPANIES WHERE comp_cod IN (' + @comp_cod + ')'
EXEC(@query)
That should give you what you are asking for I believe. What you had won't work because it is treating @comp_cod as a single string when you are putting it as '2,3'. And there is nothing in SQL stopping you from having the value '2,3' in a char based column.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 12, 2017 at 9:12 am
Where is the source data coming from, and how much control do you have on it? Could you in stead use A User Defined Table Type? For example:USE Sandbox;
GO
--Sample Table and Data
CREATE TABLE Company (ID int IDENTITY(1,1), CompName varchar(10));
INSERT INTO Company (CompName)
VALUES ('Ted''s'),('Pizzaria'),('Jimmy Dogs');
GO
SELECT *
FROM Company;
GO
--Create a Type
CREATE TYPE CompanyID AS Table (CompID int);
GO
--Insert required values into Parameter, for example
DECLARE @P1 CompanyID;
INSERT INTO @P1 VALUES(2),(3);
--EXECUTE SQL, using table value parameter
EXEC sp_executesql N'select * from Company where ID IN (SELECT CompID FROM @CompID)',N'@CompID CompanyID READONLY',@CompID= @P1;
GO
--Clean up
DROP TYPE CompanyID;
DROP TABLE Company;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 12, 2017 at 9:13 am
bmg002 - Tuesday, September 12, 2017 8:40 AMI could be wrong, but I think you will need to build this as dynamic SQL. That is:DECLARE @query varchar(255)
DECLARE @comp_cod nvarchar(19) = N'2,3'
SELECT @query = 'SELECT * FROM COMPANIES WHERE comp_cod IN (' + @comp_cod + ')'
EXEC(@query)That should give you what you are asking for I believe. What you had won't work because it is treating @comp_cod as a single string when you are putting it as '2,3'. And there is nothing in SQL stopping you from having the value '2,3' in a char based column.
The only problem with this, is you could inject. yes, varchar(19) is relatively small, but you could do something.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 12, 2017 at 10:36 am
Good thoughts with that Thom. SQL Injection, while unlikely in a NVARCHAR(19), could occur. Your solution does eliminate the risk involved of SQL injection.
I suppose if you knew that @comp_cod would only contain integers and a comma, you could do some weird stuff with splitting the string too, but that feels a bit like overkill for this.
In the end, it all depends on what your @comp_cod variable is created from and how easy it is to manipulate the source of that to fit your needs.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 12, 2017 at 11:14 am
bmg002 - Tuesday, September 12, 2017 10:36 AMGood thoughts with that Thom. SQL Injection, while unlikely in a NVARCHAR(19), could occur. Your solution does eliminate the risk involved of SQL injection.
I suppose if you knew that @comp_cod would only contain integers and a comma, you could do some weird stuff with splitting the string too, but that feels a bit like overkill for this.In the end, it all depends on what your @comp_cod variable is created from and how easy it is to manipulate the source of that to fit your needs.
IMO, I don't think that using a splitter is overkill. The code required to use one is quite concise:
CREATE TABLE #companies (CompCode NVARCHAR(10));
INSERT #companies
(
CompCode
)
VALUES
(N'2')
,(N'3')
,(N'4');
DECLARE @comp_cod NVARCHAR(19) = N'2,3';
SELECT c.*
FROM
#companies c
CROSS APPLY dbo.udfDelimitedSplit8K(@comp_cod, ',') split
WHERE c.CompCode = split.Item;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 12, 2017 at 1:29 pm
Phil Parkin - Tuesday, September 12, 2017 11:14 AMbmg002 - Tuesday, September 12, 2017 10:36 AMGood thoughts with that Thom. SQL Injection, while unlikely in a NVARCHAR(19), could occur. Your solution does eliminate the risk involved of SQL injection.
I suppose if you knew that @comp_cod would only contain integers and a comma, you could do some weird stuff with splitting the string too, but that feels a bit like overkill for this.In the end, it all depends on what your @comp_cod variable is created from and how easy it is to manipulate the source of that to fit your needs.
IMO, I don't think that using a splitter is overkill. The code required to use one is quite concise:
CREATE TABLE #companies (CompCode NVARCHAR(10));INSERT #companies
(
CompCode
)
VALUES
(N'2')
,(N'3')
,(N'4');DECLARE @comp_cod NVARCHAR(19) = N'2,3';
SELECT c.*
FROM
#companies c
CROSS APPLY dbo.udfDelimitedSplit8K(@comp_cod, ',') split
WHERE c.CompCode = split.Item;
The problem with it (that I thought was overkill) was that in that example you need to have a function for the splitter. Not everyone has that installed on their systems (myself included). For everything I use SQL for at my workplace, we do not have a real-world use for a string splitter to be stored in SQL. Splitting strings doesn't feel like a SQL task, but more of an application task (most .NET languages for example do string splitting quite well). So building a function to split the string (yes, I know it has been done on the forum and optimized to death) still feels like overkill to me.
But now I feel like I am getting off topic.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 13, 2017 at 1:47 am
bmg002 - Tuesday, September 12, 2017 1:29 PMThe problem with it (that I thought was overkill) was that in that example you need to have a function for the splitter. Not everyone has that installed on their systems (myself included). For everything I use SQL for at my workplace, we do not have a real-world use for a string splitter to be stored in SQL. Splitting strings doesn't feel like a SQL task, but more of an application task (most .NET languages for example do string splitting quite well). So building a function to split the string (yes, I know it has been done on the forum and optimized to death) still feels like overkill to me.But now I feel like I am getting off topic.
I Definitely wouldn't suggest that splitting strings is an application task. Consider that in SSRS if you're passing multi valued parameters, that they are provided in a delimited string. If you didn't split those in SQL, how would you use it? Just an example. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 13, 2017 at 2:20 am
bmg002 - Tuesday, September 12, 2017 8:40 AMI could be wrong, but I think you will need to build this as dynamic SQL. That is:DECLARE @query varchar(255)
DECLARE @comp_cod nvarchar(19) = N'2,3'
SELECT @query = 'SELECT * FROM COMPANIES WHERE comp_cod IN (' + @comp_cod + ')'
EXEC(@query)That should give you what you are asking for I believe. What you had won't work because it is treating @comp_cod as a single string when you are putting it as '2,3'. And there is nothing in SQL stopping you from having the value '2,3' in a char based column.
Thank you for your reply.
The problem with that is that it will create an entry with each different variation of @comp_cod variable, one of the reasons i am using parametrized query other than injection is minimizing cache usage.
Regards
Nader
September 13, 2017 at 2:36 am
bmg002 - Tuesday, September 12, 2017 1:29 PMPhil Parkin - Tuesday, September 12, 2017 11:14 AMbmg002 - Tuesday, September 12, 2017 10:36 AMGood thoughts with that Thom. SQL Injection, while unlikely in a NVARCHAR(19), could occur. Your solution does eliminate the risk involved of SQL injection.
I suppose if you knew that @comp_cod would only contain integers and a comma, you could do some weird stuff with splitting the string too, but that feels a bit like overkill for this.In the end, it all depends on what your @comp_cod variable is created from and how easy it is to manipulate the source of that to fit your needs.
IMO, I don't think that using a splitter is overkill. The code required to use one is quite concise:
CREATE TABLE #companies (CompCode NVARCHAR(10));INSERT #companies
(
CompCode
)
VALUES
(N'2')
,(N'3')
,(N'4');DECLARE @comp_cod NVARCHAR(19) = N'2,3';
SELECT c.*
FROM
#companies c
CROSS APPLY dbo.udfDelimitedSplit8K(@comp_cod, ',') split
WHERE c.CompCode = split.Item;The problem with it (that I thought was overkill) was that in that example you need to have a function for the splitter. Not everyone has that installed on their systems (myself included). For everything I use SQL for at my workplace, we do not have a real-world use for a string splitter to be stored in SQL. Splitting strings doesn't feel like a SQL task, but more of an application task (most .NET languages for example do string splitting quite well). So building a function to split the string (yes, I know it has been done on the forum and optimized to death) still feels like overkill to me.
But now I feel like I am getting off topic.
Using same idea i was able to split it using a table valued function and use the result in the in clause.
Thanks for all the replies, i appreciate it.
Nader
September 13, 2017 at 6:43 am
nadersam - Wednesday, September 13, 2017 2:36 AMbmg002 - Tuesday, September 12, 2017 1:29 PMPhil Parkin - Tuesday, September 12, 2017 11:14 AMbmg002 - Tuesday, September 12, 2017 10:36 AMGood thoughts with that Thom. SQL Injection, while unlikely in a NVARCHAR(19), could occur. Your solution does eliminate the risk involved of SQL injection.
I suppose if you knew that @comp_cod would only contain integers and a comma, you could do some weird stuff with splitting the string too, but that feels a bit like overkill for this.In the end, it all depends on what your @comp_cod variable is created from and how easy it is to manipulate the source of that to fit your needs.
IMO, I don't think that using a splitter is overkill. The code required to use one is quite concise:
CREATE TABLE #companies (CompCode NVARCHAR(10));INSERT #companies
(
CompCode
)
VALUES
(N'2')
,(N'3')
,(N'4');DECLARE @comp_cod NVARCHAR(19) = N'2,3';
SELECT c.*
FROM
#companies c
CROSS APPLY dbo.udfDelimitedSplit8K(@comp_cod, ',') split
WHERE c.CompCode = split.Item;The problem with it (that I thought was overkill) was that in that example you need to have a function for the splitter. Not everyone has that installed on their systems (myself included). For everything I use SQL for at my workplace, we do not have a real-world use for a string splitter to be stored in SQL. Splitting strings doesn't feel like a SQL task, but more of an application task (most .NET languages for example do string splitting quite well). So building a function to split the string (yes, I know it has been done on the forum and optimized to death) still feels like overkill to me.
But now I feel like I am getting off topic.
Using same idea i was able to split it using a table valued function and use the result in the in clause.
Thanks for all the replies, i appreciate it.
Nader
Please post the table valued function that you ended up using.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 13, 2017 at 10:12 am
Thom A - Wednesday, September 13, 2017 1:47 AMbmg002 - Tuesday, September 12, 2017 1:29 PMThe problem with it (that I thought was overkill) was that in that example you need to have a function for the splitter. Not everyone has that installed on their systems (myself included). For everything I use SQL for at my workplace, we do not have a real-world use for a string splitter to be stored in SQL. Splitting strings doesn't feel like a SQL task, but more of an application task (most .NET languages for example do string splitting quite well). So building a function to split the string (yes, I know it has been done on the forum and optimized to death) still feels like overkill to me.But now I feel like I am getting off topic.
I Definitely wouldn't suggest that splitting strings is an application task. Consider that in SSRS if you're passing multi valued parameters, that they are provided in a delimited string. If you didn't split those in SQL, how would you use it? Just an example. 🙂
SSRS can actually do this for you, but you'll have to use an "in" expresison in your "where" clause and then SSRS will hardwire the comma'd parameters before sending the entire mess to SQL. You CAN use the splitter server side tho, so you have your choice. I found this out by tracing a report one of my compadres did and thats what happened, SSRS built a list of all the multivalued selections and sent the entire hardwired list to the server.
What I did was instead send the list to the server instead and split it there, but I did it on the fly without the delimiter8k function, I just included code in the sql that was called to split the list. I couldn't use the delimit8k at the time but nothing stops you from doing similar code inline without the function right?
September 15, 2017 at 2:47 am
You can use the below query directly if you are using the latest version as it Microsoft implemented STRING_SPLIT() built-in function which converts a string into rows as per the seperator. If this function is not available in your version, you can use the logic in the next to populate the table variable and use in the query as explained above.
exec sp_executesql N'select * from companies CROSS APPLY STRING_SPLIT(@comp_cod,'','') AS S where comp_cod = S.value',N'@comp_cod nvarchar(19)',@comp_cod=N'2,3'
--populate table variable using the below logic, and then use the same in the query as per Thom
declare @comp_cod varchar(10)='2,3'
select R.r.value('.','varchar(100)')as comp_code
From (
select cast(('<R>'+replace(@comp_cod,',','</R><R>')+'</R>') as xml) as Code
) as C
cross apply Code.nodes('/R')as R(r)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply