Post reply

Dynamic SQL Return value

  • 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.

  • August 7, 2008 at 6:10 am

    #854380

    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

    */

  • August 7, 2008 at 8:25 am

    #854484

    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]

  • August 7, 2008 at 8:53 am

    #854509

    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

  • August 8, 2008 at 4:40 am

    #855020

    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?

  • August 8, 2008 at 6:43 am

    #855094

    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

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

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