declare @Reg_No char(7)
set @Reg_No = '18S3320'
go
declare @ClientCode CHAR(10)
SET @ClientCode = 'SELECT client_code FROM [dbo].[registration] AS [R] WHERE [R].[reg_no]= ' + @Reg_No go
declare @ClientHasMapping BIT
set @ClientHasMapping = 0
SET @ClientHasMapping =(SELECT CAST(CASE WHEN EXISTS(SELECT * FROM Dbo.[AnalyteMapping] AS [AM] where [AM].[client_code] = @ClientCode) THEN 1 ELSE 0 END AS BIT)) go
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data GO
CREATE TABLE #data
( ID int IDENTITY(1,1) PRIMARY KEY,
[COC ID] VARCHAR(6) NULL,
[COC GENERATED BY] VARCHAR(30) NULL,
[COC GENERATED Date] VARCHAR(10) NULL,
[LOCATION CODE] VARCHAR(50) NULL,
[LAB NAME] VARCHAR(10) NULL,
[IH LOG NUMBER] VARCHAR(6) NULL,
[TYPE] VARCHAR(30) NULL,
[PLANT SAMPLE ID] VARCHAR(22) NULL,
[MEDIA] VARCHAR(50) NULL,
[MEDIA LOT/SERIAL NUMBER] VARCHAR(14) NULL,
[SAMPLE DATE] VARCHAR(10) NULL,
[VOLUME] VARCHAR(7) NULL,
[TOTAL MINUTES] VARCHAR(7) NULL,
[SUBSTANCE / AGENT] VARCHAR(60) NULL,
[CAS NUMBER] VARCHAR(20) NULL,
[DURATION TYPE] VARCHAR(5) NULL,
OEL VARCHAR(128) NULL,
[OEL UNITS] VARCHAR(10) null,
[LAB AMOUNT] VARCHAR(128) NULL,
[LAB AMOUNT UNITS] VARCHAR(10) null,
[LAB CONCENTRATION] VARCHAR(128) NULL,
[LAB CONCENTRATION UNITS] VARCHAR(10) null,
[DETECTION LIMIT] numeric(20,8) null,
[DETECTION LIMIT UNITS] VARCHAR(10) null,
[DETECTION CODE] VARCHAR(10) null,
[LAB ANALYST] VARCHAR(10) null,
[LAB SAMPLE ID] VARCHAR(15) null,
[DATE RECEIVED] VARCHAR(10) NULL,
[Date Analyzed] VARCHAR(10) NULL,
[ANALYTICAL METHOD] VARCHAR(150) NULL,
[LAB COMMENTS] VARCHAR(200) NULL )
Declare @sql nvarchar(max)
set @sql = 'Insert into #data
( [COC ID] ,
[COC GENERATED BY] ,
[COC GENERATED Date] ,
[LOCATION CODE] ,
[LAB NAME] ,
[IH LOG NUMBER] ,
[TYPE] ,
[PLANT SAMPLE ID] ,
[MEDIA] ,
[MEDIA LOT/SERIAL NUMBER] ,
[SAMPLE DATE] ,
[VOLUME] ,
[TOTAL MINUTES] ,
[SUBSTANCE / AGENT] ,
[CAS NUMBER] ,
[DURATION TYPE] ,
OEL , [OEL UNITS] , [LAB AMOUNT] , [LAB AMOUNT UNITS] , [LAB CONCENTRATION] ,
[LAB CONCENTRATION UNITS] , [DETECTION LIMIT] , [DETECTION LIMIT UNITS] ,
[DETECTION CODE] , [LAB ANALYST] , [LAB SAMPLE ID] , [DATE RECEIVED] , [Date Analyzed] ,
[ANALYTICAL METHOD] , [LAB COMMENTS])
SELECT distinct
COALESCE([JTR].[CoCNo],'''') AS [COC ID] ,
'' as [COC GENERATED BY] ,'' as [COC GENERATED Date] ,
[JTR].[Sample Description] AS [LOCATION CODE] ,
''ChemCentre'' as [LAB NAME] ,'''' as [IH LOG NUMBER] ,
'''' AS [TYPE] ,[JTR].[SampleClientID] AS [PLANT SAMPLE ID] ,'''' as [MEDIA] ,
'''' as [MEDIA LOT/SERIAL NUMBER] ,'''' as [SAMPLE DATE] ,'''' as [VOLUME] ,
'''' as [TOTAL MINUTES] ,[JTR].[analyte] AS [SUBSTANCE / AGENT] ,
A.[CASNumber] AS [CAS NUMBER] ,'''' AS [DURATION TYPE] ,'''' AS OEL ,
'''' AS [OEL UNITS] ,'''' AS [LAB AMOUNT] ,'''' AS [LAB AMOUNT UNITS] ,
'''' AS [LAB CONCENTRATION] ,'''' as [LAB CONCENTRATION UNITS] ,
'''' as [DETECTION LIMIT] ,'''' as [DETECTION LIMIT UNITS] ,'''' as [DETECTION CODE] ,
'''' as [LAB ANALYST] ,LTRIM(RTRIM([JTR].Reg_No)) + ''/'' + [JTR].lab_no as [LAB SAMPLE ID] ,[JTR].[DateReceived_V3] as [DATE RECEIVED] ,'''' as [Date Analyzed] ,
'''' as [ANALYTICAL METHOD] ,'''' as [LAB COMMENTS]
FROM
[dbo].[JobTestResults_CHEVRON]( ' + @Reg_No + ',1) AS [JTR]
INNER JOIN [dbo].[analyte] AS [A]
ON a.[analyte_name]=[JTR].[analyte_name]
LEFT JOIN
(SELECT [ClientLongAnalyteNm], [ClientShortAnalyteNM], asp.method_no, asp.[analyte_name]
FROM [dbo].[AnalyteMapping] AM
INNER JOIN [dbo].[analyte_spec] AS [ASP] ON [ASP].[fk_Analyte] = [AM].[fk_Analyte] AND
[ASP].[fk_Method] = [AM].[fk_Method]
WHERE AM.client_code =
CASE WHEN @ClientHasMapping = 1 AND am.[analyte_name]=asp.[fk_Analyte]
AND am.[fk_Method]=asp.[fk_Method] THEN '+@clientcode+' ELSE ''SSD2'' END ) AS AM
ON AM.[analyte_name]= JTR.[analyte_name] AND AM.method_no = JTR.method WHEREJTR.method = ''ORG205AT'' or
(JTR.method = ''ORG101'' and AM.analyte_name = ''TubeExt'') or
(JTR.method = ''ORG205AM'') or
(JTR.method = ''iHG1FCVG'') or
(JTR.method = ''ORG199C'')
ORDER BY [PLANT SAMPLE ID],[SUBSTANCE / AGENT]'
EXEC (@sql);
update #data set #data.[ANALYTICAL METHOD] = CONMETHOD.[ANALYTICAL METHOD]
from (SELECT top 1 [S].client_id,
STUFF((SELECT distinct ', ' + [T].method_no FROM test as [T] WHERE [T].reg_no = [S].reg_no FOR XML PATH('')), 1, 1, '') as [ANALYTICAL METHOD]
FROM [dbo].[Sample] AS [S]
where [S].reg_no = @Reg_No ) as CONMETHOD
inner join #data on CONMETHOD.client_id = #data.[PLANT SAMPLE ID]
When i run this code i am getting Must declare the scalar variable for @Reg_No,@ClientCode,@ClientHasMapping when i try to run it. how can i resolve the issue
