Link to home
Start Free TrialLog in
Avatar of Nick67
Nick67Flag for Canada

asked on

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

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?
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

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

Avatar of Nick67

ASKER

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?
Avatar of Nick67

ASKER

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?
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?
Avatar of Nick67

ASKER

'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.
" 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)

User generated image
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.
Avatar of Nick67

ASKER

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?
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"
Avatar of Nick67

ASKER

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
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.
Avatar of Nick67

ASKER

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.
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.
Avatar of Nick67

ASKER

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.
Avatar of Nick67

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nick67

ASKER

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.
very good, and yes I'm familiar with Erland's work

looking forward to the outcome
Avatar of Nick67

ASKER

Hey man,
Thanks for the hand and the explanations.
It's coming together!
Pleased to hear that! Cheers, Paul