Insert query not working 2

[vba317]

I have a query that is part of a stored procedure. That I cant seem to get to process. Also it has a function dbo.fn_GetARType(@rptid) = 1 , how do I find where the function is and how do I determine what the value of the variables are? The problem with this is the info in the field transagebucket is not getting inserted into the field arbucket. I am currently getting an error Incorrect syntax near '@rptid'

Tom

DECLARE @qid int,@rptid int,@sqlproc varchar(1100)

-- Process Tracking
INSERT INTO rptdata_process.dbo._ProcessTracking (ord,sectdesc,tstamp )
VALUES (320,'Compile ARDetail Reports',GETDATE());
-- Create Temp Table
CREATE TABLE tmpRpt_ARDetail (qrptid int,curpd int,uci varchar(10),clntid int,monasdt datetime
				,grp1 int,grp1_id varchar(50),grp2 int,grp2_id varchar(50),filt1 int
				,filt1_id varchar(50),filt2 int,filt2_id varchar(50),aid int
				,PatName varchar(75),AcctNu varchar(50),eid int,slid int,arbucket int
				,dos datetime,chgpostdt datetime,cptcode varchar(50),cptcomp varchar(1)
				,cptdisplay varchar(52),insmne varchar(50),insdesc varchar(50)
				,curbal decimal(18,2),credbal int,chgamt decimal(18, 2) );

-- Declare Bulk Insert

DECLARE bulk_insert CURSOR FOR

-- Get a list of the files to bulk insert with fields for names
SELECT qrptid,rptid
FROM tmp_RptUpdtQ
WHERE srctable = 'RptSrc_ARDetail'
ORDER BY qrptid;

-- Start Loop
OPEN bulk_insert
FETCH next FROM bulk_insert INTO @qid,@rptid

WHILE @@fetch_status = 0
BEGIN

-- SQL
INSERT INTO tmpRpt_ARDetail (qrptid,curpd,uci,clntid,monasdt,grp1,grp1_id,grp2,grp2_id,filt1
				,filt1_id,filt2,filt2_id,aid,PatName,AcctNu,eid,slid,arbucket,dos,chgpostdt
				,cptcode,cptcomp,cptdisplay,insmne,insdesc,curbal,credbal,chgamt )
SELECT uq.qrptid,uq.rptpd,uq.uci,ar.clntid,ar.reportmonth
			,uq.grp1,(case when uq.grp1 = 2 then CAST(ar.provid AS varchar(50))
												when uq.grp1 = 3 then CAST(ar.facid AS varchar(50))
												when uq.grp1 = 4 then CAST(ar.posid AS varchar(50))
												when uq.grp1 = 5 then CAST(ar.modalid AS varchar(50))
												when uq.grp1 = 6 then CAST(ar.dptid AS varchar(50))
												when uq.grp1 = 7 then isnull(ins.rcat,'SP')
												else '0' end),uq.grp2
			,(case when uq.grp2 = 2 then CAST(ar.provid AS varchar(50))
												when uq.grp2 = 3 then CAST(ar.facid AS varchar(50))
												when uq.grp2 = 4 then CAST(ar.posid AS varchar(50))
												when uq.grp2 = 5 then CAST(ar.modalid AS varchar(50))
												when uq.grp2 = 6 then CAST(ar.dptid AS varchar(50))
												when uq.grp2 = 7 then isnull(ins.rcat,'SP')
												else '0' end),uq.filt1
			,(case when uq.filt1 = 2 then CAST(ar.provid AS varchar(50))
												when uq.filt1 = 3 then CAST(ar.facid AS varchar(50))
												when uq.filt1 = 4 then CAST(ar.posid AS varchar(50))
												when uq.filt1 = 5 then CAST(ar.modalid AS varchar(50))
												when uq.filt1 = 6 then CAST(ar.dptid AS varchar(50))
												when uq.filt1 = 7 then isnull(ins.rcat,'SP')
												else '0' end),uq.filt2
			,(case when uq.filt2 = 2 then CAST(ar.provid AS varchar(50))
												when uq.filt2 = 3 then CAST(ar.facid AS varchar(50))
												when uq.filt2 = 4 then CAST(ar.posid AS varchar(50))
												when uq.filt2 = 5 then CAST(ar.modalid AS varchar(50))
												when uq.filt2 = 6 then CAST(ar.dptid AS varchar(50))
												when uq.filt2 = 7 then isnull(ins.rcat,'SP')
												else '0' end)
			,ar.aid,pat.PatName,pat.AcctNu,ar.eid,ar.slid
			,(case when dbo.fn_GetARType(@rptid) = 1 then ar.dosbucket
					when dbo.fn_GetARType(@rptid) = 2 then ar.postdtbucket
					else ar.transagebucket end)
			,ar.dos,ar.chgpostdt,ar.cptcode,ar.cptcomp,ar.cptdisplay,ar.insmne,ins.insdesc
			,ar.curbal,ar.credbal,ar.chgamt
FROM rpt_dat_ARDetail ar 
	INNER JOIN tmp_RptUpdtQ uq ON ar.clntid = uq.clntid 
	LEFT JOIN rpt_dic_Ins ins ON ar.clntid = ins.clntid AND ar.insmne = ins.insmne
	LEFT JOIN rptdata_ahs.dbo.bi_PatientData pat ON ar.clntid = pat.clntid AND ar.aid = pat.aid
WHERE (uq.qrptid = @rptid)DECLARE @qid int,@rptid int,@sqlproc varchar(1100)

-- Process Tracking
INSERT INTO rptdata_process.dbo._ProcessTracking (ord,sectdesc,tstamp )
VALUES (320,'Compile ARDetail Reports',GETDATE());
-- Create Temp Table
CREATE TABLE tmpRpt_ARDetail (qrptid int,curpd int,uci varchar(10),clntid int,monasdt datetime
				,grp1 int,grp1_id varchar(50),grp2 int,grp2_id varchar(50),filt1 int
				,filt1_id varchar(50),filt2 int,filt2_id varchar(50),aid int
				,PatName varchar(75),AcctNu varchar(50),eid int,slid int,arbucket int
				,dos datetime,chgpostdt datetime,cptcode varchar(50),cptcomp varchar(1)
				,cptdisplay varchar(52),insmne varchar(50),insdesc varchar(50)
				,curbal decimal(18,2),credbal int,chgamt decimal(18, 2) );

 

[gmmastros]

how do I find where the function is

On the left side of SQL Server Management Studio (object explorer window), expand programmability, expand functions, expand scalar valued functions. Your function should be in this list.

If you can't find it there, it may be under Table-valued Functions instead.

If you still can't find it, then you can run this....

sp_helptext 'fn_GetARType'

how do I determine what the value of the variables are?

I usually use PRINT to see the value of variables while writing code. You can simply put a line like this:

FETCH next FROM bulk_insert INTO @qid,@rptid

WHILE @@fetch_status = 0
BEGIN
   [!]Print @quid
   Print @rptid
   Print ''[/!]
End

When you run the query in SQL Server Management Studio, there will be a messages that that shows the output of your print statements. Once you have the code working, you should remove the print statements.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom