SQL Server : execute dynamic sql help please

Question

EXEC SP_EXECUTESQL 
            @DynamicSQL
        ,   N'@HostIDs VARCHAR(MAX) OUTPUT'
        ,   @HostIDs OUTPUT;

PRINT @HostIDs;

SELECT @HostIDs AS HostIDs;
SET @UpdateSQL = '
            EXECUTE [dbo].[usp_Win7_HostUpdater_NEW]
            @HostID = ''' + @HostIDs + ''' , 
            @PackageID = ''' + @PackageID  + ''' , 
            @MigrationFlag = ''' + @MigrationFlagID + ''' , 
            @Manufacturer = ' + @Manufacturer + ' , 
            @Product = ' + @Product + ' , 
            @Version = ' + @Version + ' ,
            @Reason = ' + @Reason + ' ,
            @Contact = ' + @Contact + '
        ';

        SELECT @UpdateSQL AS UpdateSQL;
        PRINT @UpdateSQL;           
        EXEC( @UpdateSQL )  
  END

I have a stored procedure on both a SQL Server 2005 and 2008 in which the above code is the last part of

it returns a VARCHAR(MAX) of numbers separated by commas.

Now this returned value is large upwards of 600k characters. If I execute this on a SQL Server 2005 it works like 50% of the time, @HostIDs is populated always and @UpdateSQL gets generated with the correct values and is executed.

On SQL Server 2008, @HostIDs is populated but @UpdateSQL is always NULL

This is weirding me out tremendously

Can anyone maybe shed some light on my odd problem?

Probably one of the other variables is NULL. Did you test all of them? — Aaron Bertrand
– Aaron Bertrand, Commented Sep 25, 2012 at 14:33
Why do you need to create the @UpdateSQL in the first place? (It just looks like a target for SQL injection.) Why do you not just have the command as an actual statement? — freefaller
– freefaller, Commented Sep 25, 2012 at 14:34

RichardTheKiwi · Accepted Answer · 2012-09-25 23:46:44Z

1

Check these out

SET CONCAT_NULL_YIELDS_NULL OFF
select 'abc' + null + 'def'
--- abcdef

SET CONCAT_NULL_YIELDS_NULL ON
select 'abc' + null + 'def'
--- NULL

That's one way to get around the problem, which is to set it off before your string building and back on after. Any NULL in the sequence of string concatenation renders the entire statement NULL, which explains it works like 50% of the time - these are when all of the variables are non-null.

Completely agree with freefaller though, unless the question's an abstraction of a larger puzzle, there's no reason to build a dynamic SQL and EXEC it when a direct execution will work for the particular snippet shown.

answered Sep 25, 2012 at 23:46

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

d89761 Over a year ago

Very cool, I didn't know about this feature! Does it perform better than doing an ISNULL? It won't work for this question because it probably needs to insert the value null in null cases, and not cause an empty string, but I could definitely have used this feature once or twice in the past.

d89761 · Accepted Answer · 2012-09-25 16:19:29Z

1

If any of the parameters are null, the entire statement will be null. You can work around it by doing something like this (and I don't know what the data types are, but sometimes you need to cast them to varchar from int/bool/etc. types to make the concatenation work):

SET @UpdateSQL = '
            EXECUTE [dbo].[usp_Win7_HostUpdater_NEW]
            @HostID = ' + ISNULL('''' + @HostIDs + '''', 'null') + ' , 
            @PackageID = ' + ISNULL('''' + @PackageID + '''', 'null')  + ' , 
            @MigrationFlag = ' + ISNULL('''' + @MigrationFlagID + '''', 'null') + ' , 
            @Manufacturer = ' + ISNULL(@Manufacturer, 'null') + ' , 
            @Product = ' + ISNULL(@Product, 'null') + ' , 
            @Version = ' + ISNULL(@Version, 'null') + ' ,
            @Reason = ' + ISNULL(@Reason, 'null') + ' ,
            @Contact = ' + ISNULL(@Contact, 'null') + '
        ';

answered Sep 25, 2012 at 16:19

d89761

1,4449 silver badges11 bronze badges

1 Comment

David OBrien Over a year ago

thank you... I didn't know if one was null the whole thing would be null

Kumar_2002 · Accepted Answer · 2012-09-25 23:05:04Z

0

It causes because you are not handling nulls

you can use sp_executesql instead of exec it has some benefits over exec

answered Sep 25, 2012 at 23:05

Kumar_2002

6041 gold badge6 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL Server : execute dynamic sql help please

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related