Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql

Question

Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql.

I need to do the following:

1. Query the information schema to get the parameter names to an sp SELECT PARAMETER_NAME, DATA_TYPE from information_schema.parameters where specific_name=@SPName
2. Create a dynamic query using this names like select @param1+","+@param2 in a function.Which accepts @SPName as the parameter
3. call this function in my sps to get the dynamic sql
4. Execute this sql in my procedure and get the param 1 and param 2 value (param 1 +param2)
5. Update this value in my spcallTracking Table SpCallDetails field

I have tried this method ,but getting error like Must declare the scalar variable (param 1) and the reason i Guess is Dynamic sql runs in a different session and therefore variables defined outside the dynamic query will not be available to the dynamic query

https://stackoverflow.com/questions/14960777/must-declare-the-scalar-variable-error-sql

1. Table-

CREATE TABLE [dbo].[Single](
[ID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Age] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [Name] ASC,
    [Age] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

---

2. Function-

CREATE FUNCTION [dbo].[udf_getSPName]
(
    @SPName nvarchar(100)
)
RETURNS  nvarchar(4000)
AS
BEGIN
DECLARE @ReturnString nvarchar(4000)='SELECT '
DECLARE @tmpParameterDetails table (ParamName varchar(200),DataType varchar(200))
INSERT @tmpParameterDetails
    SELECT  PARAMETER_NAME, DATA_TYPE from information_schema.parameters
    where specific_name=@SPName
    SET @ReturnString=@ReturnString+
                (SELECT STUFF(
                    (SELECT ','+ParamName
                     FROM 
                            @tmpParameterDetails

                    FOR XML PATH (''))
                 ,1, 1, ''))

     Return @ReturnString

  RETURN @ReturnString
END

3. Procedure-

CREATE PROCEDURE [dbo].[usp_Single_insert]
    @Name nvarchar(50),
    @Age  int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   DECLARE @ReturnString as nvarchar(max)


    INSERT INTO [Single]
           ([ID]
           ,[Name]
           ,[Age])    
     VALUES
           (NEWID(),
            @Name,
            @Age)
            DECLARE  @CreatedBy as nvarchar(100)='test'
            DECLARE  @CreatedOn as datetime=getdate()
            DECLARE @ColV nvarchar(max)

           DECLARE @ParmDefinition nvarchar(500);            

    SET @ReturnString=[dbo].[udf_getSPName]('usp_Single_insert')
    EXECUTE sp_executesql @ReturnString

END

Answer 1

Unless I am mistaken, the request is to ultimately dynamically construct a string to be executed that would be able to pull in local variable values that exist in the context that is executing the Dynamic SQL. For example:

The function constructs and returns something along the lines of:

SET @ReturnString = N'@Param1 INT = ''+@Param1+'', @Param2 VARCHAR(15) = ''+Param2+''';

The proc that calls the function EXECs that Dynamic SQL in the hopes of replacing the escaped-quoted parameters as follows:

SET @SQL = N'INSERT INTO dbo.spcallTracking (ProcName, SpCallDetails) VALUES ('''
              + OBJECT_NAME(@@PROCID)
              + N''', '''
              + @ReturnString
              + N''');';

EXEC sp_executesql @SQL;

and the desire is that @ReturnString actually renders as:

@Param1 INT = 2, @Param2 VARCHAR(15) = 'bob'

Unfortunately no, you cannot access variables that exist in the parent context. That would require an eval() (JavaScript / C# 1 / C# 2 / PHP)-like system stored procedure to execute the dynamic SQL in the current context and no such functionality exists, at least not in T-SQL.

Answer 2

I'm not sure I understand your question properly, but I'll take a stab at it anyway:

USE tempdb
GO


CREATE TABLE [dbo].[Single] (
    [ID] [uniqueidentifier] NOT NULL
    ,[Name] [nvarchar](50) NOT NULL
    ,[Age] [nvarchar](50) NOT NULL
    ,CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED ([ID] ASC,[Name] ASC,[Age] ASC) 
)
GO



CREATE FUNCTION [dbo].[udf_getSPName]
(
    @SPName nvarchar(100)
)
RETURNS @results TABLE (
    SQLaction nvarchar(max),
    params nvarchar(max)
) 
AS
BEGIN
    DECLARE @Params nvarchar(4000)=' '
    DECLARE @Names nvarchar(4000)=' '
    DECLARE @tmpParameterDetails table (ParamName sysname,DataType sysname, character_maximum_length int)

    INSERT @tmpParameterDetails
    SELECT  PARAMETER_NAME, DATA_TYPE, character_maximum_length from information_schema.parameters
    where specific_name=@SPName

    SET @Params=@Params+
                (SELECT STUFF(
                    (SELECT ','+ParamName + ' ' + DataType + ISNULL('(' + CAST(character_maximum_length AS varchar(6)) + ')','')
                     FROM @tmpParameterDetails
                     FOR XML PATH (''))
                 ,1, 1, ''))

    SET @Names = 'SELECT ' + (SELECT STUFF(
                    (SELECT ','+ParamName
                     FROM @tmpParameterDetails
                     FOR XML PATH (''))
                 ,1, 1, ''))

    INSERT INTO @results VALUES (@Names, @Params)
    RETURN 
END
GO


CREATE PROCEDURE [dbo].[usp_Single_insert]
    @Name nvarchar(50),
    @Age  int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   DECLARE @ReturnString as nvarchar(max)


    INSERT INTO [Single]
           ([ID]
           ,[Name]
           ,[Age])    
     VALUES
           (NEWID(),
            @Name,
            @Age)
            DECLARE  @CreatedBy as nvarchar(100)='test'
            DECLARE  @CreatedOn as datetime=getdate()
            DECLARE @ColV nvarchar(max)

           DECLARE @ParmDefinition nvarchar(max);            

    SELECT @ParmDefinition = params,
        @ReturnString = SQLaction
    FROM [dbo].[udf_getSPName](OBJECT_NAME(@@PROCID))

    PRINT @ParmDefinition
    PRINT @ReturnString
    EXECUTE sp_executesql @ReturnString, @ParmDefinition, @Name, @Age

END

GO


EXEC usp_Single_insert 'me', 37