"Select Where In" using a parameter?

Question

Post reply

"Select Where In" using a parameter?

Viewing 6 posts - 16 through 20 (of 20 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Interesting. Thanks for the info on the while method. 🙂

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 2

GSquared (3/3/2008)
Just did some tests.
The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.

Any bets? 😉 Post your test data please, the Numbers Table code you used for the split, and the XML code you used for the split and we'll see 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 3

Jeff Moden (3/31/2008)
GSquared (3/3/2008)
Just did some tests.
The XML version is faster, significantly so, than the Numbers table version, for parsing out a string.
Any bets? 😉 Post your test data please, the Numbers Table code you used for the split, and the XML code you used for the split and we'll see 🙂

BWAA-HAA!! Still waiting for this, Gus... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arshad7887 SSC Rookie Points: 45 More actions · Answer 4

To Run this kind of query first you need to create UDF(user defined function)

SPLIT Varchar in sql server

User Defined Method

CREATE FUNCTION SplitString

(

-- Add the parameters for the function here

@myString varchar(500),

@deliminator varchar(10)

)

RETURNS

@ReturnTable TABLE

(

-- Add the column definitions for the TABLE variable here

[id] [int] IDENTITY(1,1) NOT NULL,

[part] [varchar](50) NULL

)

AS

BEGIN

Declare @iSpaces int

Declare @part varchar(50)

--initialize spaces

Select @iSpaces = charindex(@deliminator,@myString,0)

While @iSpaces > 0

Begin

Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

Insert Into @ReturnTable(part)

Select @part

Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))

Select @iSpaces = charindex(@deliminator,@myString,0)

end

If len(@myString) > 0

Insert Into @ReturnTable

Select @myString

RETURN

END

GO

Now Run this query

RUN The query

select * From SplitString('Mohammed**Arshad**Shaikh','**')

You may use it in where clause also.

Declare @Paramlist varchar(50)

Set @Paramlist = '1,2,3'

SELECT * FROM Customer

WHERE CUSTOMERId In(select * From SplitString(@paramlist,','))

This will work sure.

Insha Allah..

🙂

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 5

arshad7887 (8/20/2011)
To Run this kind of query first you need to create UDF(user defined function)
SPLIT Varchar in sql server
User Defined Method
CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)
--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0
Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
Insert Into @ReturnTable(part)
Select @part
Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))
Select @iSpaces = charindex(@deliminator,@myString,0)
end
If len(@myString) > 0
Insert Into @ReturnTable
Select @myString
RETURN
END
GO
Now Run this query
RUN The query
select * From SplitString('Mohammed**Arshad**Shaikh','**')
You may use it in where clause also.
Declare @Paramlist varchar(50)
Set @Paramlist = '1,2,3'
SELECT * FROM Customer
WHERE CUSTOMERId In(select * From SplitString(@paramlist,','))
This will work sure.
Insha Allah..
🙂

No... don't use a While Loop to split strings. They're just too slow. Please see the following article for the code that proves it.

http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

Joe90-646727 (2/19/2008)
Hi there,
this seems like it should be simple, but is a little frustrating!
I have an sp which takes a list of keys like "12,13,14,15,16" as a varchar param.
I would like to execute the following query in the sp:
select * from tableName where id in (@id_list)
I receive the error "Unable to convert between varchar and int".
An easy way to reproduce is to run the following code
declare @id_list varchar(4000)
set @id_list='313352,313353'
select * from tableName where id in (@id_list)
I think dynamic sql might get around it, but I need the sp to be pretty efficient and would prefer not to have to resort to it. Is there a way of doing this without resorting to dynamic SQL?
Any help would be great!

Joe,

I wrote an article about this type of thing:

http://www.sqlservercentral.com/articles/T-SQL/73838/

Todd Fifield