April 19, 2012 at 8:33 pm
USE [SMART]
GO
/****** Object: StoredProcedure [dbo].[sProcess_Upload_Data_Admissions] Script Date: 04/19/2012 09:43:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sProcess_Upload_Data_Admissions]
as
insert into tblDownload_Import
(Description) values ('top of sProcess_Upload_Data_Admissions')
/*
--Append First Received To Transaction Table
INSERT INTO dbo.SMARTAdmissionsTransaction
(TransactionUser, TransactionDate, AdmissionID, TransactionTypeID)
SELECT USER, GETDATE(),
CAST(dbo.SMARTAdmissionUpload.[Admission ID] AS int), 1
FROM dbo.SmartAdmissions RIGHT OUTER JOIN
dbo.SMARTAdmissionUpload ON dbo.SmartAdmissions.[Admission ID] = cast(dbo.SMARTAdmissionUpload.[Admission ID] as int)
WHERE (dbo.SmartAdmissions.[Admission ID] IS NULL)
*/
/*
--Append Updated to Transaction Table
INSERT INTO dbo.SMARTAdmissionsTransaction
(TransactionUser, TransactionDate, AdmissionID, TransactionTypeID)
SELECT USER, GETDATE(),
dbo.SmartAdmissions.[Admission ID], 2
FROM dbo.SmartAdmissions INNER JOIN
dbo.SMARTAdmissionUpload ON dbo.SmartAdmissions.[Admission ID] = cast(dbo.SMARTAdmissionUpload.[Admission ID] as int)
*/
--Delete Admissions To be Updated
DELETE FROM dbo.SmartAdmissions
WHERE ([Admission ID] IN
( SELECT dbo.FormatInt([Admission ID])
FROM smartadmissionupload))
BEGIN TRY
--Append all Admissions
INSERT INTO dbo.SmartAdmissions
(
[SMARTUploadID],
[Agency ID],
[Clinic ID],
[National Provider Identifier],
[Client Intake ID],
[Client ID],
[Admission ID],
[Facility Identifier],
[Unique Client ID],
[Admission Creation Date],
[Agency Client ID],
[Agency Client ID bsas],
[Social Security Number],
[Client Type],
[Date of Admission],
[Transaction Type],
[Number of Prior Admissions],
[Source of Referral],
Sex, Race, Ethnicity, [Date of Birth],
[County of Residence], [Zip Code], [Marital Status],
[Highest School Grade Completed], [Employment Status],
[Family Income], [Primary Source of Income],
[Living Arrangement], [Number of Dependant Children],
[Health Coverage],
[Currently Pregnant?],
[Current Mental Health Problems?],
[Tobacco use in the past 30 days?],
[Number of days waiting to enter treatment],
[ASI Medical Score],
[ASI Employment Score], [ASI Alcohol Score],
[ASI Drug Score], [ASI Legal Score], [ASI Family Score],
[ASI Psychiatric Score],
[In a Controlled Environment past 30 Days?],
[POSIT Substance Abuse Score],
[POSIT Physical Health Score], [POSIT Mental Health Score],
[POSIT Family Score], [POSIT Peer Score],
[POSIT Education Status Score], [POSIT Vocational Status Score],
[POSIT Social Skill Score],
[POSIT Leisure Recreatal Score], [POSIT Aggression Score],
[POSIT STD HIV Risk Score], [Primary Substance],
[Primary Severity], [Primary Frequency],
[Primary Route], [Primary Age of First Use],
[Secondary Substance], [Secondary Severity],
[Secondary Frequency], [Secondary Route],
[Secondary Age of First Use], [Tertiary Substance],
[Tertiary Severity], [Tertiary Frequency],
[Tertiary Route], [Tertiary Age of First Use],
[Treatment Setting], [Attending Grades K-12],
[Attending GED Program], [Attending Vocational Training],
[Attending Higher Education],
[Number of Arrests in the Past Year],
[Number of Arrests in the Past 30 Days],
[Special Funding 1], [Special Funding 2],
[Special Funding 3],
[Special Project 1], [Special Project 2],
[Special Project 3],
[Number of Days in Support Group in Last 30 Days],
[Number of Days Attended AA/NA in Last 30 Days],
[Additional Information],
[Information_Code],
[Is Submit Without ASI Scores], [Intake Date],
[Intake Creation Date],
HATSAdmissionID,
SrcFileDate )
SELECT
dbo.FormatInt(dbo.SMARTAdmissionUpload.[ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Agency ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Clinic ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[National Provider Identifier]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Client Intake ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Client ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Admission ID]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Facility Identifier]),
dbo.SMARTAdmissionUpload.[Unique Client ID],
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Admission Creation Date]),
dbo.SMARTAdmissionUpload.[Agency Client ID],
dbo.FormatAgencyClientID(dbo.SMARTAdmissionUpload.[Agency Client ID]),
dbo.SMARTAdmissionUpload.[Social Security Number],
dbo.SMARTAdmissionUpload.[Client Type],
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Date of Admission]),
dbo.SMARTAdmissionUpload.[Transaction Type],
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Prior Admissions]),
dbo.SMARTAdmissionUpload.[Source of Referral],
dbo.FormatInt(dbo.SMARTAdmissionUpload.Sex),
dbo.SMARTAdmissionUpload.Race,
dbo.FormatInt(dbo.SMARTAdmissionUpload.Ethnicity),
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Date of Birth]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[County of Residence]),
dbo.SMARTAdmissionUpload.[Zip Code],
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Marital Status]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Highest School Grade Completed]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Employment Status]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Family Primary Source of Income]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Source of Income]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Living Arrangement]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Dependant Children]),
dbo.FormatInt(SMARTAdmissionUpload.[Health Coverage]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Currently Pregnant?]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Current Mental Health Problems?]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tobacco use in the past 30 days?]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of days waiting to enter treatment]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Medical Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Employment Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Alcohol Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Drug Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Legal Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Family Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[ASI Psychiatric Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[In a Controlled Environment past 30 Days?]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Substance Abuse Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Physical Health Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Mental Health Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Family Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Peer Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Education Status Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Vocational Status Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Social Skill Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Leisure Recreatal Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT Aggression Score]),
dbo.FormatFloat(dbo.SMARTAdmissionUpload.[POSIT STD HIV Risk Score]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Substance]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Severity]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Frequency]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Route]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Primary Age of First Use]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Substance]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Severity]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Frequency]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Route]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Secondary Age of First Use]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Substance]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Severity]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Frequency]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Route]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Tertiary Age of First Use]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Treatment Setting]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending Grades K-12]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending GED Program]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending Vocational Training]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Attending Higher Education]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Arrests in the Past Year]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Arrests in the Past 30 Days]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Funding 1]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Funding 2]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Funding 3]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Project 1]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Project 2]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Special Project 3]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Days in Support Group in Last 30 Days]),
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Number of Days Attended AA/NA in Last 30 Days]),
dbo.SMARTAdmissionUpload.[Additional Information],
case
when patindex('%migrated%',Lower(dbo.SMARTAdmissionUpload.[Additional Information]))>0
then 1
else
0
end,
dbo.FormatInt(dbo.SMARTAdmissionUpload.[Is Submit Without ASI Scores]),
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Intake Date]),
dbo.MakeDate(dbo.SMARTAdmissionUpload.[Intake Creation Date]),
dbo.SMARTAdmissionUpload.HATSAdmissionID,
dbo.SMARTAdmissionUpload.[SrcFileDate] as datetime
FROM dbo.SMARTAdmissionUpload
WHERE (dbo.SMARTAdmissionUpload.Ignore = 0) and
( dbo.FormatAgencyClientID(dbo.SMARTAdmissionUpload.[Agency Client ID]) >0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Admission Creation Date]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Admission]) > 0 ) and
( dbo.fn_Is_SMART_Date(SMARTAdmissionUpload.[Date of Birth]) > 0 )
END TRY
BEGIN CATCH
insert into tblDownload_Import
(Description) values ('error: ' + ERROR_Number() + ' msg: ' +
ERROR_Message())
END CATCH
insert into tblDownload_Import
(Description) values ('bottom of sProcess_Upload_Data_Admissions')
return
April 20, 2012 at 3:16 am
deebabat (4/19/2012)
BEGIN CATCHinsert into tblDownload_Import
(Description) values ('error: ' + ERROR_Number() + ' msg: ' + ERROR_Message())
END CATCH
In your CATCH part you try to concatenate an interger value (error_number) with a varchar. You need to convert the integer to an varchar first before concatenating them.
[font="Verdana"]Markus Bohse[/font]
April 20, 2012 at 7:44 am
MarkusB (4/20/2012)
deebabat (4/19/2012)
BEGIN CATCHinsert into tblDownload_Import
(Description) values ('error: ' + ERROR_Number() + ' msg: ' + ERROR_Message())
END CATCH
In your CATCH part you try to concatenate an interger value (error_number) with a varchar. You need to convert the integer to an varchar first before concatenating them.
Markus, Please how do I do the conversion.
April 20, 2012 at 7:51 am
deebabat (4/20/2012)
MarkusB (4/20/2012)
deebabat (4/19/2012)
BEGIN CATCHinsert into tblDownload_Import
(Description) values ('error: ' + ERROR_Number() + ' msg: ' + ERROR_Message())
END CATCH
In your CATCH part you try to concatenate an interger value (error_number) with a varchar. You need to convert the integer to an varchar first before concatenating them.
Markus, Please how do I do the conversion.
CONVERT(NVARCHAR,ERROR_NUMBER())
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply