Var inside a string var

Question

question

Pedro Ivo asked Apr 20, '10

Var inside a string var

Why i get the error: Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable "@cd_empresa". I declared it already. Sry for the bad english.

DECLARE @SQL VARCHAR(250)                    
DECLARE @AONDE VARCHAR(250)                    
DECLARE @CP VARCHAR(250)                    
DECLARE @BANCO VARCHAR(23)                    
DECLARE @TABELA VARCHAR(50)                    
DECLARE @TB VARCHAR(250)                    
DECLARE @cnpj NUMERIC(14)                    
DECLARE @cd_empresa INT                    
                    
SET @cnpj = 9211813000103                    
                    
                    
SELECT @BANCO = BANCO                     
FROM ESTAB_GERDOR                     
WHERE NUM_CNPJ_ESTAB_GERDOR = @cnpj                    
SET @BANCO = @BANCO+'.DBO'                    
                    
SET @TABELA = '.CADEntidade'                    
SET @TB = @BANCO+@TABELA                    
SET @CP = ' @cd_empresa = cd_entidade'                    
SET @AONDE = ' WHERE cnpj_cpf = '+CONVERT(VARCHAR(14),@cnpj)                    
SET @SQL = 'SELECT '+@CP+' FROM '+@TB+@AONDE                    
PRINT (@SQL)                    
EXEC (@SQL)                    
PRINT @cd_empresa

query select

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-20T23:15:29Z

Squirrel 1 answered Apr 20, '10

"I declared it already"

Actually. . . NO. You didn't. When you use exec(), it runs in another scope, the variable is not declared in that scope.

use sp_executesql instead

exec sp_executesql @SQL, N'@cd_empresa INT OUTPUT', @cd_empresa OUTPUT

also read this http://www.sommarskog.se/dynamic_sql.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question