Using the EXEC Command - 16 Feb 2000

In response to my articles about refreshing SQL Server views, Gert Drapers from Microsoft sent me an updated stored procedure for executing views. This stored procedure isn't notable so much for refreshing views as it is for its approach to using the EXEC command with parameters:

create procedure procRefreshViews asdeclare #curRefreshViews scroll cursorfor select name from sysobjects where xtype = 'v' and status >= 0declare @viewName sysname --, @cmd varchar(1000)open #curRefreshViewsfetch next from #curRefreshViews into @viewNamewhile (@@fetch_status <> -1)begin-- set @cmd = 'sp_refreshview ' + @viewNameexec sp_refreshview @viewName--  execute(@cmd)fetch next from #curRefreshViews into @viewNameendclose #curRefreshViewsdeallocate #curRefreshViews

The code uses the EXEC command to execute the sp_refreshview system stored procedure. Instead of putting the entire command into a variable and passing that to EXEC, this stored procedure simply passes in the view's name. I like this format because it’s simple and quite readable, allowing for easy maintenance down the road.

Using EXEC in a stored procedure is a handy way to execute any other stored procedure. You can use T-SQL to build complex logic into your stored procedures. I like to combine stored procedures with COM objects to implement applications, and T-SQL is useful when you're building maintenance utilities.

I found some code on the Microsoft Web site that uses EXEC to execute an external command and inserts the result of the external command into a SQL table. I modified this code slightly as shown below:

Alter Procedure "getDirInfo" as drop table DirresultsCREATE TABLE Dirresults(C1 varchar(256))INSERT dirresultsEXEC master..xp_cmdshell 'dir'

In this example, you use the xp_cmdshell stored procedure to execute the OS Dir command. The results of Dir are stored in the Dirresults table. These two examples are only the tip of the stored procedure iceberg. Stored procedures offer so much functionality that even those tomes that claim to uncover everything in T-SQL can’t cover it all.