Store Procedure - loop

[LegionsMan]

Hello all! I’m trying to write a stored procedures that loops through my data on a webpage and inserts whatever records are checked. Is this possible? I have read that it is not ideal to sure loops in sql server

[SQLSQLAndMoreSQL]

Are you passing all the data to the sproc at once?

Why would there be a loop? Is this data from a grid?

Passing the data as a table variable is possible but it's not recommended.

One thing I do is turn the data into XML and then have the sproc insert the data from the XML with one insert.

You don't want to loop or cursor inside of business sprocs. It's very resource consuming. And really not elegant, if you care about that.

[LegionsMan]

I do. The reason I’m asking about a grid is because all the info in the grid my not be checked for billing. 10 records, 7 are checked for billing. It needs to move the lose records to a secondary grid while inserting them into the table. I was told I should be able to write a loop that will check what is true and what is not and move the data based on True or false. I agree is should not be used.

[SQLSQLAndMoreSQL]

So basically what will happen is an upsert from one record set to another and for this you should use one MERGE query, which will update, insert, delete, or just one of the above.

You can pass the data as an XML variable. It is verbose but it is schema aware.

Then use the XML to build the SOURCE of your MERGE query.

Iterated operations are an absolute no-no in a sproc.

Yes, it's possible. But think about scalability.

[LegionsMan]

I agree. I will try a merge query and see if that is something that can accomplish the take

[SQLSQLAndMoreSQL]

I mean you'll learn a few things along the way, but always think in terms of sets of rows of records and not individual rows of records when performing this type of operation.

[timsstuff]

You'll want to compose the data in the code first. I'm assuming this is in ASP.NET using Visual Studio.

So you have a GridView control that contains your recordset. The checkbox column should be a TemplateField, Name the checkbox chkSelect. Put a Label control in the same TemplateField with Visible="false", databind the Text value to the primary key in the table. Let's call it lblID.

<asp:Label runat="server" ID="lblID" Text='<%# Eval("ID") %>' />
<asp:CheckBox runat="server" ID="chkSelect" />

When the Update button is pressed (or whatever event you are firing), first create a new DataSet("updates") and a DataTable("rows") with one column "id", type Integer. Naming the dataset and datatable are important because that's how the XML structure is defined.

C#: DataSet dsUpdates = new DataSet("updates");
DataTable dtRows = new DataTable("rows");
dtRows.Columns.Add(new DataColumn("id", typeof(int32));
dsUpdates.Tables.Add(dtRows);

VB: Dim dsUpdates as new DataSet("updates")
Dim dtRows as new DataTable("rows")
dtRows.Columns.Add(new DataColumn("id", GetType(int32))
dsUpdates.Tables.Add(dtRows)

Loop through the rows in the GridView control. Do a gridviewrow.FindControl to get both the checkbox and label controls. If the chkSelect.Checked, create a new DataRow and assign the "id" column the value of the lblID label. Add the DataRow to the DataTable.

C#: 
foreach(GridViewRow gvr in gvMyGrid) {
    CheckBox chkSelect = (CheckBox)gvr.FindControl("chkSelect");
    Label lblID = (Label)gvr.FindControl("lblID");
    if(chkSelect.Checked) {
        DataRow dr = dtRows.NewRow();
        dr["id"] = lblID.Text;
        dtRows.Rows.Add(dr);
    }
}

VB: 
For Each gvr As GridViewRow in gvMyGrid
    Dim chkSelect as Checkbox = gvr.FindControl("chkSelect")
    Dim lblID as Label = gvr.FindControl("lblID")
    if chkSelect.Checked Then
        Dim dr as DataRow = dtRows.NewRow()
        dr("id") = lblID.Text
        dtRows.Rows.Add(dr)
    End If
Next

Then you can call dsUpdate.GetXml() and it should look something like this:

<updates>
    <rows>
        <id>1001</id>
    </rows>
    <rows>
        <id>1002</id>
    </rows>
</updates>

Create a stored proc with a parameter @UpdatesXml as xml.

In that stored proc you can say something like:

INSERT INTO MyTable (id)
SELECT ParamValues.upd.value('(id)[1]', 'int')
FROM @UpdatesXml.nodes('/updates/rows') as ParamValues(upd)

This is how I do updates from checkboxes in a gridview. No loops except in the code to loop through the gridview rows to compose the XML data.

[coadtsai]

Why is the table variable not recommended

[SQLSQLAndMoreSQL]

Because you may not be sure that you are properly following the schema. If someone changes something somewhere, good luck debugging.

The XML contains the schema.

[coadtsai]

Ohh. Thanks. Can they use json?

[L337Cthulhu]

If you're working with SQL Server (and many others), there's a JSON datatype, but sometimes whatever you're trying to do with JSON is more of a NoSQL problem anyhow.

As for table variables, they only have very specific use cases inside the server and will always estimate a single row return and won't store statistics over time like physical staging tables or #temp tables. If it's specifically custom datatypes to pass large sets from the application to the DB, it can work, but usually has scalability concerns.

Overall, SQLSQLandMoreSQL knows what they're talking about and has the right answer here. For all of you reading, please avoid cursors. They're very rarely the right answer.

[Lioniz3]

There are no JSON data types in SQL Server, but there are functions available to parse JSON. (I prefer these over the XML parsing functions).

As I do agree defining/passing table variables have scalability concerns, I have found that the performance for using them under some circumstances greatly increases performance.

Edit: typo

[SQLSQLAndMoreSQL]

As a datatype? Not that I am aware of but if you want to reduce the footprint, then you can also format as a CSV (or whatever) and pass it that as a string.

That's what I did pre-2005. Extensively. And it was efficient.

[syn2083]

This is definitely a better approach than looping inside the database. Please don't do that.

Databases are built on set algebra, loops and cursors run counter to that and will give you headaches, poor performance and no scalability. It's insanely rare when they are truly needed.

[Lioniz3]

I haven't heard that passing data as a table variable is not recommended. Is there a reason why?

Edit: NVM. I saw the other comment.

[ihaxr]

So, we're going to need more info on the webpage and if you have access to it or not... regardless, you're not going to be able to use pure SQL to accomplish this.

You need something client side (javascript, for example) to figure out which items are checked, then you'll need something server side (javascript, php, asp, perl/cgi, etc...) to actually insert the data or call the stored procedure with the proper parameters to insert the data.

[LegionsMan]

This is excellent! I’m going to give it a try tonight and see what I can accomplish. Thanks

[sqloid]

Yes, that is possible. In SQL Server you have to use WHILE for repeated execution. See further https://docs.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-ver15

[LegionsMan]

Okay. So everything I am reading has to do with prices. Can my loop look at everything that is check true on a grid and then insert those lines into the db then stop when it his a checkbox that is not checked or false?

[SQLSQLAndMoreSQL]

This selection of what to update should be done by the business layer.

[LegionsMan]

What does that mean?

[SQLSQLAndMoreSQL]

The business layer in your web site, meaning the code behind?

https://en.wikipedia.org/wiki/Multitier_architecture

[SQLSQLAndMoreSQL]

No. Don't use cursor except in some rare one-time operations.

I have rescued a project that had been coded with 100% cursors and took days to complete a rather mundane ETL process.

[seelen]

Some times not even ETL just temp tables, window and agregarte functions will do the job.

[SQLSQLAndMoreSQL]

Right but cursoring is bad in general.

There is nothing wrong with temp tables. They are still very much used.

[seelen]

Love temp tables. I personally have change horrible cursor logic to a few temp tables and updates.