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

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

http://ift.tt/1zPHjio

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]' `

GO

2. Function-

enter code here

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

enter code here

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'

Regards,

Jeena