Using Stored Procedures and Output Parameters

Since version 1.0, XMod Pro has enabled you to execute stored procedures. Beginning in the upcoming 1.3 version, you'll have a cleaner way to do that and, more importantly, you'll be able to work with SQL Output parameters to boot! In this article, I'll explore this new feature set in XMod Pro and show you how to start using it today.

In earlier versions, you executed stored procedures using a method similar to what you would type into the query editor in SQL Server Management Studio. Something like:

EXEC GetBooks @StoreId, @GenreId

You would then add those parameters to your DataSource tag like so:

<ListDataSource CommandText="EXEC GetBooks @StoreId, @GenreId">

  <parameter name="StoreId" value="23" datatype="int32" />

  <parameter name="GenreId" value='[[Url:gid]]' datatype="int32" />

</ListDataSource>

As you see, the EXEC command is placed in the CommandText property along with the stored procedure name (GetBooks) and the parameters that stored procedure requires: @StoreId and @GenreId. In addition to that, you need to define those parameters in the child <parameter> tags. FYI, the [[Url:gid]] retreives the value of the URL parameter "gid" and uses that as the GenreId. While not technically required, we also added a data type for each parameter. This is a good habit to fall into as it enhances the security of your data, especially when using values passed in via the URL.

CommandType Attribute

Beginning with version 1.3, there is a simpler approach.

<ListDataSource CommandText="GetBooks" CommandType="StoredProcedure">

  <parameter name="StoreId" value="23" datatype="int32" />

  <parameter name="GenreId" value='[[Url:gid]]' datatype="int32" />

</ListDataSource>

First, notice that we've removed EXEC and the @StoreId and @GenreId parameters. All we have to do is specify the name of the stored procedure. Second, we've added a new attribute to the <ListDataSource> tag – CommandType – and given it a value of StoredProcedure. Everything else remains the same. This new syntax should simplify calling stored procedures – especially those with longer lists of parameters. More importantly, though, by using this new syntax, we can take advantage of another new addition to version 1.3

SQL Output Parameters

Output parameters enable a stored procedure to send vital information back to the caller, in addition to a resultset. For instance, let's say that our example stored procedure retrieves a list of books from a given store (StoreId) and the passed-in Genre (GenreId). The SELECT command we execute in our stored procedure would return a list of book records. But what if we wanted to display the name of the store and the name of the genre in our template?

Well, we could adjust the SQL SELECT statement in the stored procedure to JOIN the StoreId and GenreId with their respective lookup tables and pass the names back with each record.

However, that would unnecessarily bloat the amount of data returned from the database with duplicate information, since each record would contain the same store name and genre name. A better approach is to use output parameters. Our stored procedure can lookup the store name and genre name and stick those in parameters passed back to us rather than attach them to each record in our result set.

So, how do you pull this off?

1. Set up your stored procedure to return output parameters. Modifying our example would result in something like this:
    

   CREATE PROCEDURE GetBooks
   
     @StoreId int, 
   
     @GenreId int, 
   
     @StoreName nvarchar(100) OUTPUT, 
   
     @GenreName navarchar(150) OUTPUT
   
   AS
   
   
   
   SELECT @StoreName = StoreName FROM Stores WHERE Id=@StoreId
   
   
   
   SELECT @GenreName = GenreName FROM Genres WHERE Id=@GenreId
   
   
   
   SELECT Id, Title, Author, ISBN 
   
       FROM Books WHERE StoreId = @StoreId AND GenreId=@GenreId

   
   In lines 4 and 5, the output parameters are declared using the OUTPUT keyword. Their values are set in lines 8 and 10. Finally, the list of books is returned in line 12.
    
2. Setup your data source to use retrieve those output parameters. In our example, the modified version would look like this:

   <ListDataSource CommandText="GetBooks" CommandType="StoredProcedure">
   
     <parameter name="StoreId" datatype="int32" value="23" />
   
     <parameter name="GenreId" datatype="int32" value='[[Url:gid]]'/>
   
     <parameter name="StoreName" direction="Output" datatype="string" size="100" />
   
     <parameter name="GenreName" direction="Output" datatype="string" size="150" />
   
   </ListDataSource>

   
   Lines 4 and 5 create our output parameters. We've added the Direction attribute and given it a value of Output. This tells XMod Pro the parameter is an output parameter. Next, the DataType is set to String, which is the type used to handle all textual data sent to and from the database. Finally, the Size attribute is assigned. The size determines the maximum length of the string. It's usually a good idea to use the same value defined for the parameter by your stored procedure, as we've done here. However, you could specify a smaller value. If you do, and the returned value is longer, it will be truncated. Note, even if you do not specify a length in your stored procedure, you still have to provide a value for Size and that value must be greater than 0. If you do not provide a value for Size, you'll receive an error.
    
3. Use the parameter values in your template. They can be used in the HeaderTemplate, ItemTemplate, AlternatingItemTemplate FooterTemplate and even the NoItemsTemplate.

   <xmod:template id="Books">
   
     ...
   
     <HeaderTemplate>
   
       <h3>[[Books_list@GenreName]] Available At [[Books_list@StoreName]]</h3>
   
     </HeaderTemplate>
   
     <ItemTemplate>
   
       <img src="[[ImageUrl]]" align="left" />
   
       <strong>[[Title]]</strong><br />
   
       by [[Author]]
   
       <hr />
   
     </ItemTemplate>
   
     ...
   
   </xmod:template>    

   
   Line 4 is where the action is. Here we're using the new data parameter token to display the genre name and store name as the heading for the list of books. It's made up of five components:
    
1. The [[ and ]] brackets identify it as a token


2. "Books" is the ID assigned to our <xmod:template> tag. Since XMod Pro allows you to have multiple templates, the template's ID enables XMod Pro to more quickly locate the parameter in question.


3. Within each template, you can have a List and a Detail data source. The "_list" identifies that the parameter we want is in the <ListDataSource> data source. If we wanted to use a parameter from the <DetailDataSource>, we'd replace "_list" with _detail".


4. The "at" symbol (@) marks this token as a parameter token and serves to tell XMod Pro that the parameter name is coming next


5. The parameter name. Note that the parameter name is not case-sensitive./li>

That's all there is to it. Output parameters will be an exciting addition to XMod Pro 1.3, due out very soon.