Creating New Stored Procedures


The process used to create and edit stored procedures has changed very little since Visual Studio 2003 (and not that much since Visual Basic 6.0), so it should be familiar to those of you that have worked with previous versions. For the rest of you, the process is intuitive and should not present any real problems.

Tip

The Professional and better versions of Visual Studio 2005 and the SQL Server Management Studio tools permit you to create stored procedures, execute them, and run selected T-SQL code from the stored procedure code editor window, as discussed next in this chapter.


Remember, a stored procedure is simply a program written in T-SQL that runs on SQL Server. It might accept input and return OUTPUT parameters and always returns a RETURN value. Input parameters are used as placeholders for constants in the stored procedure logic. They cannot be used to name tables, columns, or other database objects. When you test your stored procedure using the Server Explorer, Visual Studio prompts you to supply parameter values. Note that Visual Studio sometimes gets confused when using OUTPUT parametersit might prompt for input values for these parameters as well. The best thing to do is to provide some valuewhich is ignored by the stored procedure during execution.

1.

Let's walk through the process of creating a stored procedure using the Server Explorer. Open the Server Explorer and navigate to the Stored Procedures icon (as shown in Figure 5.1). Right-click and choose "Add New Stored Procedure". This opens the new stored procedure template, shown in Figure 5.2.

Figure 5.2. The Create New Stored Procedure template.


Note that when you first create a new stored procedure, you need to use the CREATE PROCEDURE T-SQL command. However, once it's saved, Visual Studio automatically changes the syntax to ALTER PROCEDURE because SQL Server won't let you create a stored procedure with the same namebut it will let you ALTER an existing procedure.

2.

The stored procedure name is prefixed with the owner of the schema, so you need to work with your DBA to determine the correct nameor simply leave it off, as the name defaults to "dbo". Name your new procedure so that it's easy to find and conforms to your team's naming conventions. No, don't begin your stored procedure name with "sp_" as some documentation suggeststhis prefix is reserved for systems stored procedures. If you use this prefix, SQL Server searches the master database for the procedure before searching your "user" database. I'm going to name my first new procedure "CustomerByState"[2]. It's designed to return a limited number of customer rows from a specific state.

[2] The sample Biblio database already has a "CustomerByState" stored procedure, so you might want to name your new stored procedure "CustomerByStateNew" or some such.

Don't prefix your stored procedure name with "sp_".

3.

Directly below the CREATE PROCEDURE command, you'll find a begin comment "/*" tag (Figure 5.2, line 2). This is placed in the template to comment out the parameters block. Since my procedure needs a few parameters (as most stored procedures do), remove the beginning "/*" (line 2) and ending "*/" (line 7) comment tags. Note that T-SQL comments are colored green so when you remove the comment tags, the text color changes to black and blue. Blue text terms are reserved words recognized by the T-SQL editor. As you edit the text in the T-SQL editor, Visual Studio color-codes comments, keywords, operators, and logic differently to help make it easier to know if the code is correctly entered. No, there is no intellisense when coding stored proceduresI've asked for this feature for decades without much progress.

4.

Since my stored procedure logic requires several parameters, I need to populate the parameters block. For each parameter, I need to:

  • Name the parameter. In ADO.NET 2.0, parameter names are especially importantthey are the only way you can address specific parameters in the Command Parameters collection. The parameter name should be descriptive and make it clear what the developer needs to pass to the stored procedure.

  • Assign a datatype. Each parameter must be assigned one of the SQL Server datatypes or a datatype defined by a CLR datatype (as discussed in Chapter 13 Managing SQL Server CLR Executables). If the datatype is variable-length, you need to provide the maximum acceptable length.

  • (Optionally) Set a default value. If your procedure is written to permit the calling routine to call the stored procedure without including the parameter, you'll have to set a default value here in the stored procedure definition. If the ADO.NET Command Parameters collection does not include a Parameter object for a parameter with a default value, SQL Server substitutes the default value. If there is no default value set, SQL Server throws an error back to ADO.NET.

  • (Optionally) Set whether the parameter can accept a NULL. If your procedure is written to accept a NULL for the parameter value, you need to mark the parameter as permitting NULL.

  • (Optionally) Set the parameter direction. By default, all parameters are considered to be input to the stored procedure. If you want to pass back a value in a parameter, you need to mark the parameter as OUTPUT or INPUTOUTPUT (if the parameter should require an input value).

