CODEWITHSUNDEEP
sqlsql-server
Gautam
Gautam

Reputation: 383

Dynamic SQL throws error complaining scalar variable is not defined

I am copying my bulk data to SQL Server (table name: TmpTable) via C# code and then I want to update the table with following stored procedure:

ALTER PROCEDURE dbo.sp_Update_Locations
    (@lupdatedNoRow VARCHAR(10) OUT)
AS
    SET NOCOUNT ON
BEGIN

    DECLARE @mttblfaximages3_sql NVARCHAR(500) ='UPDATE testAdmin.dbo.mttblFaxImages2 set fRemoteStorageLocation = temp.RemoteStorageLocation, fRemoteImageName = temp.RemoteImageName  from testAdmin.dbo.mttblFaxImages2 T INNER JOIN #TmpTable Temp ON (T.fFaxId=Temp.PrimaryId  AND T.fFaxPageId=Temp.SecondaryId); DROP TABLE #TmpTable;SELECT @lupdatedNoRow = cast(@@rowcount as VARCHAR)' 

    EXEC sp_executesql @mttblfaximages3_sql
            select @lupdatedNoRow

END

I see update works fine but c# throws exception after that

Must declare the scalar variable "@lupdatedNoRow"

I want to return the number of rows updated.

How I should modify my stored procedure to return number of rows updated?

Upvotes: 2

Views: 182

Answers (1)

Squirrel
Squirrel

Reputation: 24763

you need to define & pass the variable @lupdatedNoRow into the sp_executesql

EXEC sp_executesql @mttblfaximages3_sql, 
                   N'@lupdatedNoRow varchar(10) OUTPUT', 
                   @lupdatedNoRow OUTPUT
select @lupdatedNoRow

Upvotes: 2

Related Questions