Dynamic SQL throws error complaining scalar variable is not defined

Question

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?

Squirrel · Accepted Answer · 2018-11-23 02:19:41Z

2

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

answered Nov 23, 2018 at 2:19

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gautam Over a year ago

Thanks Squirrel, once I update the SP as you said that error goes away but when I try to extract value for @lupdatedNoRow in c# , it returns 0. Though I see in table update has happened.

Squirrel Over a year ago

move the @@rowcount to immediately after the update and before the drop table. @@rowcount returns the affected rows from last operation. In your case it is the drop table

Gautam Over a year ago

Ohh Shoot I should have noticed that ! Thanks Squirrel ! Time to buy SQL classes online.

Collectives™ on Stack Overflow

Dynamic SQL throws error complaining scalar variable is not defined

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related