Dynamic SQL Return value

Question

Dynamic SQL Return value

Telammica

Ten Centuries

Points: 1288
More actions
August 7, 2008 at 4:56 am

#70979

Hi All
I have a bit of a problem. If I execute the following statement:
DECLARE @x int
EXEC @x = master.dbo.xp_cmdshell 'cmd /c dir c:\inhouse_apps\'
SELECT @x
I get the result for @x as returned from the commandshell. That is actually the value I want.
The problem is that I cannot execute like that since I have to use dynamic sql to execute what I want.
SELECT @ExecStr = @ImportCopyCommand + '"'+ @ImportSourcePath + '\'+ @ImportFileName + '" "' + @ImportDestPath
SET @exec_with_outputcapture ='INSERT INTO FileImportProcessLog EXEC master.dbo.xp_cmdshell '' ' + @execStr +'''';
EXEC (@exec_with_outputcapture)
Now how will I manage to get the return value from the commandshell if I execute using dynamic sql?
Any ideas will be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 1

It is a bit clunky and I might not understand the problem correctly; however, you might be able to start with something like this and refine it as needed:

/*

create procedure testor

as

return 5

*/

declare @receiver table(b int)

declare @ret int

declare @STR varchar(999)

set @STR = '

declare @b-2 int

exec @b-2 = testor;

select @b-2 as [@b]'

insert into @receiver

exec ( @STR )

select * from @receiver

/* -------- Sample Output: --------

b

-----------

5

*/

Maksymilian Mulawa Ten Centuries Points: 1336 More actions · Answer 2

For tips and tricks on dynamic SQL I would recommend reading 'The Curse and Blessings of Dynamic SQL' article by Erland Sommarskog.

Solution to your problem (the same as suggested by Kent) can be found here

http://www.sommarskog.se/dynamic_sql.html#sp_executesqlong

Cheers, Max

Check-out free open-source utility Sql Server Replication Explorer[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You can use sp_executesql. Something like this which is from another forum post, bust should give you an idea:

Declare @sql nvarchar(max), @params nvarchar(100), @Retval Int

Set @sql = 'SELECT @Rows = COunt(*) FROM ' + @DBNAME + '.information_schema.columns Where table_name = @TableName AND column_name = @ColumnName'

Set @params = '@Rows Int Output, @TableName varchar(100), @ColumnName varchar(100)'

Exec sp_executesql @sql, @params, @TableName = @TableName, @ColumnName = @ColumnName, @Rows = @Sfound Output

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Telammica Ten Centuries Points: 1288 More actions · Answer 4

Thanks everyone for your replies. I managed to solve my problem.

BTW. What would your preference be - xp_cmdshell BCP or BULK INSERT? With BULK INSERT can work with bulkadmin server role whereas xp_cmdshell can only work with sysadmin.

Is there any performance benefit in using the one above the other?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I don't really have any experience with BCP or Bulk Insert. I do avoid using xp_cmdshell because it can be dangerous and on my 2005 servers it is disabled, so for that reason I'd probably go with Bulk Insert.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question