if and else in stored procedure

I can see that you tried with @@RowCount before. Why you commented that part of code?

Because it returns 1 in any case

want to know whether it actually updated the field

Where is the sense in dynamic SQL in this scenario? Enabling SQL injection?

Cheers!

the dynamic sql issue apart:

 SET @sql = 'UPDATE table1  SET   [DeleteRecord] = 0
	              WHERE  [Name] = '''+ @Name  + '''  
AND ( [DeleteRecord] <> 0 OR [DeleteRecord] IS NULL) '

Select allOpen in new window

that will make the @@recordcount variable return 1 or 0 accordingly.
however, it will not tell you if the 0 would come from Name = @Name not matching or the value already being 0

and not, there is no other way other then checking the actual value, eventually using the OUTPUT clause: https://msdn.microsoft.com/en-us/library/ms177564.aspx

Any suggestion on how to go about this this one:

      UPDATE [dbo].[table1]
      SET  
              [Number]=CAST(@Number AS  Nvarchar(50)),            
                  [DatesCovered]= CAST(@DatesCovered AS  varchar(50)),             
                  [Date]=CAST(@Date AS  nvarchar(20)),
                  [Details]=  CAST(@Details  AS Nvarchar(100)),                  
                  [ReqBy]=CAST(@ReqBy  AS Nvarchar(50)),            
                 [DeleteRecord] = 0
            WHERE ([Number] =@Number AND [Date] = @Date AND [Details] =@Details )

Suggestion for what?

where clause where it returns rowcount correctly
update only when number<> @number and date<> @date and details <>@details

If you want to follow the same line of thought then you'll need to add all fields that are being updated:

UPDATE [dbo].[table1]
SET  
               [Number]=CAST(@Number AS  Nvarchar(50)),            
               [DatesCovered]= CAST(@DatesCovered AS  varchar(50)),             
               [Date]=CAST(@Date AS  nvarchar(20)),
               [Details]=  CAST(@Details  AS Nvarchar(100)),                  
               [ReqBy]=CAST(@ReqBy  AS Nvarchar(50)),            
               [DeleteRecord] = 0
WHERE 
               [Number]<>CAST(@Number AS  Nvarchar(50)) AND            
               [DatesCovered]<>CAST(@DatesCovered AS  varchar(50)) AND
               [Date]<>CAST(@Date AS  nvarchar(20)) AND
               [Details]<>CAST(@Details  AS Nvarchar(100)) AND
               [ReqBy]<>CAST(@ReqBy  AS Nvarchar(50)) AND
               [DeleteRecord]<>0

Select allOpen in new window

NOTE: This will only update the record if ALL 6 columns have different values from the variables. If you want the update to happen when AT LEAST ONE if different then change the ANDs to ORs.

Thanks a ton Vitor!!! Really appreciate your help!

where clause where it returns rowcount correctly
 update only when number<> @number and date<> @date and details <>@details

Seems you still have a misconception here: @@ROWCOUNT returns the rows touched by your statement. Thus the values in the columns are set to the new value. Regarding less which value they had before, as long as you don't use an appropriate predicate to exclude these rows.

SQL Server does not distinguish between the difference that changing a value to the same value is in our human real-world logic no change. SQL Server updates such a value. It's basically the same as using identity functions in Mathematics. No real use for humans, but very important piece of theory.

btw: USE THE CODE BUTTON!!

E.g.

UPDATE  [dbo].[table1]
SET     [DatesCovered] = @DatesCovered ,
        [ReqBy] = @ReqBy ,
        [DeleteRecord] = 0
WHERE   [Number] = @Number
        AND [Date] = @Date
        AND [Details] = @Details
        AND ( [DatesCovered] <> @DatesCovered
              OR [ReqBy] <> @ReqBy
              OR [DeleteRecord] <> 0
            ); 

Select allOpen in new window

p.s. Where is the sense in using NVARCHAR for every data type?

ste5an,
p.s. Where is the sense in using NVARCHAR for every data type?

The input to the stored procedure is from vbb.net  app so Nvarchar as thay are srings

Cheers

?? Why. VB.NET has more data types than only strings.

There were lotz of error when I tried to pass the variables as date and integer.

You don't do you a favor, when you parse the data at the lowest level, cause it's harder to bubble up the correct error messages.

Seems like your front-end does not handle the values correctly.

This should be fixed.

Just as an example: I've seen an application with a database without those kind of checks. After two years of work it was shut down. Cause it was no longer possible to retrieve meaningful information from it.

Thanks Ste5an, but I dont understand why it so wrong ? The stored procedure does convert the datatypes to correct datatype and it works well.