代码改成存储过程

[too1998]

将下列代码改成一个存储过程~~会的帮下`谢谢

 public void Update(string SettingCode,string IsSettingCode)
        {
            if (!Error.Status)
            {
                try
                {
                    AppConnection tempAppConnection = new AppConnection();

                    try
                    {
                        string[] Code = new string[0]; ;
                        string[] IsCode=new string[0];

                        if (SettingCode.Length > 0)
                        {
                            Code = Regex.Split(SettingCode.Substring(0, SettingCode.Length - 1), ",");
                        }

                        if (IsSettingCode.Length > 0)
                        {
                            IsCode = Regex.Split(IsSettingCode.Substring(0, IsSettingCode.Length - 1), ",");
                        }

                        tempAppConnection.OpenSQLConnect(UserKey);

                        string sqlText = "declare @UserCode				int " +
                                         "declare @PlayerCode			int " +

                                         "Select @UserCode=uli_UserCode " +
                                         "From vw_UserLogin " +
                                         "Where uli_UserKey='"+UserKey+"' "+

                                         "Select @PlayerCode=pif_PlayerCode " +
                                         "From vw_PlayerInfo " +
                                         "Where pif_UserCode=@UserCode " +

                                         "Delete PlayerSettingPrivate " +
                                         "Where psp_PlayerCode=@PlayerCode ";

                        for (int i = 0; i < Code.Length; i++)
                        {
                            sqlText += "Insert Into PlayerSettingPrivate Values(@PlayerCode," + Code[i] + ",1) ";
                        }
                        for (int i = 0; i < IsCode.Length; i++)
                        {
                            sqlText += "Insert Into PlayerSettingPrivate Values(@PlayerCode," + IsCode[i] + ",0) ";
                        }

                        if (!Error.Status)
                        {
                            tempAppConnection.CommandType = CommandType.Text;
                            tempAppConnection.CommandText = sqlText;
                            int row = tempAppConnection.ExecuteNonQuery();
                        }            
                    }
                    catch (Exception ex)
                    {
                        Error.Message = "{Game131_AppLibrary.AppUserInfo.Update()} " + ex.Message;
                    }
                    finally
                    {
                        tempAppConnection.CloseSQLConnect();
                    }

                    if (tempAppConnection.Error.Status)
                    {
                        Error.Message = tempAppConnection.Error.Message;
                        tempAppConnection.Error.Clear();
                    }
                }
                catch (Exception ex)
                {
                    Error.Message = "{Game131_AppLibrary.AppUserInfo.Update()} " + ex.Message;
                }
              }
            }

[too1998]

自己先抢个沙发

[jiezi316]

存储过程和普通的SQL语句一样的，我看你这个基本加上
Create Procedure xxx

这些就差不多了。没有考虑事务哈

[丰云]

大致是下面这样，具体细节自己改！！

create proc Proc_Update 
@SettingCode varchar(4000),
@IsSettingCode varchar(4000),
@UserKey varchar(50)
as
begin
    declare @PlayerCode int 
	declare @tmpIndex   int
	declare @Code varchar(40)
    Select @PlayerCode = a.pif_PlayerCode 
      From vw_PlayerInfo a, vw_UserLogin b 
     Where a.pif_UserCode=b.uli_UserCode 
       and b.uli_UserKey = @UserKey

    Delete PlayerSettingPrivate Where psp_PlayerCode = @PlayerCode

	set @tmpIndex = 0
	while @tmpIndex < Len(@SettingCode)
	begin
	    set @Code = SUBSTRING(@SettingCode,@tmpIndex,CHARINDEX(@SettingCode,',',@tmpIndex))
	    Insert Into PlayerSettingPrivate Values(@PlayerCode, @Code ,1) 
	end
	while @tmpIndex < Len(@IsSettingCode)
	begin
	    set @Code = SUBSTRING(@IsSettingCode,@tmpIndex,CHARINDEX(@IsSettingCode,',',@tmpIndex))
	    Insert Into PlayerSettingPrivate Values(@PlayerCode, @Code ,0) 
	end
end

[丰云]

小改了一下：

create proc Proc_Update 
@SettingCode varchar(4000),
@IsSettingCode varchar(4000),
@UserKey varchar(50)
as
begin
    declare @PlayerCode int 
	declare @tmpIndex   int
	declare @Code varchar(40)
    Select @PlayerCode = a.pif_PlayerCode 
      From vw_PlayerInfo a, vw_UserLogin b 
     Where a.pif_UserCode=b.uli_UserCode 
       and b.uli_UserKey = @UserKey

    Delete PlayerSettingPrivate Where psp_PlayerCode = @PlayerCode
    set @tmpIndex = 0
	while @tmpIndex < Len(@SettingCode)
	begin
	    set @Code = SUBSTRING(@SettingCode,@tmpIndex + 1,CHARINDEX(@SettingCode,',',@tmpIndex) - 1)
	    Insert Into PlayerSettingPrivate Values(@PlayerCode, @Code ,1) 
	    set @tmpIndex = CHARINDEX(@SettingCode,',',@tmpIndex)
	end
    set @tmpIndex = 0
	while @tmpIndex < Len(@IsSettingCode)
	begin
	    set @Code = SUBSTRING(@IsSettingCode,@tmpIndex + 1,CHARINDEX(@SettingCode,',',@tmpIndex) - 1)
	    Insert Into PlayerSettingPrivate Values(@PlayerCode, @Code ,0) 
	    set @tmpIndex = CHARINDEX(@SettingCode,',',@tmpIndex)
	end
end