1

I have an stored procedure with below body:

ALTER PROCEDURE dbo.sp_DeleteRoecords
    @Conditions NVARCHAR(200)
AS
BEGIN
    DECLARE @Query NVARCHAR(2000) 

    SET @Query = 'DELETE FROM Customer Where ' + @Conditions
    EXEC (@Query)
END

How I can Get Affected Rows after executing the @Query?

Behzad
  • 3,502
  • 4
  • 36
  • 63
  • 3
    You should hope no one types in `1 = 1` as a parameter to your query – Mr Moose May 25 '15 at 05:47
  • 1
    look at the @@rowcount variable. I hope what you are doing in that stored procedure is not going into a production system. – Peter May 25 '15 at 05:50
  • Before your delete use a SELECT COUNT..WHERE.. into a variable – Mihai May 25 '15 at 05:56
  • check https://msdn.microsoft.com/en-us/library/ms177564.aspx – Kavan May 25 '15 at 06:02
  • Do you really need the conditions variable? -Will you actually be changing the column your delete conditions are based on every time you run the script? Is this part of an application? If so, can't you get the ID's of the records you want to delete based on logic in the back end, then pass this to the stored proc? Your code is VERY unsafe. – KidCode May 25 '15 at 06:17

1 Answers1

1
ALTER PROCEDURE dbo.sp_DeleteRoecords
    @Conditions NVARCHAR(200)
AS
BEGIN
    DECLARE @Query NVARCHAR(2000) 

    SET @Query = 'DELETE FROM Customer Where ' + @Conditions + ';Select @@rowcount'
    EXEC (@Query)
END

Add Statement of @@rowcount after delete query.

KuldipMCA
  • 3,079
  • 7
  • 28
  • 48
  • 1
    Can you run `SET NOCOUNT OFF` before running this statement and try again? This query works for me on SQL2014. – KidCode May 25 '15 at 06:20