Create custom SQL commands

Create custom SQL commands

When you click the Specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures option and then click Finish in the Configure Database Connection dialog box, the Edit Custom SQL Commands dialog box opens. In this dialog box, you can create custom SQL statements to retrieve or modify the data in a database.

There are four tabs in the Edit Custom SQL Commands dialog box, each corresponding to a common SQL command. They are:

• Select  The SELECT command is used to retrieve data from a table.
• Update  The UPDATE command is used to modify data in a table.
• Insert  The INSERT INTO command is used to insert new rows into a table.
• Delete  The DELETE command is used to delete rows from a table.

The Stored Procedure list displays all of the stored procedures that are available in the database that you are connecting to.

When the Edit Custom SQL Commands dialog box opens, any statement already applied to the data appears in the SQL Statement box. For example, if you create a database connection by clicking Select a table or view to select a table, and later you decide to edit the SELECT statement, you see the following statement in the SQL Statement box on the Select tab:

SELECT * FROM dbo.[your_table_name]

The asterisk in this statement is a wild-card character that means all. This SQL statement means select all of the fields for all of the records from this specific table. To edit the statement, click Edit Command to open the Command and Parameter Editor.

Edit the SQL query by typing directly in the SELECT command box.

Add SQL parameters

You can also create parameters for your SQL queries by clicking Add Parameter.

SQL parameters should be used anywhere that you might use a query string, meaning text enclosed in single quotation marks. Because using query strings in SQL statements creates a security vulnerability, SQL parameters are required in Office SharePoint Designer 2007 whenever you create an SQL statement that retrieves filtered data from a data source.

For example, Northwind Traders is a gourmet food distribution company. They store all of their business data in an SQL-based database. When they retrieve their entire product list from the products table, the resulting SQL query looks like this:

SELECT * FROM dbo.[Products]

This SQL statement retrieves all of the records in the product table. Northwind wants to retrieve a filtered list that includes only the items that are out of stock. The table includes a UnitsInStock field. Using traditional SQL, the statement that would retrieve this list looks like this:

SELECT * FROM dbo.[Products] WHERE UnitsInStock = '0'

The

'0'

in this statement is a query string. This query string should be replaced by a variable. For this example, you want to create a variable called OutOfStock. The new SQL statement looks like this:

SELECT * FROM dbo.[Products] WHERE UnitsInStock = @OutOfStock

To create the SQL statement by using the OutOfStock variable in this example, perform the following procedure:

1. In the Command and Parameter Editor dialog box, in the SELECT command box, type the following string:
   

   SELECT * FROM dbo.[Products] WHERE UnitsInStock = @OutOfStock

2. Under Parameters, click Add Parameter.
3. The variable that you want to define is OutOfStock, so in the Name column, type OutOfStock.
4. In the Parameter Source list, click None.
5. Because the value that you want to match is 0, in the DefaultValue box, type0.
   
   
6. Click OK.
   
   You can now see the new SQL statement in the Edit Custom SQL Commands dialog box.
   
7. Click OK again.
   
   In the Data Source Properties dialog box, the Edit Custom Query button appears on the Source tab.