CODEWITHSUNDEEP
sqldynamicquery
Anoop Nair
Anoop Nair

Reputation: 35

#Temporary table ,#Dynamic Query

This is my query:

DECLARE @DB_NAME VARCHAR(50)
SET @DB_NAME='CC_GET'

DECLARE @CMD_BUSID VARCHAR(300)
SET @CMD_BUSID='(SELECT DISTINCT A.BUSID  FROM LTBAUDITTYPE A WHERE BUSINESS IN ( SELECT DISTINCT CONTACT_BUSINESS FROM '+@DB_NAME+'.DBO.LTBCONTACT))'
--DECLARE @BUS_ID TABLE(BUS INT)
--INSERT INTO @BUS_ID
--DROP TABLE #BUS_ID
CREATE TABLE #BUS_ID( BUS INT)
INSERT INTO #BUS_ID
EXEC(@CMD_BUSID)

--SELECT * FROM #BUS_ID 

DECLARE @CMD_BUSNAME VARCHAR(300)
SET @CMD_BUSNAME='(SELECT DISTINCT  A.BUSINESS FROM LTBAUDITTYPE A WHERE BUSINESS IN ( SELECT DISTINCT CONTACT_BUSINESS FROM '+@DB_NAME+'.DBO.LTBCONTACT))'
--DECLARE @BUS_NAME TABLE(BUS1 NVARCHAR(50))
--INSERT INTO @BUS_NAME
--DROP TABLE #BUS_NAME
CREATE TABLE #BUS_NAME(BUS1 VARCHAR(50))
INSERT INTO #BUS_NAME
EXEC(@CMD_BUSNAME)
--SELECT * FROM  #BUS_NAME


declare @cmd_ltbEnvPriority varchar(500)

set @cmd_ltbEnvPriority='insert into #Track_info(BusID,BusName,CC_DBName,Table_Name,[ID missed in GEEHS])
  (
  select  (select bus from #BUS_ID ) BusID,(select bus1 from  #BUS_NAME) BusName,
  @db_name CC_DBName,''ltbEnvPriority'' Table_Name,a.words [ID missed in GEEHS]
 from '+@DB_NAME+'.dbo.ltbEnvPriority a where  not exists (select 1 from ltbEnvPriority b where a.words=b.words))'

 --print  @cmd_ltbEnvPriority

 EXEC (@cmd_ltbEnvPriority)

After the above execution its giving me the below error:

Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@db_name".
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

But as you can see above the variable is already declared! Please help!

Upvotes: 0

Views: 44

Answers (1)

Tim Rogers
Tim Rogers

Reputation: 21713

Variables have to be declared with in the script that is executing. You have created another script in @cmd_ltbEnvPriority and then executed that. It has no knowledge of variables from the outer script that has called it.

Upvotes: 1

Related Questions