trying to create completely dynamic SP_EXECUTESQL call in proc

  • EXEC SP_EXECUTESQL @sql,

    @Parameters,

    @lColumnValue

    is there a way to make the third col in this call to be truly dynamic. My @Parameters is a generated string however it appears in all the examples i've seen the third col in the exectute sql call is hard coded.

    Is there a way to make this dynamic?

  • here's an example of what i'm trying to do. I'm getting an "Error converting data type nvarchar to int." error. i want the script to be smart enough so that it only includes @param1 and or @param2 is they're not null. is this possible?

    DECLARE @param1 INT = NULL,

    @param2 INT = NULL,

    @select NVARCHAR(1000) = '',

    @params NVARCHAR(200),

    @paramValues NVARCHAR(1000)

    SET @param1 = 1

    -- SET @param2 = 1

    SET @select = 'SELECT TOP 1 * FROM sys.columns (NOLOCK)

    where ' + CASE

    WHEN @param1 IS NOT NULL THEN 'column_id = @param1'

    ELSE ''

    END + CASE

    WHEN @param2 IS NOT NULL THEN ' or column_id = @param2'

    ELSE ''

    END

    SET @params= CASE

    WHEN @param1 IS NOT NULL THEN '@param1 int '

    ELSE ''

    END + CASE

    WHEN @param2 IS NOT NULL THEN ' , @param2 int'

    ELSE ''

    END

    SET @paramValues = CASE

    WHEN @param1 IS NOT NULL THEN '@param1 = ' + CAST(@param1 AS VARCHAR)

    ELSE ''

    END + CASE

    WHEN @param2 IS NOT NULL THEN ', @param2 = ' + CAST(@param2 AS VARCHAR)

    ELSE ''

    END

    PRINT @select --SELECT TOP 1 * FROM sys.columns (NOLOCK) where column_id = @param1

    PRINT ''

    PRINT @params --@param1 int

    PRINT ''

    PRINT @paramValues --@param1 = 1

    EXEC Sp_executesql

    @select,

    @params,

    @paramValues

  • You can modify the SQL Statement so it will get 2 parameters each time, but won't always use both of them. Here is a small example that is based on your example:

    DECLARE @param1 INT,

    @param2 INT ,

    @select NVARCHAR(1000),

    @params NVARCHAR(200),

    @paramValues NVARCHAR(1000)

    SET @param1 = 1

    SET @param2 = 2

    SET @select = 'SELECT * FROM sys.columns (NOLOCK)

    where ' + CASE

    WHEN @param1 IS NOT NULL THEN 'column_id = @param1'

    ELSE '1=2'

    END + CASE

    WHEN @param2 IS NOT NULL THEN ' or column_id = @param2'

    ELSE ' or 1=2'

    END

    SET @params= '@param1 int, @param2 int '

    EXEC sp_executesql

    @select,

    @params,

    @param1, @param2

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Yes. You just build your parameters string dynamically.

    declare @params nvarchar(max) = '';

    if @Param1 is not null

    set @params += ', @Param1 int';

    if @Param2 is not null

    set @params += ', @Param2 int';

    set @params = stuff(@Params, 1, 1, ''); -- to get rid of any leading comma

    Then, when you call sp_executeSQL, put @params where you put the parameters list.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • thanks guys

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

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