Query - In a Stored Procedure 1

[topub]

Hi there,

I have a webpage that calls a storedProcedure with @temp parameter

ex: @temp = '1,2,3,4'

--********** START PROCEDURE ************************

create proc usp_tt (@temp as varchar(12))

select *
from t1
inner join t2
on t1.ID = t2.ID
and t2.col in (Case
when @temp = '0' then
t2.col
else
(select int_col
from call_function_that_returns_rows_for_given_string(@temp))
End)

--**********************************

NOTE:
"call_function_that_returns_rows_for_given_string" this function returns rows for a given comma delimited string i.e. '1,2,3,4,5,6' into

int_col
1
2
3
4
5
6

I am breaking my head over this....
I need a fresh set of eyes and brain to look at this.... may be there is a simple solution

thanks a lot,
_ub

 

[AlexCuse]

Did you try removing the quotes around the zero so @temp = 0?

You also might not need the function to return rows, as you could say where t2.col in (1,2,3,4,5,6)

If it is not something this simple, I'm sure someone else will have better answer.

Hope it helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[bborissov]

create proc usp_tt (@temp as varchar(12))
IF RIGHT(@temp,1) <> ','
   SET @temp = @temp + ','

select *
from t1
inner join t2
   on t1.ID = t2.ID
   and (@temp = '0,' OR @Test LIKE "%"+CAST(t2.col AS varchar(??))+',%')

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[topub]

Thanks AlexCuse, bborissov.....

@temp could be '0' or '1,2,3,4'. It is compared against table with column data type as Int

[b]Credit goes to Anit Sen:[/b]

SELECT *
  FROM t1
 INNER JOIN t2
    ON t1.id = t2.id
 WHERE @temp = '0'
    OR ( @temp <> '0'
   AND t2.col IN ( SELECT c FROM UDF ( @temp )  ) ;

 

[SQLSister]

YOu want to pass a string of numbers?
Look in this FAQ for some advice

http://www.tek-tips.com/faqs.cfm?fid=5207

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[topub]

thanks SQLSister.

The function that I have does exactly that.

thanks everyone,
_ub