The stored procedure I'm building needs two parametersone to input the state code and another to indicate how many rows should be returned. Both of these parameters have default values set, so if the stored procedure is executed without passing parameters, it runs anyway but returns customers from all states.

5.

I'm ready to insert the T-SQL logic for the procedure. Under the AS clause, define any local variables used in the stored procedure. In this case, I'll illustrate use of local variables by declaring @Rows as an integer, as shown in Figure 5.4.

6.

Next, right-click in the stored procedure editor window directly before the "RETURN" and choose "Insert SQL". This opens the (now familiar) Query Builder, as shown in Figure 5.3 (after the T-SQL code was generated).



Figure 5.3. The "Insert SQL" dialog opens the Query Builder.


When you're happy with the generated SQL, click "Validate Query" to test the SQL or "Execute Query" to test the logic. Since the stored procedure has parameters, Visual Studio exposes a dialog to capture the parameters, but this also means that the Validate Query option won't work. For some reason, this same dialog is exposed even if your stored procedure has only OUTPUT parameters. That's because the SQL Server interfaces have not been able to tell the difference between INPUTOUTPUT and OUTPUT parameters for some time. Note that the TOP expression cannot accept a NULL value or <default> as passed by the Visual Studio dialog that captures the input parameters, so you'll have to supply a valid value (at least, the first time). This dialog remembers what was entered for each parameter and uses that as the default for subsequent executions.

7.

When you're ready to save the generated SQL back to the stored procedure, click "OK". At this point, the generated SQL is inserted into your new stored procedure at the cursor, as shown in Figure 5.4. Note again that the code surrounded with the black border is an "atomic" code block.



Figure 5.4. The Query Buildergenerated SQL is inserted into your new stored procedure.


Add a SET statement to the T-SQL to capture the number of rows returned by the SELECT. I pass this value back as the RETURN value. Sure, I could have passed the @@RowCount directly to RETURN, but I wanted to show how to use a local variable.

IMHO

For some reason, the Visual Studio T-SQL editor insists on removing unnecessary (or, at least, what it judges to be unnecessary) carriage-returns (CR) in the T-SQL text. The T-SQL compiler does not care about these, and I often insert extra CRs into the T-SQL to make it easier to read (I did so in Figure 5.4). I think it's rude that the editor makes these arbitrary and unnecessary edits.

At this point, your stored procedure should be ready to save. Here is where the "fun" part starts. Most of us are accustomed to interactive code development IDEs that make it clear where I've made mistakes in syntax or usage. The SQL editor in Visual Studio is not one of those developer-friendly tools. If there's something wrong, the IDE returns a very terse error dialogalmost akin to "Stuff happened". For example, if you remove the parenthesis around the "(@MaxRows)" expression and click the Save button (or File Save), you get back the dialog shown in Figure 5.5.



Figure 5.5. The Visual Studio T-SQL editor fails to return the error line number.


Actually, this is better than previous versions that simply reported that the stored procedure could not be saved. It turns out that SQL Server returns more information about the syntax error, but Visual Studio throws it away. The same syntax error generates the following (see Figure 5.6) message in SQL Server Enterprise Studio (or in SQLCMD). Knowing the problem is on line 8 of the procedure is very helpful.

Figure 5.6. The SQL Server Enterprise Studio error message for the same syntax error.


Again, I expect that this oversight is because Visual Studio has to be an OSFA interface and they could not figure out how to genericize the UI and keep the (really important) line number that caused the problem.

Assuming you can get Visual Studio to save your new stored procedure, you can begin testing it from your application or from here in the Visual Studio IDE.

Note that the Visual Studiogenerated code is surrounded with a black bordercontaining what Microsoft calls a "SQL block." Note that if you need to change a SQL block, you can right-click on a block and choose "Design SQL block". Your stored procedure might have several SQL "blocks" of generated codeeach of these can be edited separately. As I said earlier, the power of the Query Builder is fairly limited, so you'll eventually bypass the generated code process to simply build and edit the code yourself.


8.

Now that your stored procedure has been saved, it should appear in the list of stored procedures in the Server Explorer. Click on the stored procedure icon in the Server Explorer and navigate down to your new stored procedure. Click on the + sign to expand the parameters list, and click on the first (@StateWanted) parameter. The parameter properties are displayed in the Visual Studio properties dialog (see Figure 5.7). Note that while these properties are read-only, you can glean enough information from these properties to create an ADO.NET Parameter object to map this parameter when it comes time to execute the stored procedure.

