How do I change inline sql statement into Stored Procedure?

Question

Hello,

I have few inline sql statements with some arrays and if loops. I want to change them all into one stored procedure in SQL server 2005. As i am learning SQL, i have got no idea about how it should be done with arrays and if loops. Is that possible to send arrays into stored procs or is there any other solution.

Refer the code which i am going to change into SP. Thanks in advance!!

public bool TempUpdateMerchantCategories(long LocationID, ArrayList CategoryList, ArrayList ImageData)
{
    try
    {
        int j = 0;
        bool hasImage = false;
        string previousPID = string.Empty;
        bool isFirstPID = true;
        int numberOfSubCategories = 3;
        int pIDCount = 0;
        foreach (string data in CategoryList)
        {
            string pID = data.Split(',')[0];
            if (isFirstPID)
            {
                isFirstPID = false;
                previousPID = pID;
                pIDCount++;
            }
            else
            {
                if (pID != previousPID)
                {
                    previousPID = pID;
                    pIDCount++;
                }
            }
        }
        ArrayList stepsThresholdList = new ArrayList();
        if (pIDCount > 1)
        for (int k = 1; k < pIDCount; k++)
        {
            stepsThresholdList.Add(k * numberOfSubCategories);
        }


        if (CategoryList == null || CategoryList.Count == 0) return true;
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("@LocationID", LocationID)
        };

        //SqlHelper.ExecuteNonQuery(DbConnString, System.Data.CommandType.StoredProcedure, "TempMerchant_Location_Category", parameters);

        string commandText = String.Format("DELETE FROM [TempMerchant_Location_Category] WHERE locationid = @LocationID");
        Debug.Write(commandText);
        SqlHelper.ExecuteNonQuery(DbConnString, CommandType.Text, commandText, parameters);

        for (int i = 0; i < CategoryList.Count; i++)
        {
            if (ImageData.Count > 0 && j < ImageData.Count)
            {
                string imageID = ImageData[j].ToString().Split(',')[0];
                string primaryID = ImageData[j].ToString().Split(',')[1];

                if (primaryID == CategoryList[i].ToString().Split(',')[0])
                if (imageID != "IsDefault")
                hasImage = true;
            }

            if (!hasImage)
            {
                parameters = new SqlParameter[]
                {
                    new SqlParameter("@LocationID", LocationID),
                    new SqlParameter("@PrimaryID", Convert.ToInt32(CategoryList[i].ToString().Split(',')[0])),
                    new SqlParameter("@SecondaryID", Convert.ToInt32(CategoryList[i].ToString().Split(',')[1]))
                };

                //SqlHelper.ExecuteNonQuery(DbConnString, System.Data.CommandType.StoredProcedure, "TempMerchant_Location_Category", parameters);
                commandText = String.Format("INSERT INTO [dbo].[TempMerchant_Location_Category] ([LocationID],[PrimaryID],[SecondaryID])  VALUES (@LocationID,@PrimaryID,@SecondaryID)");
            }
            else
            {
                parameters = new SqlParameter[]
                {
                    new SqlParameter("@LocationID", LocationID),
                    new SqlParameter("@PrimaryID", Convert.ToInt32(CategoryList[i].ToString().Split(',')[0])),
                    new SqlParameter("@SecondaryID", Convert.ToInt32(CategoryList[i].ToString().Split(',')[1])),
                    new SqlParameter("@ImageID", Convert.ToInt64(ImageData[j].ToString().Split(',')[0]))
                };
                // SqlHelper.ExecuteNonQuery(DbConnString, System.Data.CommandType.StoredProcedure, "TempMerchant_Location_Category", parameters);
                commandText = String.Format("INSERT INTO [dbo].[TempMerchant_Location_Category] ([LocationID],[PrimaryID],[SecondaryID],[ImageID])  VALUES (@LocationID,@PrimaryID,@SecondaryID,@ImageID)");
            }
            if (stepsThresholdList.Count > 0 && j < stepsThresholdList.Count)
            if (i == (Convert.ToInt32(stepsThresholdList[j]) - 1))
            j++;
            Debug.Write(commandText);
            SqlHelper.ExecuteNonQuery(DbConnString, CommandType.Text, commandText, parameters);
        }
        return true;
    }


    catch (Exception ex)
    {
        LogError("Error Occurred When Updating TempMerchant Ctegories: LocationID:" + LocationID.ToString(), ex);
        return false;
    }
}

Answer 1

Think of using a # Temp Table or Table Variable as your 'Array'. Stored procs can do Looping, but you may want to think in sets to avoid RBAR (Row by Agonizing Row) processing.

Answer 2

If you have SQL Server 2008, you can use a table variable as an input variable for a stored proc, that is how you handle your array. Look up how to do this in Books Online.

As far as the looping and IF, I suggest you try to use set-based processing instead as it is significantly faster. For ideas on set-based logic that may do what you want to do:

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

You might especially pay attention to the examples using the CASE Statement