3

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
  • Can you please show the code that you tried? – JNK Dec 3 '14 at 13:27
2

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.

0

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
  • I was looking for a method to avoid explicitly specifying parameter names in execute sql.As per srutzky its not possible – Jeena Dec 5 '14 at 9:01
  • What exactly are you trying to avoid? Passing Name and Age to sp_executesql? – spaghettidba Dec 5 '14 at 9:05
  • @Jeena is trying to avoid any hard-coding of anything. The idea is to have a completely dynamic system to capture whatever input params are in whatever procs. This way that same piece of code can be slapped into all procs without modification. So this would include dynamically passing in not just the param names, but also their values. – Solomon Rutzky Dec 5 '14 at 16:18
  • Yes you are right srutzky – Jeena Dec 8 '14 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.