User-Defined Function and IN?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

User-Defined Function and IN??

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-02-11 : 19:23:46

What would be the equivalent of "IN" in an user-defined function. I'm trying to say:

if @MyValue IN @TheirLookingFor
begin
go code
end

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-11 : 20:21:51

You can use "in" in a udf without problems.
Sounds more like you are just trying to use it incorrectly. Is @TheirLookingFor a csv string and @MyValue an int? if so then

if ',' + @TheirLookingFor + ',' like '%,' + convert(varchar(20),@MyValue) + ',%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-02-11 : 20:35:39

No, @MyValue and @TheirValue are both varchar and yes @TheirValue is a csv.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-11 : 20:45:33

Then just omit the convert.

if ',' + @TheirLookingFor + ',' like '%,' + @MyValue + ',%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-02-11 : 20:56:33

Thanks, nr. Will that work even if two of the values are similar (e.g. war,war hero)? Here's the completed UDF:

CREATE FUNCTION dbo.RankPoints3 (@MyValue varchar(50),
@MyLookingFor varchar(1000),
@MyLookingForRequired bit,
@TheirValue varchar(50),
@TheirLookingFor varchar(1000),
@TheirLookingForRequired bit)
returns int

as

begin
declare @Rank int
set @Rank = 0

if @TheirLookingFor = 'Any'
begin
set @Rank = @Rank + 5
end
else
if ',' + @TheirLookingFor + ',' like '%,' + @MyValue + ',%' and @TheirLookingForRequired = 1
begin
set @Rank = @Rank + 10
end
else
if ',' + @TheirLookingFor + ',' like '%,' + @MyValue + ',%'
begin
set @Rank = @Rank + 5
end

if @Rank = 0 and @TheirLookingForRequired = 1
begin
return -10000
end

if @MyLookingForRequired = 0
begin
if @MyLookingFor = 'Any'
begin
set @Rank = @Rank + 5
end
else
begin
if ',' + @MyLookingFor + ',' like '%,' + @TheirValue + ',%'
begin
set @Rank = @Rank + 5
end
end
end

return @Rank
end

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-11 : 23:58:13

>> Thanks, nr. Will that work even if two of the values are similar (e.g. war,war hero)?

Yes that's the point of adding the commas

it for 'war' it will check
',war,war hero,' like '%,war,%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 00:01:10

If this is a function to be used on a column in a recordset as part of a select statement then it will probably be very slow.
I wouldn't recommend it unless the number of rows tested is very small.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-02-12 : 00:06:03

You're right it's not very fast. It has to be done, though. If you have any suggestions, I would appreciate it.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 00:20:16

I would do the processing in a stored proc so that the like can act on the column rather than passing each value to a function.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-02-12 : 01:03:12

Wouldn't that involve using if statements in a select?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 01:07:09

Nope - you can use case statements or the where clause.
If necessary code multiple statements to populate a temp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-02-12 : 01:28:22

Not sure I understand...could you post an example?

derketo
Starting Member

28 Posts

Posted - 2005-02-12 : 02:41:59

Think I got it...

case
when ',' + MV.TTV + ',' like '%,' + V.MTV + ',%' and MV.TVRequired = 1 then 10
when ',' + MV.TTV + ',' not like '%,' + V.MTV + ',%' and MV.TVRequired = 1 then -10000
when ',' + MV.TTV + ',' like '%,' + V.MTV + ',%' then 5
else 0
end +
case
when ',' + V.TTV ',' like '%,' + MV.MTV + ',%' and V.TVRequired = 1 then 10
when ',' + V.TTV + ',' not like '%,' + MV.MTV + ',%' and V.TVRequired = 1 then -10000
when ',' + V.TTV + ',' like '%,' + MV.MTV + ',%' then 5
else 0
end

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 09:35:43

Looks like the right sot of thing but maybe this would be better


case 
    when MV.TVRequired = 1 then 
          case when ',' + MV.TTV + ',' like '%,' + V.MTV + ',%' 
                 then 10
                 else -10000
          end
    when ',' + MV.TTV + ',' like '%,' + V.MTV + ',%' 
        then 5
    else 0
end 
+...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-02-12 : 15:09:35

Thanks, nr. One more thing. Should I use this same code in the where close or use a derived table with one statement?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 15:39:48

Try them both and look a the query plan.
Also consider making it two statements and populating a tenmp table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derketo
Starting Member

28 Posts

Posted - 2005-02-12 : 17:02:24

If I have "car,boat,house" and "car,boat" I was going to use parsename...anyone have any idea on how to check how many items are in a comma delimited list? Then I can use a case statement based on a max list size. Or would it be better just to run through all of them even though they might be null?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-12 : 18:21:32

will this do?

declare @str varchar(20)
set @str = '123,456,789'
select len(@str) - len(replace(@str, ',', '')) + 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources