Simple SQL using IN

Question

Simple SQL using IN

Brendan Collins

SSC Eights!

Points: 852
More actions
September 21, 2004 at 4:12 am

#59611

HI
I am using the SQL code below to return a list of contacts associated with a page. During testing the @LearnID was set to 2. This returned any records where there is only one value in puTOP_SECTIONID field = 2. The problem is when there is multiple values in the field, such as when it contains "8,10,2,4", it doesn't return that record even though 2 is in it. puTOP_SECTIONID is a char(100) field.
Anyone know a workaround I could use?
Thanks all.
Brendan
CREATE PROCEDURE spGetLearnImproveContactsByID
@LearnID char(100)
AS
Begin
  SELECT rtrim(REGION) As REGION, rtrim(CONTACT_NAME) As CONTACT_NAME, rtrim(TELEPHONE) As TELEPHONE, rtrim(CONTACT_EMAIL) AS CONTACT_EMAIL, CONTACT_ORDERBY
  FROM TBLCONTACT, TBLREGION
  WHERE TBLCONTACT.puTBLREGION = TBLREGION.puTBLREGION And @LearnID IN (puTOP_SECTIONID)
  ORDER BY CONTACT_ORDERBY, REGION, CONTACT_NAME
End
GO

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

What you're asking for is how to tokenize the input variable. Not easily done in Transact SQL, and tend to get pretty ugly when attempted. It's not optimal and don't scale well.

One technique is to parse the input variable, look for the breaker charachters (comma?) and one by one store the values in a temptable. Then rewrite the select query to join against the temptable instead of using the IN clause..

Not easy, not pretty, slow and ugly but it can be done.

Another option is to not accept a string of several arguments in the same variable. (it's a design issue - don't design this way)

In any case, good luck

/Kenneth

Richard Clarke-156683 SSC Enthusiast Points: 190 More actions · Answer 2

Hi Brendan,

The reason it fails is that the puTOP_SECTIONID is treated as a single value of "8,10,2,4" and not individual values of 8, 10, 2, 4.

The IN clause is really just a short cut for multiple OR statements.

These statement are the same to SQL.

SELECT region FROM table WHERE field = 1 OR field = 2

SELECT region FROM table WHERE field IN ( 1, 2 )

To achive what you want I would use Dynamic SQL, but the downside is that the performance will be affected. How much will depend on the size of the table etc.

CREATE PROCEDURE spGetLearnImproveContactsByID

@LearnID char(100)

AS

Begin

DECLARE @sql VARCHAR(1000)

SELECT "SELECT rtrim(REGION) As REGION, rtrim(CONTACT_NAME) As CONTACT_NAME, rtrim(TELEPHONE) As TELEPHONE, rtrim(CONTACT_EMAIL) AS CONTACT_EMAIL, CONTACT_ORDERBY"

+ "FROM TBLCONTACT, TBLREGION"

+ "WHERE TBLCONTACT.puTBLREGION = TBLREGION.puTBLREGION And @LearnID IN (" + puTOP_SECTIONID + ")"

+ "ORDER BY CONTACT_ORDERBY, REGION, CONTACT_NAME"

EXECUTE(@SQL)

End

GO

Regards

Richard.

Brendan Collins SSC Eights! Points: 852 More actions · Answer 3

Have made a seperate table which now links the section to the Contact, but

am now having trouble inserting into this table using SQL. The code I have

(simplified) is:

Declare @ContactID bigint

--Insert contact details into Contact table

insert into [TBLCONTACT] (ContactName)

values (rtrim(@Name))

--Retrieve id of inserted contact

Set @ContactID = @@IDENTITY

--insert into table linking Contact to Section

insert into [TBLCONTACT_TO_TBLSECTION] puTBLCONTACT, puTBLTOP)

values (@ContactID, @Section)

What I need is to loop the last two lines of SQL so that a new record is

inserted for each value passed in the @Section parameter. So if @Section

contained "3,8,10" Then it would insert 3 records.

Any ideas?

Brendan

David Burrows SSC Guru Points: 65143 More actions · Answer 4

You could use this in the where clause

CHARINDEX(',' + CAST(@LearnID as varchar) + ',',',' + puTOP_SECTIONID) + ',') > 0

but you will sacrifice performance

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65143 More actions · Answer 5

The following will anser you last question

WHILE CHARINDEX(',',@Section) > 0

BEGIN

insert into [TBLCONTACT_TO_TBLSECTION] puTBLCONTACT, puTBLTOP)

values (@ContactID, LEFT(@Section,CHARINDEX(',',@Section)-1)

SET @Section = SUBSTRING(@Section,CHARINDEX(',',@Section)+1,LEN(@Section))

END

insert into [TBLCONTACT_TO_TBLSECTION] puTBLCONTACT, puTBLTOP)

values (@ContactID, @Section)

Far away is close at hand in the images of elsewhere.
Anon.

EdVassie SSC Guru Points: 60478 More actions · Answer 6

Apart from anything else, your clause '@LearnID IN (puTOP_SECTIONID)' is the wrong way round. The IN clause works like 'needle IN haystack'. Try rewriting it as 'puTOP_SECTIONID IN (@LearnID)'.

The SP is also intrinsically open to SQL Injection attacks if it is coded as shown. Have a look in the forums for SQL Injection for tips on what the security issues with this are and how to avoid it.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Brendan Collins SSC Eights! Points: 852 More actions · Answer 7

Hi

Thanks to all for your help. I have managed to take something from each reply to get the problem sorted.

Brendan