Figure 5.7. Focus on a specific stored procedure parameter to view the properties.


Creating and Editing Complex Stored Procedures

Now that you've created a simple stored procedure, it's time you tried something a bit more challenging. The stored procedure I'm about to build is used to update a specific row in the Customers table and return the TimeStamp value from the changed row in an OUTPUT parameter. Yes, that's rightstored procedures are often used to perform complex DML commands.

1.

Navigate to the stored procedures icon in the Server Explorer and right-click. Choose "Add New Stored Procedure".

2.

Name your stored procedure "CustomerUpdate1". Yes, there is a "CustomerUpdate" stored procedure in the Biblio database that you can use as a finished solution.

3.

Set up a parameter signature to capture:

  • CustomerID as an integer

  • TID as a varchar(10)

  • CustomerName as a varchar(50)

  • Discount as a float

  • OriginalTimeStamp as a Timestamp

  • NewTimeStamp as a Timestamp

    Sure, you can name these parameters anything that makes sense for you and your team. The datatypes and lengths specified must match those used in the table definition. To determine the table schema, use the Server Explorer to navigate to the selected (Customers) table and drill into selected columns. The Visual Studio properties dialog will show the defined datatype, as shown in Figure 5.8.

    Figure 5.8. Using the Server Explorer to determine database table column properties.


4.

The NewTimeStamp parameter is set by the stored procedure code, so it needs to be marked as OUTPUT. If your code includes a parameter that serves as both an input and output parameter, you must set it to INPUTOUTPUT.

5.

The stored procedure has two T-SQL code blocksone to perform the UPDATE and a second to retrieve the newly generated TimeStamp value. These two operations are bound in a single transaction. Note that the UPDATE uses the Customer ID (CustID) to locate the row to change and uses the current TimeStamp value (fetched by the client application when the row was initially readbefore changes were made to the row) for concurrency. That is, if the original TimeStamp value matches the current (database) TimeStamp value, I know the row has not changed since last fetched, so it's safe to update. If the TimeStamp column has changed, I know some other operation has changed the row since it was last fetched. In this case, the UPDATE changes no rows. I inform the calling application of this by passing back 0 in the RETURN value and NULL in the @NewTimeStamp OUTPUT parameter.

Notice how the OUTPUT parameter is setit can be treated as any local variable. For example, it can be changed (set) in a SELECT or with a SET statement, as I have done on line 21 in Figure 5.9.

Figure 5.9. The CustomerUpdate procedure definition.


Remember, you should not depend on being able to fetch OUTPUT or RETURN value parameters until any rowsets are returned from the query. In this case, this is not an issue because I'm not generating a rowset. No, the SELECT @GeneratedTimeStamp does not return any rowsit's simply used to set a local variable. This means the OUTPUT and RETURN value parameters should yield correct results as soon as the stored procedure is executed.

Wait a second. I was going to skip this, as I'm running out of time, but I can't go any further without showing you how to use the SQL Profiler to monitor what SQL Server is executing. This tool should be one of the first you pull out of your bag when you start testing stored procedures or any code generated by Visual Studio or ADO.NET. I've included a few pages in Appendix III, "Monitoring SQL Server," to show you how to set up a basic trace. I suggest you take a look before going on.


6.

I'm ready to test this stored procedure, so navigate to the CustomerUpdate stored procedure in the Server Explorer and right-click. Choose "Execute". Since the stored procedure has parameters, Visual Studio displays the "Run Stored Procedure" dialog to capture the parameters (as shown in Figure 5.10). Note that all of the parameter are initially set to "<DEFAULT>".

Figure 5.10. The Visual Studio parameter capture dialog on first invocation.


Unfortunately, unless you manually set these values, Visual Studio executes the stored procedure using "default" for all of the parametersregardless of the datatype. This mean you'll have to fill in valid values (that conform to the datatype) or NULL for each parameter. If you want to pass NULL, use the dropdown list on each value to set "<NULL>".

The results of this execution are displayed in the Visual Studio Output window (View | Output). Use the dropdown in this window (if necessary) to view the Database Output (as shown in Figure 5.11). This includes a prototype for the execution (the actual T-SQL sent to SQL Server can be viewed in the profiler), as well as the built-in "rows affected" value and the values returned by any OUTPUT parameters and the RETURN value. Yes, to get (far) more detail, use the SQL Profiler. See Appendix III on setting up a SQL Profiler trace.

Figure 5.11. The Visual Studio Database Output window.





Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net