SQL EXEC(@QUERY) does not have access to global variables

Question

I am writing a query which need to append the database name dynamically.

The query gets values from a table (in another database) , and I would interested to know if there is a better approach/way.

So my query looks like following :

DECLARE @TagNames AS VARCHAR(MAX) --Probably this is not a Global variable
DECLARE @QUERY AS VARCHAR(MAX)

        SET @QUERY ='SELECT @TagNames = coalesce( @TagNames + '','','''') +    
                     fldTagName FROM '+ dbo.fnGetZiConfigValue('KEYNAME')+'.dbo.tblTags Where  
                     fldInterpolate = 1 AND fldUnitID = 13'

        EXEC (@QUERY)

When I execute this , I get an error,

Must declare the scalar variable "@TagNames".

IVNSTN · Accepted Answer · 2018-03-14 06:42:07Z

5

It is for sure not a global variable. You have to "declare" it inside dynamic SQL and return outside just like you'd do this with stored procedure.

DECLARE @TagNames AS VARCHAR(MAX) --Probably this is not a Global variable
DECLARE @QUERY AS NVARCHAR(MAX)
    SET @QUERY = N'SELECT @TagNames = coalesce( @TagNames + '','','''') +    
                 fldTagName FROM '+ QUOTENAME(dbo.fnGetZiConfigValue('KEYNAME'))+'.dbo.tblTags Where  
                 fldInterpolate = 1 AND fldUnitID = 13'

exec sp_executesql @QUERY, N'@TagNames VARCHAR(MAX) OUTPUT', @TagNames OUTPUT

for sp_executesql dynamic sql and it's params declaration must be NVarchar.

edited Mar 14, 2018 at 6:42

answered Mar 14, 2018 at 6:28

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Simsons Over a year ago

on exec sp_executesql , Getting error , Unclosed quotation mark after the character string '@TagNames VARCHAR(MAX) OUTPUT), @@TagNames OUTPUT, If I close with quotations exec sp_executesql @@QUERY, N'@@TagNames VARCHAR(MAX) OUTPUT), @@TagNames OUTPUT' the getting an error Incorrect syntax near ','.

Simsons Over a year ago

My bad for @@ (double @) , SO is not letting me with single @

IVNSTN Over a year ago

typo fixed (was ) instead of ').

Simsons Over a year ago

Thanks , If you don't mind a followup question , How do I add an extra parameter to the query to substitute hardcoded fldUnitId value. I tried with exec sp_executesql '@QUERY, N'@IPSet VARCHAR(50),@TagNames VARCHAR(MAX) OUTPUT', '@TagNames OUTPUT, but getting this error , The formal parameter "@IPSet" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

Simsons Over a year ago

Updated this and it's working , exec sp_executesql @QUERY, N'@IPSet VARCHAR(50),@TagNames VARCHAR(MAX) OUTPUT', '@IPSet,@TagNames OUTPUT

Collectives™ on Stack Overflow

SQL EXEC(@QUERY) does not have access to global variables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related