use exec(@sql) in if exists and get output parameters dynamic sql

  • I have result set from @runtimequery like below .I want to set 2 parameters according to the
    in it .Though I am not getting any result set with the query below 
    Declare @Sql NVARCHAR(MAX)
    SET @Sql = N'IF EXISTS( SELECT * FROM ' + QUOTENAME(@RunTimeQuery)
           + N' where CODE is null )'
        + N'BEGIN
          SET @Canbedeleted = 1;
         END
         ELSE
         BEGIN
          SET @dependent = 0;
         END'

                 print @Sql
     exec sp_executesql @Sql
           ,@Canbedeleted OUTPUT
           ,@dependent OUTPUT

  • mnr123 - Thursday, July 20, 2017 8:10 AM

    I have result set from @runtimequery like below .I want to set 2 parameters according to the
    in it .Though I am not getting any result set with the query below 
    Declare @Sql NVARCHAR(MAX)
    SET @Sql = N'IF EXISTS( SELECT * FROM ' + QUOTENAME(@RunTimeQuery)
           + N' where CODE is null )'
        + N'BEGIN
          SET @Canbedeleted = 1;
         END
         ELSE
         BEGIN
          SET @dependent = 0;
         END'

                 print @Sql
     exec sp_executesql @Sql
           ,@Canbedeleted OUTPUT
           ,@dependent OUTPUT

    You're missing the @params parameter.

    exec sp_executesql @Sql , N'@Canbedeleted bit OUTPUT, @dependent bit OUTPUT'
           ,@Canbedeleted OUTPUT
           ,@dependent OUTPUT

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • You don't show what @runtimequery is defined as.   If it's defined as a table, you've got a different problem altogether, as once you go dynamic sql, you lose context and that variable has no meaning within the dynamic sql.   If it's just a string, you can't just SELECT from it.   That's not how things work.   You might not need to make it quite this complicated.    Please post the actual query that produces the shown result set, and we may have an easier way to do what you're looking to accomplish.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • This is the full query 

    USE DDS_QA

    DECLARE
    @Dimension_Type varchar(100),
    @DBID varchar(10),
    @code nvarchar(max),
    @Canbedeleted INT = '',
    @dependent INT = '' ,
    @TableID varchar(100),
    @Dimension_TypeList varchar(500),
    @Table_Name varchar(300),
    @Database_Name varchar(100),
    @DataType varchar(100),
    @RunTimequery nvarchar(max),
    @Code_Col_Expr varchar (100), 
    @series_table varchar(30),
    @Display_Col_Expr varchar (100)

    SET @DBID = 'WDIWKG'
    SET @Dimension_Type = 'country'
    set @code = 'usa'
    set @series_table = 'WDI_SERIES_DIM'
    set @Database_Name = 'WDI Working'

    SET @RunTimeQuery = 'select DISTINCT ''Normal list - '' As Name ,(
    SELECT STUFF (( SELECT '' , '' +listname FROM (SELECT listname FROM [dbo].[DDSC_ListDetails] L
    INNER JOIN [dbo].[DDSC_ListHeader] H
    ON L.ListHeaderID =H.ListHeaderID AND UPPER(H.DatabaseID) = '''+ @DBID + ''' AND L.DimensionType = ''' + @Dimension_Type + ''' AND L.DimensionCode = ''' + @Code + ''') X
         FOR XML PATH ('''')),1,1,'''') series) AS CODE
    union
    SELECT DISTINCT ''AggregationGroup -'' As Name ,(
    SELECT STUFF (( SELECT '','' +Aggregation_Rule FROM (SELECT Aggregation_Rule FROM [' + @Database_Name + '] .dbo.' + @series_table + ' where Aggregation_Rule like ''%Group_code%'') X
         FOR XML PATH ('''')),1,1,'''') series) AS CODE'

                     print @RunTimeQuery
                                  exec @RunTimeQuery

    Declare @Sql NVARCHAR(MAX)
    SET @Sql = N'IF EXISTS( SELECT * FROM ' + QUOTENAME(@RunTimeQuery)
    + N' where CODE is null )'
    + N'BEGIN
    SET @Canbedeleted = 1;
    END
    ELSE
    BEGIN
    SET @dependent = 0;
    END'

    print @Sql
    exec sp_executesql @Sql
    ,@Canbedeleted OUTPUT
    ,@dependent OUTPUT

  • I'm not sure what you thought the QUOTENAME function was going to do, but you're basically using that function against the text of your executed SQL query.   Once you execute that query, the result set will be returned to the calling application, and you'd have to have a way to parse that resultset, so if you were running this query from SSMS, that isn't going to work because SSMS doesn't parse the resultset for you.   It just hands that back to you in the grid (or in text format if you have that option set).   If you want to process the resultset, you'll have to place those rows in a table, by either preceding the execute statement with a table creation and then an INSERT INTO, or you'll have to perform that task within that dynamic SQL.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

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

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