Solved

Pass the content of a List(of Int32) to a stored procedure to use as an IN clause

Posted on 2015-02-06
19
76 Views
Last Modified: 2015-02-17
Ok,
I'll radically simplify the code and SQL to get to the meat of the question.
In ASP.Net, you can create a SqlDataSource and give it a SelectCommand of something like
"exec myspSomeSproc @SomeParam = " & SomeControl.SelectedValue
Easy enough.
But now I need to have something on the SQL Server end that the stored procedure is something like
Select ThisField, ThatField from Stuff INNER JOIN Junk on Stuff.ID = Junk.ID
WHERE ID IN (1,2,3,4,5,6,7,8,9)


The 1,2,3,4,5,6,7,8,9 are a varying number of elements from a List(of Int32)
The elements are always int32, but the number of them will vary.
I don't really want to replicate the stored procedure in code and pass the full SQL statement in and not use a stored procedure.
But building up a string '1,2,3,4,5,6,7,8,9' and passing it in?
Will that work?  I'm thinking that
exec myspSomeSproc @SomeParam = '1,2,3,4,5,6,7,8,9'
with the stored procedure being
Select ThisField, ThatField from Stuff INNER JOIN Junk on Stuff.ID = Junk.ID
WHERE ID IN (@SomeParam)

isn't going to work.

So how do you structure the stored procedure, and the SQL that calls it, to be able to pass in a varying number of items to use in an IN clause?
0
Comment
Question by:Nick67
  • 10
  • 8
19 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 40594728
You could write a dynamic query:
DECLARE @List NVARCHAR(256)
DECLARE @SqlString NVARCHAR(2000)

SET @List = '1,2,3,4,5'
SELECT @SqlString = 'SELECT * FROM [YourTable] WHERE [YourColumn] IN (' + @List + ')'

EXEC sp_executesql @SqlString

Open in new window

0
 
LVL 26

Author Comment

by:Nick67
ID: 40594752
I am busy reading this at the moment
http://www.sommarskog.se/arrays-in-sql-2005.html
In theory, I think what you've suggested might certainly work.

But I am passing in the list from ASP.Net, so it would look different than what you have suggested.
And it is all then dynamic SQL, with the naughtiness that dynamic SQL comes with
(Injection attacks, lack of optimization etc.)
Is that really the only way to go?
0
 
LVL 26

Author Comment

by:Nick67
ID: 40594997
Ok.
From the source cited above I created a table-valued function to unpack as comma separated list into a table valued parameter.
-- Description:	Function to unpack a csv to a table valued parameter
-- =============================================
ALTER FUNCTION [dbo].[ufnCSVtoTVP] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
   RETURN
END

Open in new window


and I then wrestled the stored procedure into a form that doesn't use an IN but uses and INNER JOIN on the table-valued parameter that the function returns.

Alter PROCEDURE [dbo].[qryFlowLineReadingsByList] 
	-- Add the parameters for the stored procedure here
	@List nvarchar(4000) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @JobID int
declare @pieceID int
Declare @nominal nvarchar(max)
	
SELECT tblFlowlineReadings.JobID, 
tblFlowlineReadings.ReadingID, 
tblFlowlineReadings.PieceID,

