Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Robert Marda How to Build Dynamic Stored Procedures RE: How to Build Dynamic Stored Procedures

  • January 8, 2008 at 4:27 pm

    #765947

    markybse (1/8/2008)

    ... the first exec line fails and the second exec line works. I am at a loss as to why.

    DECLARE @SELECT varchar(100)

    DECLARE @FROM varchar(100)

    DECLARE @DynamicSQL1 varchar(8000)

    DECLARE @DynamicSQL2 varchar(8000)

    DECLARE @DynamicSQL3 varchar(8000)

    SET @SELECT = 'SELECT '

    SET @FROM = 'FROM DATA_SOURCE'

    EXEC (@SELECT + @DynamicSQL1 + @DynamicSQL2 + @DynamicSQL3 + @FROM)

    EXEC (@SELECT +''+ @DynamicSQL1 +''+ @DynamicSQL2 +''+ @DynamicSQL3 +''+ @FROM)

    I'm not sure of the exact problem, but I'd guess it stems from one of two likely sources.

    1. The @DynamicSQL# variables need an interceding space in order for their concatenation to result in syntactically correct SQL.

    Try:

    EXEC (@SELECT + ' ' + @DynamicSQL1 + ' ' + @DynamicSQL2 + ' ' + @DynamicSQL3 + ' ' + @FROM)

    2. You are trying to concatenate Null values. The solution would be to recast the @DynamicSQL# as a space if the value is Null.