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

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:

• 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

• Create a dynamic query using this names like select @param1+","+@param2 in a function.Which accepts @SPName as the parameter

• call this function in my sps to get the dynamic sql

• Execute this sql in my procedure and get the param 1 and param 2 value (param 1 +param2)

• 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];

1. 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 (''))

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.

StackExchange Database Administrators Q#84180, answer score: 2