November 12, 2015 at 3:01 pm
In t-sql 2012, I want to run a query where the value is normally an int value. I want to supply a large volume of custID values that are normally int values. I have tried to use a cast and convert values and that does not work. The query that I am trying to use is the following:
DECLARE @custID varchar(200)
set @custID = '72793,60546,91069'
select * from table
where in (@custID)
Thus can you show me the t-sql 2012 that I can use to accomplish my goal?
November 12, 2015 at 3:13 pm
Best is to use a very efficient splitter, such as DelimitedSplit8K, and then do an INNER JOIN to those results:
DECLARE @custID varchar(200)
set @custID = '72793,60546,91069'
SELECT tn.*
FROM table_name tn
INNER JOIN dbo.DelimitedSplit8K (@custID) ds ON ds.Item = tn.custID
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 12, 2015 at 3:13 pm
I'm so s-m-r-t!!! Good point, Scott... DelimitedSplit8K is perfect... (How could I miss something so blindingly obvious?)
November 13, 2015 at 9:09 am
I tried to use dbo.DelimitedSplit8K and my t-sql 2012 does not recognize this item. I pointed to the msdb, tempdb, and master tables and that did not solve the problem. Thus can you tell me what I need to do so dbo.DelimitedSplit8K is recoginized?
November 13, 2015 at 9:20 am
Sorry, I should have been clearer on that point!
DelimitedSplit8K is an inline table-valued function for SQL Server, but it's not part of SQL itself, it was developed by users. So you'll need to Google the code, download it, and create that function in your own db, then call it from there.
I didn't, and can't, post the code only because my "injection" filter at work won't allow me to.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 13, 2015 at 12:49 pm
Just to make it easier to get to the correct article for DelimitedSplit8K
November 13, 2015 at 1:21 pm
Luis Cazares (11/13/2015)
Just to make it easier to get to the correct article for DelimitedSplit8K
Be forewarned, Wendy, that DelimitedSpli8K will change the way you look at data...in a good way. It might also change your expectations of performance. The article isn't short, but it's well worth taking the time to read and understand it.
November 14, 2015 at 10:02 am
Before going down the "pass an array as a string" path, I think the alternatives are worth considering. A good compendium is How to Share Data between Stored Procedures. I do think Erland's tabulated methods go beyond just being useful for stored procedures.
November 16, 2015 at 6:30 am
I have quite a few cases where I pass a delimited list to a stored procedure. The DelimitedSplit8K ITVF lets me to use the list as a table, so it performs very well. For me, it's a tried-and-true technique that's very reliable.
November 17, 2015 at 6:37 am
Passing a delimited string certainly works. But I am currently working an issue where one sproc uses STUFF FOR XML to create a delimited string. A second sproc accepts and parses that delimited string. The problem is that the second sproc is raising "Error converting data type nvarchar to bigint" when attempting to use what was parsed.
My developers wish to consider that error as being rooted in a "data problem". I consider that error to be rooted in a "data type problem". My recommendation is to not use a delimited string, but to instead pass data between these two sprocs using a strongly typed method (such as a temporary table). Instead, the developers want to hunt down and fix the "bad" data. Unless they can constrain the source, the error will be back.
November 17, 2015 at 6:58 am
SoHelpMeCodd (11/17/2015)
Passing a delimited string certainly works. But I am currently working an issue where one sproc uses STUFF FOR XML to create a delimited string. A second sproc accepts and parses that delimited string. The problem is that the second sproc is raising "Error converting data type nvarchar to bigint" when attempting to use what was parsed.My developers wish to consider that error as being rooted in a "data problem". I consider that error to be rooted in a "data type problem". My recommendation is to not use a delimited string, but to instead pass data between these two sprocs using a strongly typed method (such as a temporary table). Instead, the developers want to hunt down and fix the "bad" data. Unless they can constrain the source, the error will be back.
Even if a strongly typed method is used, I would consider that fixing bad data should be important. Creating a list of integers and splitting it, shouldn't create an error.
November 18, 2015 at 6:30 am
Luis Cazares (11/17/2015)Even if a strongly typed method is used, I would consider that fixing bad data should be important. Creating a list of integers and splitting it, shouldn't create an error.
A strongly typed method would not have misled a developer into believing that what was being consumed must be a list of integers 🙂
November 18, 2015 at 6:37 am
SoHelpMeCodd (11/18/2015)
Luis Cazares (11/17/2015)Even if a strongly typed method is used, I would consider that fixing bad data should be important. Creating a list of integers and splitting it, shouldn't create an error.
A strongly typed method would not have misled a developer into believing that what was being consumed must be a list of integers 🙂
Never underestimate human stupidity 😀
November 19, 2015 at 2:49 pm
Hi,
try insert values into temporary table/table-valued parameters or table variable and join it with your table.
Table-valued parameters can be used as input parameter of a sproc.
November 19, 2015 at 3:05 pm
SoHelpMeCodd (11/17/2015)
Passing a delimited string certainly works. But I am currently working an issue where one sproc uses STUFF FOR XML to create a delimited string. A second sproc accepts and parses that delimited string. The problem is that the second sproc is raising "Error converting data type nvarchar to bigint" when attempting to use what was parsed.My developers wish to consider that error as being rooted in a "data problem". I consider that error to be rooted in a "data type problem". My recommendation is to not use a delimited string, but to instead pass data between these two sprocs using a strongly typed method (such as a temporary table). Instead, the developers want to hunt down and fix the "bad" data. Unless they can constrain the source, the error will be back.
If the source data is supposed to integer data and it isn't, then I actually agree with the developers and the need to hunt down and fix the bad data. If you use a strongly type method and you still have bad data that can't be converted to an integer, then you haven't solved the problem.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply