Pass parameters to complex procedure.

Hello, I have this Stored procedure, a very complex one, and it works fine with the parameters wrote on it. But when I try to change the number to variables @.param1, I got errors, can somebody tell me how to replace this please.

DECLARE @.return_value int,
@.lvaIndicador varchar(4000),
@.ecuacion varchar(4000),
@.numerador int,
@.denominador int,
@.lvaIndicador2 varchar(4000),
@.ecuacion2 varchar(4000),
@.numerador2 int,
@.denominador2 int,
@.porcentajeCumple numeric

EXEC @.return_value = [dbo].[SP_CUMPLENCONNIVEL]
@.nmtipoCompetencia = N'12,13,14',
@.nmdivisioncorporativa = N'18,19,20',
@.nmciclo = 9,
@.nmcicloCompara = 12,
@.nmempresa = 72,
@.nmcargo = N'20',
@.lvaIndicador = @.lvaIndicador OUTPUT,
@.ecuacion = @.ecuacion OUTPUT,
@.numerador = @.numerador OUTPUT,
@.denominador = @.denominador OUTPUT,
@.lvaIndicador2 = @.lvaIndicador2 OUTPUT,
@.ecuacion2 = @.ecuacion2 OUTPUT,
@.numerador2 = @.numerador2 OUTPUT,
@.denominador2 = @.denominador2 OUTPUT,
@.porcentajeCumple = 60

Could you explain your problem at bit more in deatil, I am not quite sure of somebody understood that right now.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

When I put that query on the screen of reporting services it works fine because it has the multivalue parameters wrote on the query.

But when I try to change the query to this I got some syntax errors

DECLARE @.return_value int,
@.lvaIndicador varchar(4000),
@.ecuacion varchar(4000),
@.numerador int,
@.denominador int,
@.lvaIndicador2 varchar(4000),
@.ecuacion2 varchar(4000),
@.numerador2 int,
@.denominador2 int,
@.porcentajeCumple numeric

EXEC @.return_value = [dbo].[SP_CUMPLENCONNIVEL]
@.nmtipoCompetencia = N'@.nmtipoCompetencia ',
@.nmdivisioncorporativa = N'@.nmdivisioncorporativa',
@.nmciclo = @.nmciclo,
@.nmcicloCompara = @.nmcicloCompara,
@.nmempresa = @.nmempresa,
@.nmcargo = N'@.nmcargo',
@.lvaIndicador = @.lvaIndicador OUTPUT,
@.ecuacion = @.ecuacion OUTPUT,
@.numerador = @.numerador OUTPUT,
@.denominador = @.denominador OUTPUT,
@.lvaIndicador2 = @.lvaIndicador2 OUTPUT,
@.ecuacion2 = @.ecuacion2 OUTPUT,
@.numerador2 = @.numerador2 OUTPUT,
@.denominador2 = @.denominador2 OUTPUT,
@.porcentajeCumple = @.porcentajeCumple

like nmciclo was not declared.

|||Look in the declare syntax, you did not declare the nmciclo parameter. If you want to pass the parameter to the procedure you either have to declare and eventually fill it or pass a static parameter to the procedure.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I already declared it and put the stored procedure in this way.

DECLARE @.return_value int,
@.lvaIndicador varchar(4000),
@.ecuacion varchar(4000),
@.numerador int,
@.denominador int,
@.lvaIndicador2 varchar(4000),
@.ecuacion2 varchar(4000),
@.numerador2 int,
@.denominador2 int,
@.porcentajeCumple numeric,
@.nmciclo int, @.nmciclocompara int,@.nmempresa int

EXEC @.return_value = [dbo].[SP_CUMPLENCONNIVEL]
@.nmtipoCompetencia = N'@.nmtipoCompetencia ',
@.nmdivisioncorporativa = N'@.nmdivisioncorporativa',
@.nmciclo = @.nmciclo,
@.nmcicloCompara = @.nmcicloCompara,
@.nmempresa = @.nmempresa,
@.nmcargo = N'@.nmcargo',
@.lvaIndicador = @.lvaIndicador OUTPUT,
@.ecuacion = @.ecuacion OUTPUT,
@.numerador = @.numerador OUTPUT,
@.denominador = @.denominador OUTPUT,
@.lvaIndicador2 = @.lvaIndicador2 OUTPUT,
@.ecuacion2 = @.ecuacion2 OUTPUT,
@.numerador2 = @.numerador2 OUTPUT,
@.denominador2 = @.denominador2 OUTPUT,
@.porcentajeCumple = @.porcentajeCumple

Now I got a complex error that must be something inside the stored procedure.

TITLE: Microsoft Report Designer

An error occurred while executing the query.
Must declare the scalar variable "@.nmcargo".
The variable '@.lcuPersona' does not currently have a cursor allocated to it.
Error converting data type nvarchar to numeric.

ADDITIONAL INFORMATION:

Must declare the scalar variable "@.nmcargo".
The variable '@.lcuPersona' does not currently have a cursor allocated to it.
Error converting data type nvarchar to numeric. (Microsoft SQL Server, Error: 137)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=137&LinkId=20476

BUTTONS:

OK

The strange thing it works fine with the parameters wrote there.. Do you want me to paste the 500 lines stored procedure?

|||Hi,

no I definetely don′t want you to post that code here, but if there is no way to avoid it :-)

Why are you doing this thing here: N'@.nmcargo',

Seems like you want to pass a variable, but you use it like a string ?!

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||nmcargo and the parameters that have the N in front of it are multivalue parameters that must be passed separated by commas|||

Re ... SQL Server Rerporting Services SSRS "Must declare the scalar variable @.variable"

I had the VS report working fine and as soon as I deployed the report and ran it under Report Manager ... Boom error as above.

Thanks to hint on a completely unrelated Google groups post, I checked the DS (Data Source) being used by VS (Visual Studio) and the one being used by RM...

Guess what - VS DS was using a SQL Server Connection string and the RM DS was using ODBC or OLEDB ...

Changed the DS via RM to be SQL Server and now it all works fine ...

Gotta love the quality of those error message -