The fast way to enjoy dining in the sun. Table+4 folding chairs, outdoor Table+4 chairs w armrests, outdoor Table and 4 chairs with armrests TUNHOLMEN table is easily folded up, so practical to store away when the day is over. Table+2 chrsw armr+ bench, outdoor Table+3 chrs w armr+ bench, outdoor Table and 2 stools, outdoor Table+6 chairs w armrests, outdoor Bar table and 2 bar stools Table for wall+1 fold chr, outdoorExplore the production lines at Canadel Furniture, the leader in custom dining. See how our furniture is built, using solid birch wood, and know our collections. Custom Dining ... made affordable! Inspired by early colonial craft techniques, the Champlain Collection features straight forward, unadorned furniture marked by time, with simple lines and natural colors that lend rustic charm to any dining room. The chairs represented in the Champlain Collection have been selected because of their historical style and signifiance.
Explore the production lines at Canadel Furniture, the leader in custom dining. See how our furniture is built, using solid birch wood. The self-storing leaf table is equipped with rack and pinion slides for synchronized opening so that it can be easily opened by just one person. Available with certain rectangular models, the leaf is conveniently stored inside the table, eliminating the need to carry the extension to a storage area elsewhere in the home. Canadel TV Ad January 2012Peter revisits an earlier tip on how to use stored procedures to speed up your code. This version makes your stored procedures simpler … though you may have to write a little more code to make the call. In an earlier column, I suggested that one way to speed up your application was to reduce the trips you make to your database, specifically by avoiding calling a stored procedure multiple times. To enable that, I showed how to pass a stored procedure multiple parameter values in a single call and then, inside the stored procedure, load the parameters into a table where they could be integrated with other SQL statements.
As several readers pointed out, I used very old-fashioned technology to implement this tactic: I concatenated all of the parameters into a string and then, in the stored procedure, broke the string into parts before loading those parts into a table. While my code worked in every version of SQL Server, it was hardly "hip and happening." But, if you're using any version of SQL Server later than SQL Server 2005, you have an alternative: table-valued parameters. Table-valued parameters let you pass a collection of table rows as a parameter to your stored procedure. The benefit of passing table rows is you can short circuit some of the stored procedure code from my previous column: It's no longer necessary to break up the string (I used a set of T-SQL code I "borrowed" from a client to do that) or insert the values into a table because your parameters are already in a table. In the examples for this column, I use ADO.NET, but these techniques work equally as well if you're calling your stored procedures from an Entity Framework DbContext object (as I've discussed in a previous column).
Accepting Table Valued Parameters To use table-valued parameters you first need, in SQL Server Manager, to define a table type to your database. This example defines a table type called JobSpecifications with two columns called JobName and AvailableDate: With the type defined, you can now use it as a parameter in any stored procedure where it makes sense. This example uses the type to accept one or more rows of the JobSpecifications type in a parameter called @JobCriteria: In the body of the stored procedure, you can use the parameter like any other table. This example joins the parameter to another table in the actual database to find some matching rows and return the result: But, while everything is better on the T-SQL side of the processing, the code to call the stored procedure can be a little uglier because, under the worst possible scenario, the values you're passing are just sitting around in variables in your application. In that scenario you need to create a DataTable with columns that match your table type and then load it with rows.
For example, code to create a DataTable that matches my JobSpecifications type would look like this: This code creates a row from the DataTable, populates the two columns in the row and then adds the row to the table: Finally, of course, I need to pass the DataTable to the stored procedure: If this looks a little intimidating, it is the worst-possible scenario -- many other scenarios are simpler. If, for example, your parameters are in a DataTable in a DataSet, you can just pass the DataTable as your parameter. This example passes a DataTable called Parms in the DataSet held in a variable called ds (this will also work with any method that returns a DataTable, though not with any method that returns some collection of DataRows): If you're reading your parameters from a table through a DataReader, then you can pass the DataReader as your parameter. This example uses the AddWithValue method on the Parameters collection to pass a DataReader called rdrParms: There doesn't, however, seem to be a simple way to pass the results of a LINQ query to a table-valued parameter.
Whichever mechanism you use, once you've got the parameter added you can call the stored procedure and process the results: While this column has concentrated on one technology for passing multiple parameters to a stored procedure, that's really just a means to an end. The real goal is to make your application run faster by reducing trips to your database. However you do that will make your users say the best thing: "Hey, that was fast!" Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. All the examples in this article use the AdventureWorks database and have been tested through SQL Server 2008. All the client code examples are written using Result sets are what you get when you run a simple SELECT statement inside aLet's suppose you want a stored procedure to return a list of all the
people with a given last name. The code for the stored procedure might look like this: If you just execute this stored procedure in SQL Server Management Studio you get a result set that looks like this: If you want to write a web page that calls this stored procedure and processes the results that code you'll need to add a using clause for theThis is needed for all the client side samples. The code itself might look like this: If you want to capture this result set using T-SQL you'll need a place to Temporary Tables work well for that. That code might look something This code let's us capture the result set from the stored procedure into a table variable or temporary table. I use code like this when I work withI call their stored procedures to get the results I want and then manipulate it as needed. If you just want to return a single value (or a couple of values) you can useThe stored procedure looks like this:
If we want to return the value using T-SQL we'd use code like this: And we can see that there are still 123 Alexanders in our database. call run this stored procedure from ASP.NET we'd need code that looked like The last way to get data back from a stored procedure is also the most limiting. It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure. All it does is use the RETURN statement to send back the value that was passed in. Note that executing a RETURN statement causes a stored procedure to stop executing and return control back to the calling program. This is often used to test for error conditions and stop processing if one is found. The following script calls the TestReturn stored procedure: and the output looks like this: Whatever number is returned using the RETURN statement is put into the variable @ReturnValue. The ASP.NET code to get the return value looks like this: