locked
Passing parameter in query dynamically RRS feed

  • Question

  • -- I have a query that is stored in a table column, the query contains a parameter 

    -- I tried to pass the parameter, but failed. How can I accomplish this task?

    CREATE TABLE #testFullQuery(longString varchar(Max))
    INSERT INTO #testFullQuery(longString) VALUES ('SELECT * FROM @NewValue');

    DECLARE @SourceCode nvarchar(MAX);
    SELECT @SourceCode = longString FROM #testFullQuery
    DECLARE @NewValue VARCHAR(MAX) = 'TableName';  

    DECLARE @FullQuery nvarchar(MAX);
    SET @FullQuery = '';
    SET @FullQuery = @SourceCode 
    EXEC sp_executesql @FullQuery;
    print @fullQuery, @NewValue


    l__j

    Thursday, February 19, 2015 7:27 PM

Answers

  • Simply replace the placeholder with the value of the variable:

    DECLARE @testFullQuery TABLE (longString varchar(Max))
    INSERT INTO @testFullQuery(longString) VALUES ('SELECT * FROM @NewValue');
     
    DECLARE @SourceCode nvarchar(MAX);
     SELECT @SourceCode = longString FROM @testFullQuery
     DECLARE @NewValue VARCHAR(MAX) = 'TableName';  
     
    DECLARE @FullQuery nvarchar(MAX); 
     SET @FullQuery = ''; 
     SET @FullQuery = REPLACE(@SourceCode,'@NewValue',@newValue)
     EXEC sp_executesql @FullQuery;
     print @fullQuery

    Thursday, February 19, 2015 7:46 PM