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

Question

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

mnr123

SSC-Addicted

Points: 427
More actions
July 20, 2017 at 8:10 am

#343649

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

sgmunson SSC Guru Points: 110640 More actions · Answer 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)

mnr123 SSC-Addicted Points: 427 More actions · Answer 3

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

sgmunson SSC Guru Points: 110640 More actions · Answer 4

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)