SQL in(@parameter)

[ezek]

hi guys, this is my first post here.

i have a proc like this
@parcelas, @cod_banco
select from tb_bancos where cod_banco=@cod_ banco and parcelas in (Parcelas)

but it seems that IN(@PARCELAS) does not works
i've found some articles to pass the array into a table but it didnt work too
i use sql server 2005

can anyone help me/??

thanks.

[deepuv04]

hi guys, this is my first post here.

i have a proc like this
@parcelas, @cod_banco
select from tb_bancos where cod_banco=@cod_ banco and parcelas in (Parcelas)

but it seems that IN(@PARCELAS) does not works
i've found some articles to pass the array into a table but it didnt work too
i use sql server 2005

can anyone help me/??

thanks.

if the datatype of the parcles is int in the table then

declare parameter @parcelas as varcahr(max) and send the values seperated by commas

ex : @parcles as '1,2,3,4'

then the following query will work for wat you want

select *
from tb_bancos
where cod_banco=@cod_ banco
and (','+ @parcelas +',') like ('%,'+convert(v archar(20),parc elas)+ ',%'))


if the datatype of the parcles is varchar in the table then
declare parameter @parcelas as varcahr(max) and send the values seperated by commas with single cotes
as
ex: @Parcles as '''abc'',''def' ',''ghi'''

to execute the query first build the query as string and the execute it

declare @SqlQuery as varchar(max)
set @SqlQuery = 'select * from tb_bancos where cod_banco=@cod_ banco and parcelas in (' + @Parcelas +')'
exec (@SqlQuery)

thanks

[renatomt]

if the datatype of the parcles is int in the table then

declare parameter @parcelas as varcahr(max) and send the values seperated by commas

ex : @parcles as '1,2,3,4'

then the following query will work for wat you want

select *
from tb_bancos
where cod_banco=@cod_ banco
and (','+ @parcelas +',') like ('%,'+convert(v archar(20),parc elas)+ ',%'))


if the datatype of the parcles is varchar in the table then
declare parameter @parcelas as varcahr(max) and send the values seperated by commas with single cotes
as
ex: @Parcles as '''abc'',''def' ',''ghi'''

to execute the query first build the query as string and the execute it

declare @SqlQuery as varchar(max)
set @SqlQuery = 'select * from tb_bancos where cod_banco=@cod_ banco and parcelas in (' + @Parcelas +')'
exec (@SqlQuery)

thanks

thx for the reply

i tried the second one but it seems that the parameter @codbanco doesnt work
i tried to put the quotations like
ALTER PROCEDURE [dbo].[GetLawyers] ( @Parcelas VARCHAR(100),@c odbanco int)
AS

DECLARE @SQL VARCHAR(2000)

SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE cod_banco = ' + @codbanco + ' and [PARCELAS] IN (' + @Parcelas + ')'
EXECUTE (@SQL)

when i execute the proc the following error occurs

Msg 245, Level 16, State 1, Procedure GetLawyers, Line 7
Conversion failed when converting the varchar value 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE cod_banco = ' to data type int.

if anyone can help

thx

[deepuv04]

thx for the reply

i tried the second one but it seems that the parameter @codbanco doesnt work
i tried to put the quotations like
ALTER PROCEDURE [dbo].[GetLawyers] ( @Parcelas VARCHAR(100),@c odbanco int)
AS

DECLARE @SQL VARCHAR(2000)

SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE cod_banco = ' + @codbanco + ' and [PARCELAS] IN (' + @Parcelas + ')'
EXECUTE (@SQL)

when i execute the proc the following error occurs

Msg 245, Level 16, State 1, Procedure GetLawyers, Line 7
Conversion failed when converting the varchar value 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE cod_banco = ' to data type int.

if anyone can help

thx

try the following

ALTER PROCEDURE [dbo].[GetLawyers] ( @Parcelas VARCHAR(max),@c odbanco int)
AS
begin
DECLARE @SQL VARCHAR(max)

SET @SQL = 'SELECT * FROM [dbo].[TB_VALOR_BANCOS]
WHERE cod_banco = ' + convert(varchar (10),@codbanco) + ' and [PARCELAS] IN (' + @Parcelas + ')'
EXECUTE (@SQL)
end

exec GetLawyers '''abc'',''def' ',''ghi''',1

thanks