This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
.gif)
Getting Creative with EXEC and EXEC()
Ron Talmage
No one denies the power and versatility of T-SQL's EXECUTE command and EXEC() function. But it doesn't hurt to have an expert show you some of the innovative ways in which he's used them. Chances are, Ron Talmage's techniques will strike a chord and help you solve some of your own thorny problems.
Sooner or later, if you program in T-SQL, you'll have to use the EXECUTE command or its sibling, the EXEC() function. This month's column surveys some of the uses of EXEC and EXEC() and shows why using them can sometimes seem like an exercise in gymnastics. Let's work with two problems. The first is solved with the EXEC command, the second with the EXEC() function.
Problem #1: Capturing stored procedure results
Use the permissions provided for Terminal Services to control how users and groups access the server. You can alter the default permissions to restrict individual users and groups from performing certain tasks, such as logging off a user from a session or ending sessions. You manage permissions from Terminal Services Configuration. You must have administrative privileges to set permissions.Recently I received the following request:
Is it possible to isolate a single cell from the results of "xp_msver"? I'd like to get just the "character_value" from executing "xp_msver productversion"-or, in a worst-case scenario, send all four values into a single row in a temp table, then extract the version using a string function.
The answer is "Yes, by using the EXECUTE command-or just EXEC for short. The EXEC command by itself can be used to execute a stored procedure:
EXEC master.dbo.xp_msver
The problem is, you can see the results of the stored procedure in the output window, but you can't directly capture the results of the stored procedure into your own code's variables.
Solution #1: INSERT with EXEC
The solution is to combine the EXEC command with the INSERT statement. The INSERT statement already has one ANSI variation, for which you can combine it with SELECT:
INSERT au_copy
SELECT * from authors
But you can also combine it with the EXEC command as well, and that's the answer to capturing the output of the stored procedure. All you need to do is create a table to receive the output of the stored procedure via the INSERT statement:
CREATE TABLE #msver
(index_val INT,
name VARCHAR(50),
internal_value INT,
character_value VARCHAR(255))
INSERT #msver
EXEC master.dbo.xp_msver
The key is to create the table (a temporary table in this example) so that it has the right number of columns and compatible data types to capture the output of the stored procedure. You can SELECT from the temporary table to see the results and capture the ones you want in variables, as in the following code:
SELECT name, character_value
FROM #msver
DECLARE @@prodver varchar(255)
SET @@prodver =
(SELECT TOP 1 character_value
FROM #msver
WHERE name = 'ProductVersion')
SELECT @@prodver
Granted, you can get this information just as easily from the @@version system function, but you'll have to parse the string:
SELECT SUBSTRING(@@version,30,8)
and the string location might change from version to version. Executing xp_msver gives the result without having to parse any string.
Now let's expand on this results-capturing technique.
Aside: determining whether a file exists
Suppose you need to find out whether a file exists on disk before importing it, through bcp or BULK INSERT. Usually, there's not just one file you need to find, but perhaps several. It would be nice to have a stored procedure that would find out whether the file is there, based on passing it a variable for the filename.
One way to do this is to shell out to the operating system, using the xp_cmdshell extended stored procedure. In fact, it's easy to use xp_cmdshell to find out whether a file exists:
exec master.dbo.xp_cmdshell
'dir d:\mssql7\binn\bcp.exe'
Unfortunately, the stored procedure doesn't return a success code for the "dir" command and, in fact, succeeds whether there's a file on disk or not. So we have a similar problem: We have to capture the results in order to determine whether the file exists or not. And, once again, a temporary table comes to the rescue:
CREATE TABLE #DirResults (Diroutput VARCHAR(500))
INSERT #DirResults
EXEC master.dbo.xp_cmdshell
'dir d:\mssql7\binn\bcp.exe'
IF EXISTS(SELECT *
FROM #DirResults
WHERE DirOutput LIKE '%bcp.exe%')
PRINT 'bcp.exe does exist'
ELSE
PRINT 'bcp.exe does not exist'
Now you can scan the resulting rows and determine whether the key "bytes" value is present, indicating the file was found. Easy, eh?
Well, yes, but what we really need is something general so that we can pass it a variable. Unfortunately, we can't put a variable into the EXEC command:
DECLARE @FileName VARCHAR(100)
SET @FileName = 'd:\mssql7\binn\bcp.exe'
CREATE TABLE #DirResults (Diroutput VARCHAR(500))
INSERT #DirResults
EXEC master.dbo.xp_cmdshell 'dir ' + @FileName
The problem is that you can't include operations and functions as parameters to stored procedures. The concatenation has to occur before the call to xp_cmdshell:
DECLARE @FileName VARCHAR(100), @DirCmd VARCHAR(150)
SET @FileName = 'd:\mssql7\binn\bcp.exe'
SET @DirCmd = 'dir ' + @FileName
CREATE TABLE #DirResults (Diroutput VARCHAR(500))
INSERT #DirResults
EXEC master.dbo.xp_cmdshell @DirCmd
Now the command works fine, and you can inspect the results in the temporary table to determine whether the file exists. We can expand the technique into our own stored procedure:
CREATE PROC DoesFileExist
@FileName VARCHAR(100)
AS
DECLARE @DirCmd VARCHAR(150)
SET @DirCmd = 'dir ' + @FileName
CREATE TABLE #DirResults (Diroutput VARCHAR(500))
INSERT #DirResults
EXEC master.dbo.xp_cmdshell @DirCmd
IF EXISTS(SELECT *
FROM #DirResults
WHERE DirOutput LIKE '%bytes%')
RETURN (0)
ELSE
RETURN (-1)
It returns a 0 if the file exists, and -1 if it doesn't. Ahhhhh. Now you can include the stored procedure in a conditional expression:
DECLARE @Result int
EXEC @Result = DoesFileExist 'd:\mssql7\binn\bcp.exe'
IF @Result = 0
-- file exists
ELSE
-- file doesn't exist
Granted, this is a little clunky, but probably the best we can do in SQL Server 7.0 Better than a stored procedure would be a user-defined function-now available in SQL Server 2000 beta 2. However, SQL Server 2000 UDFs can't access temporary tables.
Define with care
Be sure you define just one column in the temporary table, wide enough to capture the output lines in a command window. If you define more than one column, as in the following:
CREATE TABLE #DirResults (Diroutput VARCHAR(500), F2
VARCHAR(10), F3 INT)
you'll get an Access Violation in the SQL Server extended procedure dll. (This happens in SQL Server 7.0 SP2, but not in the SQL Server 2000 beta 2.) The message is actually rather interesting:
Stored function 'xp_cmdshell' in the library
'xpsql70.dll' generated an access violation. SQL Server
is terminating process 12.
(Yes, it's odd that it's called a "stored function" rather than a stored procedure.) The process (spid) gets terminated, which you can verify by running the same batch of commands from OSQL. In Query Analyzer, though, the reconnection is seamless, so you won't notice the termination. With that in mind, let's tackle a harder problem.
Problem #2: Dynamic table names
Suppose you want a general procedure to get the rowcounts from a varying set of tables. Most likely you'd do this in a stored procedure and pass the table name as a parameter. The problem is that the native SELECT command won't accept variables as table names. For example, the following code won't compile:
CREATE PROC GetTableCount
@MyTable VARCHAR(50)
AS
SELECT COUNT(*)
FROM @MyTable
Referencing variables in place of tables or columns isn't allowed in a T-SQL SELECT, INSERT, UPDATE, or DELETE statement.
Solution #2: Using dynamic SQL
The classic solution is to use the counterpart of the EXEC command, the EXEC() function, and execute the resulting string:
CREATE PROC GetTableCount
@MyTable VARCHAR(50)
AS
DECLARE @cmd VARCHAR(1000)
SET @cmd = 'SELECT * FROM ' + @MyTable
EXEC(@cmd)
Now call the stored procedure with a table name as the parameter:
EXEC GetTableCount 'pubs.dbo.authors'
This is fine, except how do we know whether the command failed, and if it did, how can we capture the error number? Actually, now all we have to do is use Solution #1 and capture the results in a table. Before we look at that solution, though, let's get some background information about EXEC().
EXEC() in a nutshell
The EXEC() function, like the EXECUTE command, executes a T-SQL command that you embed in its parentheses. The difference is that the EXEC() function can execute a string or string variable, as you saw in the preceiding code. The argument for EXEC() can be a literal string, but the real power of EXEC() comes to light when you use a variable.
How does the EXEC() function work? It executes the command contained in the variable, but in a different context from the session calling the function. That's important, because it introduces some severe constraints. Any tables you create are visible to the EXEC() context, but variables declared by the calling session aren't visible to the EXEC() context, and vice versa. Therefore the following doesn't work:
DECLARE @MyVar INT
EXEC('SET @MyVar = 3')
The EXEC() session can't see the variable, because it was declared in the calling session. If we stuff it inside the EXEC(), as in the following:
EXEC(' DECLARE @MyVar INT SET @MyVar = 3')
it still does no good, because now the variable doesn't exist in the calling session. Declaring the variable in both contexts just creates two variables that have nothing to do with each other.
So how can we communicate the results of an EXEC() back to the calling command? Well, despite the fact that variables aren't exchangeable across EXEC() sessions, it turns out that tables, both temporary and permanent, are. So to capture @@ERROR, we can go back to EXEC()'s cousin, EXEC, and INSERT data into a table in order to pass the @@ERROR value back to the caller.
Capturing results of EXEC()
To do this, create a temporary table to hold the results of @@ERROR from the EXEC() function:
DECLARE @cmd VARCHAR(1000), @ExecError INT
CREATE TABLE #ErrFile (ExecError INT)
SET @cmd = 'EXEC GetTableCount ' +
'''pubs.dbo.authors''' +
'INSERT #ErrFile VALUES(@@ERROR)'
EXEC(@cmd)
SET @ExecError = (SELECT * FROM #ErrFile)
SELECT @ExecError AS '@@ERROR'
In this code, the temporary table #ErrResult will capture the actual value of the @@ERROR variable. In addition to the SELECT command, we also send an INSERT command to put the @@ERROR value into the temporary table.
Gymnastic programming
This is what I meant by gymnastics in the beginning of this article: It's definitely a stretch, declaring a temporary table just to hand values back and forth with the EXEC() session. The reason why we have to do such gymnastics is that the scope of T-SQL variables in a calling context doesn't extend to EXEC()'s context, and vice versa. T-SQL has no user-defined global variables that could span execution contexts. Therefore, the only way to send data back and forth between the two contexts is to persist it in a table.
Well, at least it works. Recently I had occasion to create a T-SQL application that read a server's disk drives for free space. The technique I chose was to capture the results of xp_cmdshell calls, parse out the bytes free, and report the result periodically using the Web Wizard.
Download RON700.SQL
To find out more about Microsoft SQL Server Magazine and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the July 2000 issue of Microsoft SQL Server Magazine. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Magazine is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.