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;
        }
    }