Filtering Results of a Stored Procedure

Recently I needed to filter the results of a Stored Procedure, After some searching I found 2 options for this as below.

1. Using OpenRowSet Command

SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=ServerName;TRUSTED_CONNECTION=YES;', 'EXEC sp_Who2')

Remember in this method this feature should be enabled in the server.

2. Using Temporary Tables.

-- Creating a temporary table.
CREATE TABLE #tblSPWho2
(SPId INT, Status NVARCHAR(200), LoginName NVARCHAR(200), HostName NVARCHAR(200), BlockedBy NVARCHAR(200), DBName NVARCHAR(200),
Command NVARCHAR(200), CPUTime BIGINT, DiskIO BIGINT, LastBatch NVARCHAR(200), ProgramName NVARCHAR(200), SPId2 INT, RequestId NVARCHAR(200))

-- Inserting the results of the Stored Procedure into the temporary table.
INSERT INTO #tblSPWho2
EXEC sp_Who2

-- Doing the required filtering using the temporary table.
SELECT * FROM #tblSPWho2 WHERE LoginName = 'Domain\UserName'

-- Removing the temporary table.
DROP TABLE #tblSPWho2

SP_Who2 Stored Procedure will bring all the processes that is currently active in SQL server with the relevant information, the above query will filter the results and will only display the processes that are initiated by the given username (Domain\UserName).

Dynamically Creating Computed Columns in SQL

If you are into programming you should have definitely worked with calculated fields in SQL. But for others I will briefly explain what they are.

Calculated columns are columns which depend on other columns. It gets its value by calculating which can involve values of other columns. The calculation formula is the only thing stored in the column.

So each time the column is referenced the calculation is done. But if you use the keyword “persisted” while creating the column then the values will be kept in the table. Whenever a referenced value is updated the computed column is also automatically updated to highlight the change. Also by using persisted you can index a computed column.

You can create a table with persisted computed column as follows.

1. CREATE TABLE Customer
2. (
3. CustomerId INT IDENTITY(1, 1) NOT NULL,
4. CustomerFirstName NVARCHAR(100) NOT NULL,
5. CustomerLastName NVARCHAR(100) NOT NULL,
6. CustomerFullName AS CustomerFirstName + ' ' + CustomerLastName PERSISTED
7. )

One thing to remember is that you cannot directly insert values to a computed column.

I think you got a basic idea of computed columns. Now I would like to show how to create a computed column dynamically. For example think that you need to add a computed column to a table using a stored procedure. It is not a big deal.

I need to insert a TotalOrder column to the table named FoodOrder.

1. CREATE TABLE FoodOrder
2. (
3. OrderId INT IDENTITY(1, 1) NOT NULL,
4. OrderDate SMALLDATETIME NULL,
5. CustomerName NVARCHAR(100) NOT NULL,
6. TotalStarter INT NULL,
7. TotalMainCourse INT NULL,
8. TotalSoftBevarage INT NULL,
9. TotalLiquer INT NULL,
10. TotalDessert INT NULL
11. )

This can be done using the following query.

1. DECLARE @sExecuteCommand VARCHAR(250), --Keepa the command to be executed.
2. @sColumns VARCHAR(150) --Keeps the columns to be included in the formula.
3.  
4. SET @sColumns = 'TotalStarter+TotalMainCourse+TotalSoftBevarage+TotalLiquer+TotalDessert+'
5. SET @sExecuteCommand = 'ALTER TABLE FoodOrder ADD TotalOrder AS ' + SUBSTRING(@sColumns, 1, LEN(@sColumns)-1) -- Creating the computed column.
6. EXEC (@sExecuteCommand)

Note that a cursor or a loop can be easily used to populate the variable “sColumns” with the columns required for the formula.

Hope this helps.

Debugging Stored Procedures

I know that all of you might have tried debugging Stored Procedures (SPs). But I know there are also people who are searching how to do this so I thought I needs to put an article on how to do this.

Please note that to dubug SPs you need to use an account which is a member of sysadmin role.

1.) SQL Server 2005
When you install SQL Server 2005 it will install SQL Server Business Intelliegnce Development Studio. You can use this or Visual Studio to debug SPs.

Start any prefered application from the above two and go to View menu and click on Server Explorer (or press Ctrl + Alt + S) to display the Server Explorer.

Now create a connection to the database where the SP you want to debug is located (Right click on Data Connections and click Add Connection...).

When the connection is added to the list expand the list and browse to the Stored Procedures and right click on the SP which you want to debug and select Step Into Stored Procedure. Now if the SP is requiring any parameters a dialog box will be displayed to enter the values. After entering them click Ok to run into the SP.

After you were taken into the debug mode you can use the same keys to debug SPs as if you are debugging application code (Step Into - F11, Step Over - F10, Step Out - Shift + F11).
If you like to use the Debug toolbar activate it by Clicking on View -> Toolbars -> Debug.

If you want to know what the buttons does, just hover on top of the tool so a helpful tooltip will appear. If you need more information press on the help tool which is the right most tool with a yellow question mark.

Also remember that all the additional features are also available for you to use (as if the Immediate Window) while debugging.


2.) SQL Server 2000
In SQL Server 2000 you have to use the SQL Query Analyzer to debug SPs.
Start SQL Query Analyzer and click on Tools -> Object Browser -> Show/Hide (or F8) to display the Object Browser if it is not already shown.

Now expand the database where the required SP is located and right click on the SP which you needs to debug inside Stored Procedures node.

Click on the Debug... to start the debugging, if the SP requires any values for its parameters a window will pop up to enter the values. After entering the values click on Execute.

When debugging starts you can use the available controls or shortcut keys (Step Into - F11, Step Over - F10, Step Out - Shift + F11, Run to Cursor - Ctrl + F10) to debug through the SP.



If you want to know what the buttons does, just hover on top of the tool so a helpful tool tip will appear. If you need more information press on the help tool which is the right most tool with a yellow question mark.