SELECT * FROM table WHERE id = @Range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT * FROM table WHERE id = @Range

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2004-11-17 : 11:19:07

Hi guys

What I'm trying to do with a stored procedure is select a range of rows, based upon a where clause.
The magic part is, I want to pass in an array or string of IDs to be used in the WHERE clause.

i.e.

@Range = 2,3,4,5,7

SELECT
*
FROM
tables
WHERE
id = @Range

Would return the same rows as

SELECT
*
FROM
tables
WHERE
id = 2 OR id = 3 OR id = 4 OR id = 5 OR id 7

Can anybody help me out with this ones?
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 11:21:22

parse the CSV string with Split function (search it here...) and join to that.

Go with the flow & have fun! Else fight the flow

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2004-11-17 : 12:15:57

spirit1, I've not had much luck finding the example you are refering. Please could you provide a link?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-17 : 12:24:36

this is the function:


CREATE FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	While (Charindex ( @SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring( @RowData,1,Charindex ( @SplitOn,@RowData)-1)))

		Set @RowData = Substring( @RowData,Charindex ( @SplitOn,@RowData)+1,len( @RowData))
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))

	Return
END

select *
from Table1 t1 inner join dbo.Split('1,2,3,4,5', ',') t2 on t1.Id = t2.Data

In data column are your id to which you join.

Go with the flow & have fun! Else fight the flow

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2004-11-18 : 04:07:29

Great stuff, thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-18 : 09:02:25

Here's a link to an article here at SQLTeam:

http://www.sqlteam.com/item.asp?ItemID=11499

- Jeff

dsdeming

479 Posts

Posted - 2004-11-18 : 09:06:50

The split function is a great way to handle these situations, but you may want to look at something like this:

WHERE CHARINDEX( ',' + CAST( id AS varchar( 12 )) + ',', ',' + @Range + ',' ) > 0

In some cases this syntax is more efficient. Notice the addition of leading and trailing delimiters. This prevents you from finding a one in '8,9,10'.

Dennis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-18 : 09:11:35

Keep in mind, the syntax might be more "efficient" (meaning shorter, I guess), but the SQL statement will be much less efficient to execute using a CHARINDEX or LIKE '%'+A+'%' solution since indexes will not be used.

Having said that, I do sometimes use that type of solution when I am working with very small tables, because it is quick to write and doesn't require a UDF. (i.e., it works on SQL 7.0)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources