Sql-server – ny way to access the variables in a dynamic sql which is declared outside the dynamic sql

sql server

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

Best Answer

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.