Tuesday, March 20, 2012

problem while using sp_ExecuteSql

Hi all

I have a stored procedure as I placed below

create procedure ps_Select_Student
@.RollNo nvarchar(50),
@.Class int
AS
Begin
declare @.MainQuery nvarchar(4000)
set @.MainQuery = 'select StudentName where
RollNo=@.RollNo and Class=@.Class'

exec sp_ExecuteSql @.MainQuery
End

Go

While executing this procedure in Query Analyser i am agetting an error that

must declare @.RollNo

What is the problem. Please help me out.

You have to declare those variables on param declaration parameter of sp_executesql

Code Snippet

create procedure ps_Select_Student

@.RollNo nvarchar(50),

@.Class int

AS

Begin

Declare @.MainQuery nvarchar(4000)

Declare @.ParamDecl nvarchar(4000)

set @.MainQuery = N'select StudentName where RollNo=@.RollNo and Class=@.Class'

set @.ParamDecl = N'@.RollNo nvarchar(50),@.Class int'

Exec sp_ExecuteSql @.MainQuery , @.ParamDecl, @.RollNo, @.Class

End

Go

No comments:

Post a Comment