Patrick Patrick - 6 months ago 7
MySQL Question

Use Delete table query in stored procedure only when xml data is passed from C# to stored procedure

Here is the Xml data of table i am passing from backend C# to stored procedure.

<ArrayOfUserData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<UserData>
<Name>Dovyan<Name />
<id>434556464<id />
</UserData>
<UserData>
<Name>Alex<Name />
<id>12345767<id />
</UserData>
</ArrayOfUserData>


This is the stored procedure i am using

USE [DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [DATABASE].[sp_SaveUsertable]

(
@in_params AS XML
)
AS

BEGIN
DELETE FROM dbo.usertable; //use this only when xml data is present. how to do this part ?
select
T.x.value('./Name[1]','varchar(6)') as [Name] ,
T.x.value('./id[1]','varchar(75)') as [id]

into #Temp
from @in_params.nodes('/ArrayOfUserData/UserData') as T(x);


BEGIN TRANSACTION

INSERT INTO [dbo].[usertable](Name,id)
SELECT Name,id FROM #Temp AS T

COMMIT TRANSACTION
drop table #Temp;

END


Here is my query.
As you can see, i am first deleting the table and then saving the xml data to the table. There could be a scenario when the xml data is empty. the stored procedure will delete the table but no new data will be saved as it is empty.

I want to put a condition that delete the table only when there is xml data. If there is no xml data, donot delete the table. Any way to do it ?

Answer

1) Assuming after execution of this procedure ends data from usertable is read this procedure isn't safe within concurent environment. If two connections execute the same procedure (with diff. XML data) and then read previously inserted data then one connection can read data inserted by second connection. Is it this desired behavior ?

2) You could use

select
 T.x.value('./Name[1]','varchar(6)') as [Name] ,
 T.x.value('./id[1]','varchar(75)') as [id] 
into #Temp
from @in_params.nodes('/ArrayOfUserData/UserData') as T(x);
IF @@ROWCOUNT > 0 -- Num of affetected rows
BEGIN
    DELETE FROM dbo.usertable;   
    ...
END
ELSE
BEGIN
...
END