Must declare the scalar variable

Question

Must declare the scalar variable

Corobori

Ten Centuries

Points: 1316
More actions
August 5, 2008 at 12:18 pm

#195335

I am getting the following error with the SP shown below
sp_EdicionImpresaDetalles 912,0,0,0,''
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@iEp_E_Id".
ALTER PROCEDURE [dbo].[sp_EdicionImpresaDetalles]
@iEp_Id int=0
,@iEp_E_Id int OUTPUT
,@iEp_R_Id int OUTPUT
,@iEp_Seccion int OUTPUT
,@sEp_Nombre NVARCHAR(100) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sSQL varchar(5000)
set @sSQL = 'SELECT @iEp_E_Id=Ep_E_Id, @iEp_R_Id=Ep_R_Id,@iEp_Seccion=Ep_Seccion,@sEp_Nombre=Ep_Nombre From tblEdicionPDF WHERE Ep_Id=' + CONVERT(varchar(1), @iEp_Id)
exec(@sSQL)
END

Jean-Luc
www.corobori.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

By using dynamic sql your parameters are going out of scope. If this is how you have to do things lookup sp_execute_sql in BOL and use it. You will have to do your parameter declarations again, but it will work. It actually looks like you do not need the dynamic SQL. Try this instead of the dynamic SQL:

[font="Courier New"]SELECT

@iEp_E_Id=Ep_E_Id,

@iEp_R_Id=Ep_R_Id,

@iEp_Seccion=Ep_Seccion,

@sEp_Nombre=Ep_Nombre

FROM

tblEdicionPDF

WHERE

Ep_Id= CONVERT(VARCHAR(1), @iEp_Id)

[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

VG-619426 Hall of Fame Points: 3914 More actions · Answer 2

I think error with dynamic sql

ALTER PROCEDURE [dbo].[sp_EdicionImpresaDetalles]

@iEp_Id int=0

,@iEp_E_Id int OUTPUT

,@iEp_R_Id int OUTPUT

,@iEp_Seccion int OUTPUT

,@sEp_Nombre NVARCHAR(100) OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @sSQL varchar(5000)

set @sSQL = 'SELECT @iEp_E_Id=Ep_E_Id, @iEp_R_Id=Ep_R_Id,@iEp_Seccion=Ep_Seccion,@sEp_Nombre=Ep_Nombre From tblEdicionPDF WHERE Ep_Id= CONVERT(varchar(1), @iEp_Id)'

exec(@sSQL)

END

-VG

Corobori Ten Centuries Points: 1316 More actions · Answer 3

You're right that in this case I do not need dynamic SQL. I'll follow up your suggestion in the BOL for the other case where I really need it.

Jean-Luc
www.corobori.com

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 4

declare @sSQL varchar(5000)
set @sSQL = 'SELECT @iEp_E_Id=Ep_E_Id, @iEp_R_Id=Ep_R_Id,@iEp_Seccion=Ep_Seccion,@sEp_Nombre=Ep_Nombre From tblEdicionPDF WHERE Ep_Id=' + CONVERT(varchar(1), @iEp_Id)
exec(@sSQL)

There is no need of Dynamic SQL in this context. You can Use straight sql as suggested above in one post.

Still if you feel to go for dynamic sql , try using sp_executesql.

You can pass the parameters and also get the output parameters with sp_executesql.

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]