ExecuteNonQuery on a stored proc causes it to be deleted
- by FinancialRadDeveloper
This is a strange one. I have a Dev SQL Server which has the stored proc on it, and the same stored proc when used with the same code on the UAT DB causes it to delete itself!
Has anyone heard of this behaviour?
SQL Code:
-- Check if user is registered with the system
IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_is_valid_user
IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_is_valid_user >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_is_valid_user >>>'
END
go
create procedure dbo.sp_is_valid_user
@username as varchar(20),
@isvalid as int OUTPUT
AS
BEGIN
declare @tmpuser as varchar(20)
select @tmpuser = username from CPUserData where username = @username
if @tmpuser = @username
BEGIN
select @isvalid = 1
END
else
BEGIN
select @isvalid = 0
END
END
GO
Usage example
DECLARE @isvalid int
exec dbo.sp_is_valid_user 'username', @isvalid OUTPUT
SELECT valid = @isvalid
The usage example work all day... when I access it via C# it deletes itself in the UAT SQL DB but not the Dev one!!
C# Code:
public bool IsValidUser(string sUsername, ref string sErrMsg)
{
string sDBConn = ConfigurationSettings.AppSettings["StoredProcDBConnection"];
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter sqlAdapter = new SqlDataAdapter();
try
{
SqlConnection conn = new SqlConnection(sDBConn);
sqlcmd.Connection = conn;
conn.Open();
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = "sp_is_valid_user";
// params to pass in
sqlcmd.Parameters.AddWithValue("@username", sUsername);
// param for checking success passed back out
sqlcmd.Parameters.Add("@isvalid", SqlDbType.Int);
sqlcmd.Parameters["@isvalid"].Direction = ParameterDirection.Output;
sqlcmd.ExecuteNonQuery();
int nIsValid = (int)sqlcmd.Parameters["@isvalid"].Value;
if (nIsValid == 1)
{
conn.Close();
sErrMsg = "User Valid";
return true;
}
else
{
conn.Close();
sErrMsg = "Username : " + sUsername + " not found.";
return false;
}
}
catch (Exception e)
{
sErrMsg = "Error :" + e.Source + " msg: " + e.Message;
return false;
}
}
