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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-10 : 08:57:00
|
| Mario writes "Hi...I got a trouble with a stored procedure...I have the following code.../* ---------- */declare @filtro_viejo as varchar(58), @filtro_nuevo as varchar(58), @tam_filtro as int, @filtro as varchar(58), @suma as decimal(32,2)set @filtro = char(39) + 'A*,*B*,*C*,*D*,*E*,*G*,*I*,*L*,*N*,*P*,*R*,*S*,*V*,*W*,*' + char(39)set @filtro_viejo = @filtroset @tam_filtro = len(@filtro)set @filtro_nuevo = left(replace(@filtro_viejo,'*',char(39)),len(@filtro_viejo)-3)set @suma = (select sum(cast(new_charges as decimal(32,2)))from temporales.dbo.cst_bill_enero_bwhere cust_type in (@filtro_nuevo) and substring(bill_ref_no,1,6)between 200111 and 200111)print @filtroprint @tam_filtroprint @filtro_nuevoprint @suma/* ---------- */It's all right, but when I try to execute it, the set @suma statement won't work...It does nothing...@filtro is a parameter of my stored procedure received from a form, and it may vary 'cause it has 14 options...(A, B, C, D,...)The thing is when I put the modified parameter in the IN statement, it doesn't work...Please help... thxs...P. D.: The values in the IN statement DOES exist, just in case..." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-10 : 09:32:58
|
| You need to use dynamic sql as you where clause is attempting a literal match on cust_type...<O> |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-10 : 09:58:09
|
| Do a topic search on parsing comma-delimited lists in this forum. You'll find examples of converting such a list to a temp table or table variable which can then be joined with other rowsets to achieve the same effect you're aiming for with your IN().Jonathan Boott, MCDBA |
|
|
|
|
|
|
|
|
Stored Procedure Parameter