[UT1] AS [UT-1],
[UT2] AS [UT-2], 
[UT3] AS [UT-3], 
[UT4] AS [UT-4], 
[UT5] AS [UT-5], 
[UT6] AS [UT-6], 
[UT7] AS [UT-7], 
[UT8] AS [UT-8],  
tblPieces.SerialNumber, 
tblPartDescriptions.PartDescription, 
tblMakes.Manufacturer, 
tblInsDetails.[WO#], 
tblInsDetails.Date, 
tblClients.[Client Name], 
tblCertResults.Unit, 
tblPartDescriptions.PartID,
dbo.ufnCriteriaByPieceType(tblFlowlineReadings.PieceID,1) as Nominal,
dbo.ufnCriteriaByPieceType(tblFlowlineReadings.PieceID,2) as Minimum
From
tblClients INNER JOIN
tblPartDescriptions RIGHT OUTER JOIN
tblMakes RIGHT OUTER JOIN
tblFlowlineReadings INNER JOIN
tblPieces ON tblFlowlineReadings.PieceID = tblPieces.PieceID ON tblMakes.MakeID = tblPieces.MakeID ON 
tblPartDescriptions.PartID = tblPieces.PartID LEFT OUTER JOIN
tblTypes ON tblPieces.TypeID = tblTypes.TypeID INNER JOIN
tblInsDetails ON tblFlowlineReadings.JobID = tblInsDetails.JobID INNER JOIN
tblCertResults ON tblInsDetails.JobID = tblCertResults.JobID ON tblClients.[Client ID] = tblInsDetails.ConsigneeID INNER JOIN
ufnCSVtoTVP(@List) TheList ON tblPieces.PieceID = TheList.number

Open in new window


One last wrinkle I have to work out is that I want only a single record returned for each PieceID and desired one is the one where tblInsDetails.JobID is the largest value for each piece

Are there gotchas or other things I should be doing to make this work more efficiently?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40596531
Instead of joining directly to tblInsDetails use a derived table instead. Use ROW_NUMBER() in that derived table to choose the largest value e.g.

LEFT JOIN (
   select *, row_number() over(partition by abc order by JobID DESC) as rn
   from tblInsDetails
  ) as j  ON tblFlowlineReadings.JobID = j.JobID and j.rn = 1

BUT

"abc" is in the above because I'm not sure what to partition by. Then:

You are using nested join syntax that mixes left and right outer joins; this is difficult to follow and makes your code way harder to maintain in the longer term. The following is a bit of a guess but I think your existing joins could be simplified as:

FROM tblPieces 
INNER JOIN dbo.ufnCSVtoTVP(@List) THELIST ON tblPieces.PieceID = THELIST.number
INNER JOIN tblPartDescriptions ON tblPieces.PartID = tblPartDescriptions.PartID
LEFT OUTER JOIN tblMakes ON tblPieces.MakeID = tblMakes.MakeID
LEFT OUTER JOIN tblTypes ON tblPieces.TypeID = tblTypes.TypeID
LEFT OUTER JOIN tblFlowlineReadings ON tblPieces.PieceID = tblFlowlineReadings.PieceID
LEFT OUTER JOIN tblInsDetails ON tblFlowlineReadings.JobID = tblInsDetails.JobID
LEFT OUTER JOIN tblClients ON tblInsDetails.ConsigneeID = tblClients.[Client ID]
LEFT OUTER JOIN tblCertResults ON tblInsDetails.JobID = tblCertResults.JobID

Open in new window


As you are after the largest value of tblInsDetails.JobID, but that field is used in a join to tblFlowlineReadings.JobID  why do you not seek the highest tblFlowlineReadings.JobID  instead?
0
 
LVL 26

Author Comment

by:Nick67
ID: 40596554
'why do you not seek the highest tblFlowlineReadings.JobID  instead'
You are right, that is ultimately what is central, one record from tblFlowlineReadings for each PieceID in the List(of Int32) and that one record being the one with the largest JobID.

'You are using nested join syntax that mixes left and right outer joins; this is difficult to follow and makes your code way harder to maintain in the longer term. '
I come from the MS Access end of things where the Query By Example editor is one of the most wonderful bits of UI ever created by MS -- until it isn't.  I know what my SQL needs to look like, and most times what it needs to read like too.  Why does any code generator create a RIGHT JOIN?  They're are just LEFT JOINS with the ON clause reversed, aren't they?  This SQL was complex enough, I haven't yet gone in by hand to clean it up -- and perhaps break it, too.  All INNER JOINS are simple enough to hand edit.  This has a fair bit of complexity.

And it's complexity that comes from the facts on the ground.  For us, JobiD is the key atomic organizing principal.  A client brings in a bunch of stuff to be inspected.  We create a job, attach the particulars, and the details fan out into the supporting tables.  For the client, it is the PieceID that is atomic.  A bunch of pieces come into to be inspected together, but after inspection, they are like oil on rough water--broken apart and dissipated and re-combined willy-nilly.  So the client wants to pull information based on PieceID -- but ownership, dates, what batch they were inspected in conjunction with all relate solely through JobID.

I think I am probably looking at another INNER JOIN on a derived table of SELECT PieceID, Max(JobID) from tblFlowLineReadings to get the job done right.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599644
" a RIGHT JOIN?  They're are just LEFT JOINS with the ON clause reversed, "

That is not correct. the 2 joins below are exactly equal and neither of them is a right join

LEFT OUTER JOIN table_fred ON main_table.foreignPK = table_fred.PK
LEFT OUTER JOIN table_fred ON table_fred.PK = main_table.foreignPK

the 2 sides of an equals operator can be reversed in any situation and it has NO effect, in the same way that these can be reversed and have no effect:

1 = 1
A = A

HOWEVER a RIGHT JOIN completely alters the relationship impact (red indicates the selected data)

compare left-v-right outer joins
inspired by: visual guide to joins

You also need to be aware that NESTED joins can completely alter the outcome so please test/check your existing query structure very carefully.
0
 
LVL 26

Author Comment

by:Nick67
ID: 40599668
Perhaps I worded that poorly.
The right join in your Venn diagram can be written
Select * From TableB B LEFT OUTER JOIN TableA A on B.Key = A.Key

In general, there isn't a LEFT OUTER JOIN that can't be re-written as a RIGHT OUTER JOIN, correct, and vice versa?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599701
That is correct, flipping the table sequence does make them equal.

BUT please do not overlook the NESTED JOINS which (pl. believe me) can RADICALLY alter the outcome.

So, these are reasons why I suggested a re-write of the joins.
A person following you to support the existing query will have problems understanding it.


(nested joins are not "wrong", it's just "unusual" and anything unusual is hard to maintain)

From
tblClients INNER JOIN
tblPartDescriptions RIGHT OUTER JOIN
tblMakes RIGHT OUTER JOIN
tblFlowlineReadings

note how there is no details of those joins until:

 INNER JOIN
tblPieces ON tblFlowlineReadings.PieceID = tblPieces.PieceID ON tblMakes.MakeID = tblPieces.MakeID ON
tblPartDescriptions.PartID = tblPieces.PartID

these are "nested joins"
0
 
LVL 26

Author Comment

by:Nick67
ID: 40599719
It's funny.
I've got that query in the SSMS editor and It just will NOT make all three of those JOINS into Left Joins in the GUI view
Two Lefts and a Right or Two rights and a Left.
Weird
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599735
I don't use GUI's for these things so I can't really comment. You may need to use subqueries to alter how the GUI works (but this is a guess).

What matters is that you get the correct results; I don't have your tables/data etc. and hence cannot make that determination. I simply point out that nested joins can make quite a difference in outcome.
0
 
LVL 26

Author Comment

by:Nick67
ID: 40599860
EE has no fiddles -- not that I really have ever used fiddles -- just that other MVE's have pined for them.
I'm thinking I should probably break the query into chunks and alias them.
The MAX() is going to require a Group By and I never enjoy those.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599902
http://sqlfiddle.com is very useful - but does go down from time to time.

If I use it I don't rely on the URL being "up" and copy all script I need for an answer into the EE comment.
0
 
LVL 26

Author Comment

by:Nick67
ID: 40601694
So I've reworked the stored procedure
ALTER PROCEDURE [dbo].[qryFlowLineReadingsByList] 
	-- Add the parameters for the stored procedure here
	@List nvarchar(4000) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @JobID int
declare @pieceID int
Declare @nominal nvarchar(max)                     
                      
SELECT
	tFLR.JobID, tFLR.ReadingID, tFLR.PieceID, 
	tFLR.UT1 AS [UT-1], tFLR.UT2 AS [UT-2], tFLR.UT3 AS [UT-3], tFLR.UT4 AS [UT-4], 
	tFLR.UT5 AS [UT-5], tFLR.UT6 AS [UT-6], tFLR.UT7 AS [UT-7], tFLR.UT8 AS [UT-8], 
	tPieces.SerialNumber, tPieces.PartDescription, tPieces.Manufacturer, tPieces.PartID, tPieces.TypeName,
	tblInsDetails.WO#, tblInsDetails.Date, tblClients.[Client Name], tblCertResults.Unit,  
	dbo.ufnCriteriaByPieceType(tFLR.PieceID, 1) AS Nominal, dbo.ufnCriteriaByPieceType(tFLR.PieceID, 2) AS Minimum
FROM
		(SELECT tblPieces.PieceID, tblPieces.SerialNumber,tblPartDescriptions.PartDescription, tblTypes.TypeName, tblMakes.Manufacturer, tblPieces.PartID
		FROM
					tblPieces LEFT OUTER JOIN
					tblPartDescriptions ON tblPieces.PartID = tblPartDescriptions.PartID LEFT OUTER JOIN
					tblTypes ON tblPieces.TypeID = tblTypes.TypeID LEFT OUTER JOIN
					tblMakes ON tblPieces.MakeID = tblMakes.MakeID) as tPieces Inner Join
					                      
		(SELECT * from tblFlowlineReadings) as tFLR On tPieces.PieceID = tFLR.PieceID Inner Join
					
		tblInsDetails on tblInsDetails.JobID = tFLR.JobID Inner Join 
		tblClients on tblClients.[Client ID]= tblInsDetails.ConsigneeID Inner Join
		tblCertResults on tblInsDetails.JobID = tblCertResults.jobid Inner Join
					
		(SELECT PieceID, MAX(JobID) as TheMax from tblFlowlineReadings group by PieceID) as LastJobs
		On LastJobs.TheMax = tFLR.JobID AND LastJobs.PieceID = tflr.PieceID INNER JOIN
					
		dbo.ufnCSVtoTVP(@List) AS TheList ON tPieces.PieceID = TheList.number
					                 	
END

Open in new window

This corrals the LEFT JOINs in together and leaves everything else as an INNER JOIN
(The pasting really did a number on the formatting)

Now I'll have to tweak some data and test it.
0
 
LVL 26

Author Comment

by:Nick67
ID: 40601758
I have a question about this piece
(SELECT PieceID, MAX(JobID) as TheMax from tblFlowlineReadings group by PieceID) as LastJobs
            On LastJobs.TheMax = tFLR.JobID AND LastJobs.PieceID = tflr.PieceID


I am looking to get the largest JobID for each Piece ID.
This works, but is the compound ON condition a bad thing?
Should that be twisted into a WHERE predicate?

And this is the limiting factor to everything
INNER JOIN dbo.ufnCSVtoTVP(@List) AS TheList ON tPieces.PieceID = TheList.number

In reading about how to actually write SQL, rather than using a GUI, there are suggestions that server can do funny things with the execution plan depending on how things are structured.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40602006
>>"is the compound ON condition a bad thing?"
no

there can be bad things in joins but this isn't one of them.
however, that said, the best joins involve indexed fields.

----
There are 2 basic techniques to get the "latest" of something. You are using one. The other is ROW_NUMBER() OVER()

e.g.

select * from (
SELECT PieceID, JobID , ROW_NUMBER() OVER(partition by PieceID ORDER BY some_field DESC) as RN
from tblFlowlineReadings
) as LastJobs
            On LastJobs.TheMax = tFLR.JobID AND LastJobs.PieceID = tflr.PieceID AND RN=1

                nb: you cannot directly reference row_number() in a where clause

This technique allows you to get the whole record associated with the "latest" whereas the former technique cannot do that without another join.

---
the limiting factor to everything
INNER JOIN dbo.ufnCSVtoTVP(@List) AS TheList ON tPieces.PieceID = TheList.number

yes.

you may want to ask another question about that function. There are MANY to choose from and you might be using the most efficient - or you might not be
-----

>>" there are suggestions that server can do funny things with the execution plan depending on how things are structured."

correct, but it's way too big a point to discuss here
DO use execution plans to understand/optimize your big/complex queries (& you can do it for all queries if you insist)
0
 
LVL 26

Author Comment

by:Nick67
ID: 40602094
There are lots of ways to create that function indeed.
The link above is from Erland Sommarskog, whose been a SQL Server MVP for quite a while.
Even for you, that post may be worth perusing.  It's one of those seminal texts, like TheAccessWeb, or AllenBrowne.com in the MS Access field
And maybe you've already see it.

I picked the method for that function that a)would perform well, b)took a string I knew I wanted to construct (despite the small performance hit of comma-separated values, I didn't want to send in or create a space-delimited string for posterity's sake -- because EVERYBODY gets a csv format) and c) that I could understand.

The execution plans look good.
Now it's time to try it out.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40602113
very good, and yes I'm familiar with Erland's work

looking forward to the outcome
0
 
LVL 26

Author Closing Comment

by:Nick67
ID: 40615117
Hey man,
Thanks for the hand and the explanations.
It's coming together!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40615122
Pleased to hear that! Cheers, Paul
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now