Problems sending string parameter to a SP

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 2005 Forums

Transact-SQL (2005)

Problems sending string parameter to a SP

Author

Topic

Rawler
Starting Member

9 Posts

Posted - 2009-03-04 : 15:50:32

Hello,

I am sending this parameter "'01','02','03'" to a variable that is in a IN function [ie: Select * From Table Where Data IN(@Variable)]. It seems that SQL cant reconize the string Im sending and the result of the SP is nothing; so is there a way to send this to a IN condition or I have to change it for OR and = ?

Thanks for reading.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-04 : 15:52:42

Rawler

Show us exactly what you are trying to do it helps to resolve your problem. SQL does recognize what you are sending it, it might be that you are sending it in the wrong thing. Post your stored procedure or code.

subhash chandra
Starting Member

40 Posts

Posted - 2009-03-04 : 16:16:09

Insert the values ('01','02','03') as rows in a table (temporary or table variable) and then using that table with inner join.

Rawler
Starting Member

9 Posts

Posted - 2009-03-04 : 16:17:40

[code]
/*[SP_Dispo_X_Leg3]*/
ALTER PROCEDURE [dbo].[SP_Dispo_X_Leg3]
@ano as nvarchar(4),
@mes as nvarchar(14),
@Lugar as nvarchar(2),
@press as nvarchar(1)
AS
DECLARE
@CodDispon as nvarchar(2),
SET @CodDispon ='99'

IF @press=1
BEGIN

SELECT DisPoS.* , ( DisPoS.ValTotDispo - ISNULL(Otes.saldoOT,0) ) AS SALDOD
From (
SELECT PeMovimientoCb.Ndoc , PeMovimientoCb.Cdoc , PeMovimientoCb.Tdoc ,PeMovimientoCp.Lugar,
PeMovimientoCp.CodRubro, PeMovimientoCb.detalle AS Descripcion, PeMovimientoCb.mes ,
PeMovimientoCb.fecha , PeMovimientoCB.VALORTOTAL, sum(PeMovimientoCp.VALOR) as ValTotDispo
FROM PeMovimientoCb INNER JOIN PeMovimientoCp ON PeMovimientoCb.ano = PeMovimientoCp.ano AND
PeMovimientoCb.mes = PeMovimientoCp.mes AND PeMovimientoCb.Cdoc = PeMovimientoCp.Cdoc AND
PeMovimientoCb.Ndoc = PeMovimientoCp.Ndoc
WHERE (PeMovimientoCb.ano = @ano) AND (PeMovimientoCb.Cdoc = @CodDispon) AND
(PeMovimientoCb.Estado IS NULL OR PeMovimientoCb.Estado = N'') and (PeMovimientoCb.mes IN (@mes))
GROUP BY PeMovimientoCb.Ndoc, PeMovimientoCp.CodRubro, PeMovimientoCb.Cdoc, PeMovimientoCb.Tdoc,
PeMovimientoCb.DETALLE, PeMovimientoCb.mes ,PeMovimientoCp.lugar, PeMovimientoCb.fecha, PeMovimientoCb.VALORTOTAL
HAVING (PeMovimientoCp.lugar LiKe '%'+ @Lugar)

GO
/*SENDING PARAMETERS TO [SP_Dispo_X_Leg3]*/
DECLARE @return_value int

EXEC @return_value = [dbo].[SP_Dispo_X_Leg3]
@ano = N'2009',
@mes = N'''01'',''02'',''03''',
@Lugar = N'01',
@press = N'1'
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 16:18:37

Search for Passing array to stored procedure in here.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-05 : 02:33:01

or

Select * From Table Where ','+@Variable+',' like '%,'+cast(Data as varchar(20))+',%'

Madhivanan

Failing to plan is Planning to fail

Rawler
Starting Member

9 Posts

Posted - 2009-03-05 : 09:03:04

ok thanks for the replies, I think I'm going to use the +@variable+ thing. Thanks to all