CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005stored-procedures
David OBrien
David OBrien

Reputation: 103

SQL Server : execute dynamic sql help please

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?

Upvotes: 0

Views: 607

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

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.

Upvotes: 1

Kumar_2002
Kumar_2002

Reputation: 604

It causes because you are not handling nulls

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

Upvotes: 0

d89761
d89761

Reputation: 1434

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') + '
        ';

Upvotes: 1

Related Questions