How to get sp_executesql result into a variable in dynamic query?

Question

In the below procedure, the Select statement in the second IF statement returns results (e_id).

But I want to store results of the select statement in one variable. I am deleting the records from temporary table and inserting results in temporary table using this variable. But @myVariable returns null.

CREATE TYPE TableVariable AS TABLE
(
    id INT IDENTITY(1,1),
    field_ids INT,
    value VARCHAR(MAX)
)

Type created successfully.

USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Testing_eid]
    (@TableVar TableVariable READONLY,
     @C_id INT)
AS
    DECLARE @maxPK INT
    DECLARE @pk INT 
    DECLARE @fid SMALLINT
    DECLARE @is_List SMALLINT
    DECLARE @val VARCHAR(MAX)
    DECLARE @field_Type VARCHAR(50)
    DECLARE @Where VARCHAR(MAX)
    DECLARE @SQL NVARCHAR(2000)
    DECLARE @myVariable INT
    DECLARE @SQLSTATUS NVARCHAR(2000)

    --Declare @TempEntities table
    --(
    --entity_id int
    --)
   Set @pk = 1
BEGIN
   BEGIN TRY
    SET NOCOUNT ON;
    create table #Tempentities (e_id int)
     Select @maxPK = count(*) From @TableVar 

     While @pk <= @maxPK
    BEGIN
        SELECT @fid= field_ids FROM @TableVar where id=@pk;
        SELECT @val= value     FROM @TableVar where id=@pk;
        SELECT @field_Type=type,@is_List=is_list FROM FIELD WHERE ID=@fid


            BEGIN
            IF(@is_List = 0 AND @pk=1)
            BEGIN
            Set @SQL = 'SELECT DISTINCT v1.e_id from values v1 inner join listings l ON v1.e_id = l.e_id WHERE l.c_id = ' +CAST(@C_id AS nvarchar(20))
            SET @SQL += ' AND field_id='+CAST(@fid AS nvarchar(20))+ ' and(value IN(SELECT val FROM spliting('''+CAST(@val AS VARCHAR(2000))+''','','')))' 
            INSERT INTO #Tempentities(entity_id) EXECUTE SP_EXECUTESQL @SQL
            END
            IF(@is_List = 0 AND @pk>1)
            BEGIN
            SELECT @field_type
            SELECT * FROM #Tempentities
            Set @SQL = 'SELECT DISTINCT v1.e_id from values v1 inner join listings l ON v1.e_id = l.e_id WHERE l.catalog_id = ' +CAST(@C_id AS nvarchar(20))
            SET @SQL += ' AND field_id='+CAST(@fid AS nvarchar(20))+ ' AND(value IN(SELECT val FROM spliting('''+CAST(@val AS VARCHAR(2000))+''','',''))) AND (v1.entity_id IN(SELECT * FROM #Tempentities))' 
            --INSERT INTO @TempEntities(entity_id) EXECUTE SP_EXECUTESQL @SQL
             EXECUTE SP_EXECUTESQL @SQL, N'@myVariable INT OUTPUT', @myVariable=@myVariable OUTPUT
             --Execute @entityid=SP_EXECUTESQL @SQL
              Delete from #Tempentities
              insert into #Tempentities(entity_id) values(@myVariable)

            END
            END
             SELECT * FROM #Tempentities
     select @pk = @pk + 1
     --select @pk

    END

    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END

score 1 · Answer 1 · edited May 23 '17 at 12:18

Using sp_executesql with output parameter

You can refer to the following answers to solve this issue

sp_executesql is not necessary in your issue

If the goal is to insert values into #Tempentities , there is no need to store values in a variable, you can use the temp table inside the dynamic query:

SET @SQL = 'INSERT INTO #Tempentities(entity_id) SELECT DISTINCT v1.e_id from values v1 inner join listings l ON v1.e_id = l.e_id WHERE l.catalog_id = ' +CAST(@C_id AS nvarchar(20))
SET @SQL += ' AND field_id='+CAST(@fid AS nvarchar(20))+ ' AND(value IN(SELECT val FROM spliting('''+CAST(@val AS VARCHAR(2000))+''','',''))) AND (v1.entity_id IN(SELECT * FROM #Tempentities))'

The whole query will looks like:

USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Testing_eid]
    (@TableVar TableVariable READONLY,
     @C_id INT)
AS
    DECLARE @maxPK INT
    DECLARE @pk INT 
    DECLARE @fid SMALLINT
    DECLARE @is_List SMALLINT
    DECLARE @val VARCHAR(MAX)
    DECLARE @field_Type VARCHAR(50)
    DECLARE @Where VARCHAR(MAX)
    DECLARE @SQL NVARCHAR(2000)
    DECLARE @myVariable INT
    DECLARE @SQLSTATUS NVARCHAR(2000)

    --Declare @TempEntities table
    --(
    --entity_id int
    --)
   Set @pk = 1
BEGIN
   BEGIN TRY
    SET NOCOUNT ON;
    create table #Tempentities (e_id int)
     Select @maxPK = count(*) From @TableVar 

     While @pk <= @maxPK
    BEGIN
        SELECT @fid= field_ids FROM @TableVar where id=@pk;
        SELECT @val= value     FROM @TableVar where id=@pk;
        SELECT @field_Type=type,@is_List=is_list FROM FIELD WHERE ID=@fid


            BEGIN
            IF(@is_List = 0 AND @pk=1)
            BEGIN
            Set @SQL = 'SELECT DISTINCT v1.e_id from values v1 inner join listings l ON v1.e_id = l.e_id WHERE l.c_id = ' +CAST(@C_id AS nvarchar(20))
            SET @SQL += ' AND field_id='+CAST(@fid AS nvarchar(20))+ ' and(value IN(SELECT val FROM spliting('''+CAST(@val AS VARCHAR(2000))+''','','')))' 
            INSERT INTO #Tempentities(entity_id) EXECUTE @SQL
            END
            IF(@is_List = 0 AND @pk>1)
            BEGIN
            SELECT @field_type
            SELECT * FROM #Tempentities
            Delete from #Tempentities
            Set @SQL = 'insert into #Tempentities(entity_id) SELECT DISTINCT v1.e_id from values v1 inner join listings l ON v1.e_id = l.e_id WHERE l.catalog_id = ' +CAST(@C_id AS nvarchar(20))
            SET @SQL += ' AND field_id='+CAST(@fid AS nvarchar(20))+ ' AND(value IN(SELECT val FROM spliting('''+CAST(@val AS VARCHAR(2000))+''','',''))) AND (v1.entity_id IN(SELECT * FROM #Tempentities))' 
            --INSERT INTO @TempEntities(entity_id) EXECUTE SP_EXECUTESQL @SQL
             EXECUTE (@SQL)



            END
            END
             SELECT * FROM #Tempentities
     select @pk = @pk + 1
     --select @pk

    END

    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
END

How to get sp_executesql result into a variable in dynamic query?

1 Answers1