bfuchs
asked on
Urgent help needed! Unable to to paste in query designer
Hi Experts,
I have made some changes to a column in SQL using a different editor, and when trying to paste back it's unavailable!
See attached!
Untitled.png
I have made some changes to a column in SQL using a different editor, and when trying to paste back it's unavailable!
See attached!
Untitled.png
Have you tried pasting what you copied into a text file? Maybe the copy/paste action got lost somewhere...
ASKER
Hi,
No, just tried pasting it in notepad and works.
This is a long text, perhaps there is a limit how much it can paste back?
Thanks,
Ben
No, just tried pasting it in notepad and works.
This is a long text, perhaps there is a limit how much it can paste back?
Thanks,
Ben
ASKER
This is what I've trying to paste.
As you see its not that simple, and I have a couple of such fields..therefore the best way of doing it was copying field by field and modify in another editor where I can see clear the picture, and then paste back..
If that doesn't work what are my alternatives?
Thanks,
Ben
dbo.EmployeesMissingDocs('', dbo.Employeestbl.ID, dbo.Employeestbl.Title, dbo.Employeestbl.LicenseNumSignedYN, dbo.Employeestbl.LicenseExpires, dbo.Employeestbl.BclsExpires, dbo.Employeestbl.BclsSignedYN, dbo.Employeestbl.BclsLetterDate, dbo.Employeestbl.MalpracticeExpires, dbo.Employeestbl.MalLevelOK, dbo.Employeestbl.AclsExpires, dbo.Employeestbl.AclsSignedYN, dbo.Employeestbl.AclsLetterDate, dbo.Employeestbl.NalsExpires, dbo.Employeestbl.NalsSignedYN, dbo.Employeestbl.NalsLetterDate, dbo.Employeestbl.PalsExpires, dbo.Employeestbl.PalsSignedYN, dbo.Employeestbl.PalsLetterDate, dbo.Employeestbl.Other_Cert_Exp, dbo.Employeestbl.Other_Cert, dbo.Employeestbl.Physical, dbo.Employeestbl.PhisycalPPD_Date, dbo.Employeestbl.PhisycalPPD_Res, dbo.Employeestbl.ResumeYN, dbo.Employeestbl.MaskFitTestDate, dbo.Employeestbl.OrientationDocumentation, dbo.Facilitiestbl.Name, dbo.Employeestbl.PPD2ndStepDate, dbo.Employeestbl.PPD2ndStepRes, dbo.Employeestbl.TBQDate, dbo.Employeestbl.TBQResults, dbo.Employeestbl.ChestXRayDate, dbo.Employeestbl.ChestXRayRes, dbo.Employeestbl.MeaslesRubeolaDate, dbo.Employeestbl.Birthdate, dbo.Employeestbl.RubellaDate, dbo.Employeestbl.VaricellaDate, dbo.Employeestbl.HepBVac, dbo.Employeestbl.HepBWaiver, CoreMandatory_1.CorePartIDate, CoreMandatory_1.CorePartIIDate, CoreMandatory_1.CorePartIIIDate, dbo.Employeestbl.I9Complete, dbo.Employeestbl.W4, dbo.Employeestbl.LSFormYN, dbo.Employeestbl.LSOnApplyingDate, dbo.Employeestbl.Performance_Eval_Comp, dbo.Employeestbl.Reference1, dbo.Employeestbl.Reference2, dbo.Employeestbl.Application, dbo.Employeestbl.OrientationCheckList, dbo.Employeestbl.RecOfEmploy, dbo.Employeestbl.HIPAADate, dbo.Employeestbl.Tetanus, dbo.Employeestbl.WGDrugScreenDate, dbo.Employeestbl.MeaslesRubeolaRes, dbo.Employeestbl.MumpsDate, dbo.Employeestbl.MumpsRes, dbo.Employeestbl.RubellaRes, dbo.Employeestbl.VaricellaRes, dbo.Employeestbl.MeaslesRubeolaLabReports, dbo.Employeestbl.MumpsLabReports, dbo.Employeestbl.RubellaLabReports, dbo.Employeestbl.VaricellaLabReports, dbo.Employeestbl.SkillsChecklistDate, dbo.Employeestbl.DrugScreen, dbo.Employeestbl.OP_Date, dbo.Employeestbl.OP_Results, dbo.Employeestbl.Chauncey_Date, dbo.Employeestbl.Chauncey_Results, dbo.Employeestbl.ID_A, dbo.Employeestbl.ID_A_Expires, dbo.Employeestbl.BackgroundCheckDate, dbo.Employeestbl.AttestationFormDate, dbo.Employeestbl.AttestationFormSigned, dbo.Employeestbl.CPR, dbo.Employeestbl.FluShutDate, dbo.Employeestbl.FluExempt, dbo.CoreMandatory.DementiaExams, dbo.CoreMandatory.CNA_DementiaCare, dbo.Employeestbl.OrientationDocumentationFacility, dbo.Employeestbl.ORTVerificationDate, dbo.FacilitiesDocumentsRequirements.AttestationForm, dbo.FacilitiesDocumentsRequirements.MalpracticeInsurance, dbo.FacilitiesDocumentsRequirements.AhaBLS, dbo.Employeestbl.MaskFitTest, dbo.FacilitiesDocumentsRequirements.TwoStepPPD, dbo.FacilitiesDocumentsRequirements.Tetanus, dbo.FacilitiesDocumentsRequirements.MMRVaricellaTiters, dbo.FacilitiesDocumentsRequirements.FluVaccine, dbo.FacilitiesDocumentsRequirements.OrientationDocuementation, dbo.FacilitiesDocumentsRequirements.SkillsCheckList, dbo.FacilitiesDocumentsRequirements.DimentiaExams, dbo.FacilitiesDocumentsRequirements.MalpracticeReq, dbo.FacilitiesDocumentsRequirements.DrugScreenReq)
As you see its not that simple, and I have a couple of such fields..therefore the best way of doing it was copying field by field and modify in another editor where I can see clear the picture, and then paste back..
If that doesn't work what are my alternatives?
Thanks,
Ben
ASKER
OK I see there is indeed a limit (at least in my version 2005/2008).
I did it peace by peace, looks like the max of characters allowed for pasting back is around 8 full lines of a reg notepad doc.
However after finishing all my copy/paste I get the attached error.
While I know this msg cant be true, unless the copy/paste is playing tricks on me..
Any help please!!
Thanks,
Ben
Untitled.png
I did it peace by peace, looks like the max of characters allowed for pasting back is around 8 full lines of a reg notepad doc.
However after finishing all my copy/paste I get the attached error.
While I know this msg cant be true, unless the copy/paste is playing tricks on me..
Any help please!!
Thanks,
Ben
Untitled.png
I have made some changes to a column in SQL using a different editor, and when trying to paste back it's unavailable!
is that your machine out of resources? or is there some other software running at background which affected the clipboard (copy and paste)?
will a reboot help resolved the issue?
Is this a View object you trying to amend? do you have enough permission to amend it?
are you able to amend it via Alter View command in a script file instead?
Hi Ben,
Query designer is basically used to create query using UI. We are here directly copy paste data from outside. Clearly it has limitations when we can copy from outside. Honestly I have never used and in MSDN document I am not getting like how many may characters they allow.!
Hope it helps!
Query designer is basically used to create query using UI. We are here directly copy paste data from outside. Clearly it has limitations when we can copy from outside. Honestly I have never used and in MSDN document I am not getting like how many may characters they allow.!
Hope it helps!
ASKER
@Ryan,
Dont think any of those reasons mentioned are the case here, as I'm able to do it from same pc using a later version of SSMS as stated above.
However the alter view you mentioned is something I was thinking of, but not for this case, just for the following
https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
As for there its enough I get the whole SQL pasted, while here I need to work saparately column by column.
BTW, whats the syntax of alter view?
Thanks,
Ben
Dont think any of those reasons mentioned are the case here, as I'm able to do it from same pc using a later version of SSMS as stated above.
However the alter view you mentioned is something I was thinking of, but not for this case, just for the following
https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
As for there its enough I get the whole SQL pasted, while here I need to work saparately column by column.
BTW, whats the syntax of alter view?
Thanks,
Ben
Hi Ben,
I could be wrong for my previous comment....
for your ref
SQL Server: ALTER TABLE Statement
https://www.techonthenet.com/sql_server/tables/alter_table.php
check back your initial error of Function Argument Count Error, I think you provided wrong number of arguments into your function?
you can try create a simple query that using function: EmployeesMissingDocs and make sure it's correctly returning the value before paste it into your query that more complex?
Function argument count error
https://technet.microsoft.com/en-us/library/ms163402(v=sql.105).aspx
I could be wrong for my previous comment....
for your ref
SQL Server: ALTER TABLE Statement
https://www.techonthenet.com/sql_server/tables/alter_table.php
check back your initial error of Function Argument Count Error, I think you provided wrong number of arguments into your function?
you can try create a simple query that using function: EmployeesMissingDocs and make sure it's correctly returning the value before paste it into your query that more complex?
Function argument count error
https://technet.microsoft.com/en-us/library/ms163402(v=sql.105).aspx
ASKER
Hi,
Perhaps if you reply to the alter view question, do it in the post mentioned above, so if that works I can accept your comment.
Thanks,
Ben
SQL Server: ALTER TABLE StatementWhat I need there is how to modify a view's SQL property, not altering a table.
check back your initial error of Function Argument Count Error, I think you provided wrong number of arguments into your function?The same mentioned applies to this error as well, since I was able to do it in a later version shows that something is a prob with the copy/paste that's causing this issue, thus resulting in any time of errors including wrong number of args..
Perhaps if you reply to the alter view question, do it in the post mentioned above, so if that works I can accept your comment.
Thanks,
Ben
What I need there is how to modify a view's SQL property, not altering a table.sorry, try refer to this link instead.
ALTER VIEW (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms173846.aspx
Perhaps if you reply to the alter view question, do it in the post mentioned above, so if that works I can accept your commentI guess the issue is within the function: EmployeesMissingDocs, but ALTER VIEW is definitely a way to update your View.
ASKER
@Rayn,
Tested and it works, but I cannot accept here as an answer as explained, the question here is how to use the copy/paste feature per column.
However if you post you answer in following link, I will accept it.
https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
Thanks,
Ben
I guess the issue is within the function: EmployeesMissingDocs,Still unsure whats the issue with that function as it also ends up working when saved in later version.
but ALTER VIEW is definitely a way to update your View.
Tested and it works, but I cannot accept here as an answer as explained, the question here is how to use the copy/paste feature per column.
However if you post you answer in following link, I will accept it.
https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
Thanks,
Ben
Still unsure whats the issue with that function as it also ends up working when saved in later version.can you post the script of function: EmployeesMissingDocs here?
we just need to make sure if that function required 10 parameters, we are passing 10 parameters with valid values to it, etc.
ASKER
There you go..(I spent dozens of hours on it and here you're requesting it for free-:)
Thanks,
Ben
USE [PlacementNP]
GO
/****** Object: UserDefinedFunction [dbo].[EmployeesMissingDocs] Script Date: 12/26/2016 23:11:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[EmployeesMissingDocs] (
@ResultsType varchar(50) = null,
@EmployeeID int,
@Title varchar(50) = null,
@LicenseNumSignedYN bit = null,
@LicenseExpires DATETIME= null,
@BclsExpires DATETIME= null ,
@BclsSignedYN bit = null,
@BclsLetterDate datetime = null,
@MalpracticeExpires DATETIME= null,
@MalLevelOK bit = null,
@AclsExpires datetime = null,
@AclsSignedYN bit = null,
@AclsLetterDate datetime = null,
@NalsExpires datetime = null,
@NalsSignedYN bit = null,
@NalsLetterDate datetime = null,
@PalsExpires datetime = null,
@PalsSignedYN bit = null,
@PalsLetterDate datetime = null,
@Other_Cert_Exp datetime = null,
@Other_Cert varchar(50) = null,
@Physical datetime = null,
@PhisycalPPD_Date datetime = null,
@PhisycalPPD_Res varchar(50) = null,
@ResumeYN bit = null,
@MaskFitTestDate datetime = null,
@OrientationDocumentation bit = null,
@FacName varchar(75) = null,
@PPD2ndStepDate datetime = null,
@PPD2ndStepRes varchar(50) = null,
@TBQDate datetime = null,
@TBQResults varchar(5) = null,
@ChestXRayDate datetime = null,
@ChestXRayRes varchar(50) = null,
@MeaslesRubeolaDate datetime = null,
@Birthdate datetime = null,
@RubellaDate datetime = null,
@VaricellaDate datetime = null,
@HepBVac datetime = null,
@HepBWaiver datetime = null,
@CorePartIDate datetime = null,
@CorePartIIDate datetime = null,
@CorePartIIIDate datetime = null,
@I9Complete bit = null,
@W4 bit = null,
@LSFormYN bit = null,
@LSOnApplyingDate datetime = null,
@Performance_Eval_Comp datetime = null,
@Reference1 bit = null,
@Reference2 bit = null,
@Application bit = null,
@OrientationCheckList bit = null,
@RecOfEmploy bit = null,
@HIPAADate datetime = null,
@Tetanus varchar(250) = null,
@WGDrugScreenDate datetime = null,
@MeaslesRubeolaRes varchar(50) = null,
@MumpsDate datetime = null,
@MumpsRes varchar(50)=null,
@RubellaRes varchar(50)=null,
@VaricellaRes varchar(50) = null,
@MeaslesRubeolaLabReports as bit,
@MumpsLabReports as bit,
@RubellaLabReports as bit,
@VaricellaLabReports as bit,
@SkillsChecklistDate datetime = null,
@DrugScreenDate datetime = null,
@OP_Date as datetime,
@OP_Results varchar(25) = null,
@Chauncey_Date as datetime,
@Chauncey_Results as varchar (25) = null,
@ID_A varchar (50) = null,
@ID_A_Expires dateTime,
@BackgroundCheckDate as datetime = null,
@AttestationFormDate datetime = null,
@AttestationFormSigned as bit = null,
@CPR as bit = null,
@FluShutDate as datetime,
@FluExempt as datetime,
@DementiaExams as datetime,
@CNA_DementiaCare as datetime,
@OrientationDocumentationFacility varchar(150) = null,
@ORTVerificationDate as datetime,
@Fac_AttestationForm as bit = null,
@Fac_MalpracticeInsurance as bit = null,
@Fac_AhaBLS as bit = null,
@Fac_MaskFitTest as bit = null,
@Fac_TwoStepPPD as bit = null,
@Fac_Tetanus as bit = null,
@Fac_MMRVaricellaTiters as bit = null,
--@Fac_WGDrugScreen as bit = null,
--@Fac_DrugScreenAnually as bit = null,
--@Fac_DrugScreenBiAnually as bit = null,
@Fac_FluVaccine as bit = null,
@Fac_OrientationDocuementation as bit = null,
@Fac_SkillsCheckList as bit = null,
@Fac_DimentiaExams as bit = null,
@Fac_MalpracticeReq varchar(50) = null,
@Fac_DrugScreenReq Varchar(50) = null
) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @MissingDocs varchar(2000)
declare @ITestCount as int
declare @IsHHCFac as bit
declare @Count as int
DECLARE @Missing varchar(1000)
DECLARE @Expired varchar(1000)
DECLARE @WillExpire varchar(1000)
DECLARE @NotSigned varchar(1000)
DECLARE @Other varchar(1000)
DECLARE @Due varchar(1000)
Declare @DrugScreenLatest datetime
Declare @IsBclsDate as bit
Declare @IsAclsDate as bit
Declare @IsNalsDate as bit
Declare @IsPalsDate as bit
Declare @IsAttestDate as bit
Declare @IsMalDate as bit
declare @IsMeaslesDate as bit
Declare @IsMumpsDate as bit
Declare @IsRubellaDate bit
Declare @IsVaricellaDate as bit
--Declare @LastHireDate as datetime
Declare @Ck_OPDate as bit
Declare @Ck_ChaunceyDate as bit
Declare @Ck_I9 as bit
declare @FluMaxDate as datetime
declare @Priority1Miss as bit
declare @Priority2Miss as bit
declare @MissingPriority varchar(2000)
declare @DocMissWithStatus varchar(3000)
declare @EmpReqDocs varchar(2000)
declare @CkMalReq as bit
Declare @Mth int
SELECT @Missing =''
SELECT @Expired =''
SELECT @WillExpire =''
SELECT @NotSigned =''
SELECT @Other =''
SELECT @Due =''
select @EmpReqDocs = ''
select @IsBclsDate = 0
select @IsAclsDate = 0
select @IsNalsDate = 0
select @IsPalsDate = 0
Set @Mth = Datepart(Month, getdate())
if @LicenseExpires is not null and @Title in('rn','lpn') select @Ck_OPDate = 1
if @LicenseExpires is not null and @Title = 'cna' select @Ck_ChaunceyDate = 1
if @ID_A in ('EA PHOTO','FPP I-551','FPP I-94') select @CK_I9 = 1
if @Fac_MalpracticeReq = 'Not required'
or (@Fac_MalpracticeReq = 'Only required for RN/LPN/NP' and @Title not in ('RN','LPN','NP'))
or (@Fac_MalpracticeReq = 'Only required for CNA/RN/NP/LPN' and @Title not in ('RN','LPN','NP','CNA'))
or (@Fac_MalpracticeReq = 'Not required for PCA/PCT/Sitters' and @Title in ('PCA','PCT','Sitter'))
or @Title = 'Clerk'
select @CkMalReq = 0
else
select @CkMalReq = 1
--if @WGDrugScreenDate is not null select @DrugScreenLatest = @WGDrugScreenDate
--if @DrugScreenDate > @WGDrugScreenDate select @DrugScreenLatest = @DrugScreenDate
--select @DrugScreenLatest = CASE WHEN @WGDrugScreenDate > @DrugScreenDate THEN @WGDrugScreenDate ELSE isnull(@DrugScreenDate,@WGDrugScreenDate) END
select @DrugScreenLatest = case when @Fac_DrugScreenReq = 'WG Drug Screen' then @WGDrugScreenDate else CASE WHEN @WGDrugScreenDate > @DrugScreenDate THEN @WGDrugScreenDate ELSE isnull(@DrugScreenDate,@WGDrugScreenDate) END end
if @FacName in('COLER GOLDWATER SPECIALTY HOSP','DR. SUSAN SMITH MCKINNEY','EAST NY DIAGNOSTIC & TREATMENT','ELMHURST HOSPITAL','GOUVERNEUR NURSING FACILITY','HARLEM HOSPITAL','KINGS COUNTY HOSPITAL CENTER','LINCOLN MEDICAL CENTER','METROPOLITAN HOSPITAL CENTER','QUEENS HOSPITAL CENTER','RENAISSANCE HEALTH CARE NETWOR','SEGUNDO RUIZ BELVIS D&T CENTER','WOODHULL MEDICAL CENTER') select @IsHHCFac = 1
select @FluMaxDate = CASE WHEN @FluShutDate > @FluExempt THEN @FluShutDate ELSE isnull(@FluExempt,@FluShutDate) END
if @LicenseExpires is null and @Title in ('NP', 'RN', 'LPN', 'CNA') select @Missing = @Missing + ', License'
if @CkMalReq = 1 and @MalpracticeExpires is null
begin
select @Missing = @Missing + ', Malpractice'
select @IsMalDate = 1
end
if @BclsExpires is null
begin
select @Missing = @Missing + ', Bcls'
select @IsBclsDate = 1
end
if @Physical is null select @Missing = @Missing + ', Physical'
if @PhisycalPPD_Res is null select @Missing = @Missing + ', PPD_Res'
if @PhisycalPPD_Res = 'N' and @PhisycalPPD_Date is null select @Missing = @Missing + ', PPD Annual'
if @PhisycalPPD_Res = 'P'
begin
if @TBQDate is null select @Missing = @Missing + ', TB Questionnaire'
if @ChestXRayDate is null select @Missing = @Missing + ', Chest X-Ray'
end
if @MaskFitTestDate is null and @Fac_MaskFitTest = 1 select @Missing = @Missing + ', Mask Fit Test'
if @MeaslesRubeolaDate is null and isnull(@BirthDate,getdate()) > '12/31/56'
begin
select @Missing = @Missing + ', Measles'
select @IsMeaslesDate = 1
end
if @MeaslesRubeolaDate is not null and @MeaslesRubeolaRes is null
begin
select @Missing = @Missing + ', Measles Immune result'
select @IsMeaslesDate = 1
end
if @RubellaDate is null
begin
select @Missing = @Missing + ', Rubella'
select @IsRubellaDate = 1
end
if @RubellaDate is not null and @RubellaRes is null
begin
select @Missing = @Missing + ', Rubella immune result'
select @IsRubellaDate = 1
end
if @VaricellaDate is null
begin
select @Missing = @Missing + ', Varicella'
select @IsVaricellaDate = 1
end
if @VaricellaDate is not null and @VaricellaRes is null
begin
select @Missing = @Missing + ', Varicella Immune result'
select @IsVaricellaDate = 1
end
if @MeaslesRubeolaRes = 'N' or @MumpsRes = 'N' or @RubellaRes = 'N' or @VaricellaRes = 'N' select @Missing = @Missing + ', Titers not immune'
if @Fac_MMRVaricellaTiters=1
begin
if isnull(@MeaslesRubeolaLabReports,0) = 0 and isnull(@IsMeaslesDate,0)=0 select @missing = @missing + ', Measles_lab'
if isnull(@MumpsLabReports,0) = 0 and isnull(@IsMumpsDate,0)=0 select @missing = @missing + ', Mumps_lab'
if isnull(@RubellaLabReports,0) = 0 and isnull(@IsRubellaDate,0)=0 select @missing = @missing + ', Rubella_lab'
if isnull(@VaricellaLabReports,0) = 0 and isnull(@IsVaricellaDate,0)=0 select @missing = @missing + ', Varicella_lab'
end
if @HepBVac is null and @HepBWaiver is null select @Missing = @Missing + ', Hepatitis B'
if @CorePartIDate is null select @Missing = @Missing + ', Annual Mandatory Part 1'
--if isnull(@CorePartIIDate,getdate()-1) <getdate() and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 2'
--if isnull(@CorePartIIIDate,getdate()-1) <getdate() and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 3'
if @CorePartIIDate is null and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 2'
if @CorePartIIIDate is null and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Missing = @Missing + ', Annual Mandatory Part 3'
if isnull(@I9Complete,0)=0 select @Missing = @Missing + ', I-9 Form '
if isnull(@W4,0)=0 select @Missing = @Missing + ', Form W4'
if isnull(@Reference1,0)=0 or isnull(@Reference2,0)=0 select @Missing = @Missing + ', Reference'
if isnull(@Application,0)=0 select @Missing = @Missing + ', Application form'
if isnull(@OrientationCheckList,0)=0 select @Missing = @Missing + ', Orientation Checklist'
if isnull(@ResumeYN,0)=0 and isnull(@RecOfEmploy,0)=0 select @Missing = @Missing + ', Resume / Record of Employment form'
if @Fac_OrientationDocuementation = 1
begin
if isnull(@OrientationDocumentation,0)=0 or @FacName <> isnull(@OrientationDocumentationFacility,'') select @Missing = @Missing + ', Orientation Documentation'
end
if @HIPAADate is null select @Missing = @Missing + ', HIPAA Form'
--if @IsHHCFac = 1 and @Tetanus is null select @Missing = @Missing + ', Tetanus immune'
if isnull(@Fac_DrugScreenReq,'')<>'Not Required'
begin
if @Fac_DrugScreenReq = 'WG Drug Screen'
begin
if @WGDrugScreenDate is null select @Missing = @Missing + ', Drug Screen WG'
end
else
begin
if @DrugScreenLatest is null select @Missing = @Missing + ', Drug Screen'
end
End
if @MumpsDate is not null and @MumpsRes is null
begin
select @Missing = @Missing + ', Mumps immune result'
select @IsMumpsDate = 1
end
If isnull(@SkillsChecklistDate,0) = 0 SELECT @Missing = @Missing + ', AnnualSkillsChecklist '
if @Ck_OPDate = 1
begin
if @OP_Date is null
select @Missing = @Missing + ', OP'
else
if @OP_Results is null select @missing = @missing + ', OP Results'
end
if @Ck_ChaunceyDate = 1
begin
if @Chauncey_Date is null
select @Missing = @Missing + ', Chauncey'
else
if @Chauncey_Results is null select @missing = @missing + ', Chauncey Results'
if @Chauncey_Results <> 'Registered' select @missing = @missing + ', License'
end
if @BackgroundCheckDate is null select @Missing = @Missing + ', crim chk'
if @OP_Results <> 'Registered' select @missing = @missing + ', License'
if @Fac_AttestationForm = 1 and @AttestationFormDate is null
begin
select @missing = @missing + ', Attestation Form'
select @IsAttestDate = 1
end
--if @Fac_MaskFitTest = 1 and @MaskFitTestDate is null select @missing = @missing + ', Mask Fit test'
if @Fac_TwoStepPPD = 1 and @PhisycalPPD_Res = 'N' and @PPD2ndStepDate is null select @missing = @missing + ', PPD2'
if @Fac_Tetanus = 1 and @Tetanus is null select @missing = @missing + ', Tetanus'
if @Fac_FluVaccine = 1 and @FluMaxDate is null and @Mth in (10, 11, 12, 1, 2, 3) select @missing = @missing + ', Flu Shot'
if @Fac_DimentiaExams = 1
begin
if @Title in('rn','lpn','cna') and @DementiaExams is null select @missing = @missing + ', Dementia'
--if @Title = 'cna' and @CNA_DementiaCare is null select @missing = @missing + ', Dementia'
end
if @Title = 'ORT' and @ORTVerificationDate is null select @missing = @missing + ', ORT Ver'
--select @other = 'test' --+ cast (@Fac_AttestationForm as varchar)
----------------------------------------------Expired
if @LicenseExpires < getdate() and @Title <> 'ORT'
begin
if @LicenseExpires < getdate() select @Expired = @Expired + ', License'
select @EmpReqDocs = @EmpReqDocs + ' License'
end
if @CkMalReq = 1
begin
if @MalpracticeExpires < getdate()
begin
select @Expired = @Expired + ', Malpractice'
select @IsMalDate = 1
end
select @EmpReqDocs = @EmpReqDocs + ' Malpractice'
end
if @Physical < getdate()-365 select @Expired = @Expired + ', Physical'
select @EmpReqDocs = @EmpReqDocs + ' Physical'
if @PhisycalPPD_Res = 'N'
begin
if @PhisycalPPD_Date < getdate()-365 select @Expired = @Expired + ', PPD Annual'
select @EmpReqDocs = @EmpReqDocs + ' PPD Annual'
end
if @PhisycalPPD_Res = 'P'
begin
if @TBQDate < getdate()-365 select @Expired = @Expired + ', TB Questionnaire'
select @EmpReqDocs = @EmpReqDocs + ' TB Questionnaire'
end
if @Fac_MaskFitTest = 1
begin
if @MaskFitTestDate < getdate()-365 select @Expired = @Expired + ', Mask Fit Test'
select @EmpReqDocs = @EmpReqDocs + ' Mask Fit Test'
end
if @Fac_SkillsCheckList = 1
if @SkillsChecklistDate < getdate()-365 select @Expired = @Expired + ', AnnualSkillsChecklist'
else
if @SkillsChecklistDate < getdate()-730 select @Expired = @Expired + ', AnnualSkillsChecklist'
select @EmpReqDocs = @EmpReqDocs + ' AnnualSkillsChecklist'
if isnull(@Fac_DrugScreenReq,'')<>'Not Required'
begin
if @Fac_DrugScreenReq = 'WG Drug Screen'
begin
if @WGDrugScreenDate < getdate()-730 select @Expired = @Expired + ', Drug Screen WG'
select @EmpReqDocs = @EmpReqDocs + ' Drug Screen WG'
end
else
if @Fac_DrugScreenReq = 'Anually'
begin
if @DrugScreenLatest < getdate()-365 select @Expired = @Expired + ', Drug Screen'
select @EmpReqDocs = @EmpReqDocs + ' Drug Screen'
end
else
if @Fac_DrugScreenReq = 'Bi-Anually'
begin
if @DrugScreenLatest < getdate()-730 select @Expired = @Expired + ', Drug Screen'
select @EmpReqDocs = @EmpReqDocs + ' Drug Screen'
end
end
if @Ck_OPDate = 1
begin
if @OP_Date < getdate()-365 select @Expired = @Expired + ', OP'
select @EmpReqDocs = @EmpReqDocs + ' OP'
end
if @Ck_ChaunceyDate = 1
begin
if @Chauncey_Date < getdate()-365 select @Expired = @Expired + ', Chauncey'
select @EmpReqDocs = @EmpReqDocs + ' Chauncey'
end
if @CK_I9 = 1
begin
if @ID_A_Expires < getdate() select @Expired = @Expired + ', I-9/ID_A'
select @EmpReqDocs = @EmpReqDocs + ' I-9/ID_A'
end
if @BclsExpires < getdate()
begin
select @Expired = @Expired + ', Bcls'
select @IsBclsDate = 1
end
if @AclsExpires < getdate()
begin
select @Expired = @Expired + ', Acls'
select @IsAclsDate = 1
end
if @NalsExpires < getdate()
begin
select @Expired = @Expired + ', Nals'
select @IsNalsDate = 1
end
if @PalsExpires < getdate()
begin
select @Expired = @Expired + ', Pals'
select @IsPalsDate = 1
end
select @EmpReqDocs = @EmpReqDocs + ' Bcls Acls Nals Pals'
if @Fac_AttestationForm = 1
begin
if @AttestationFormDate < getdate()-365
begin
select @Expired = @Expired + ', Attestation Form'
select @IsAttestDate = 1
end
select @EmpReqDocs = @EmpReqDocs + ' Attestation Form'
end
if @Fac_FluVaccine = 1
begin
if @FluMaxDate < getdate()-365 select @expired = @expired + ', Flu Shot'
select @EmpReqDocs = @EmpReqDocs + ' Flu Shot'
end
if @Fac_DimentiaExams = 1 and @Title in('rn','lpn','cna')
begin
if @DementiaExams < getdate() select @expired = @expired + ', Dementia'
select @EmpReqDocs = @EmpReqDocs + ' Dementia'
end
if @CorePartIDate < getdate() select @Expired = @Expired + ', Annual Mandatory'
if @ORTVerificationDate < getdate()-365 select @Expired = @Expired + ', ORT Ver'
select @EmpReqDocs = @EmpReqDocs + ' Annual Mandatory ORT Ver'
----------------------------------------Will Expire
if @LicenseExpires between getdate() and getdate()+ 60 and @Title <> 'ORT' select @WillExpire = @WillExpire + ', License'
if @CkMalReq = 1 and @MalpracticeExpires between getdate() and getdate()+ 60
begin
select @WillExpire = @WillExpire + ', Malpractice'
select @IsMalDate = 1
end
--if @AclsExpires between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', Acls'
--if @NalsExpires between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', NRP'
--if @PalsExpires between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', Pals'
if @Physical between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Physical'
if @PhisycalPPD_Res = 'N' and @PhisycalPPD_Date between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', PPD Annual'
if @PhisycalPPD_Res = 'P' and @TBQDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', TB Questionnaire'
if @Fac_MaskFitTest = 1 and @MaskFitTestDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Mask Fit Test'
if @Fac_SkillsCheckList = 1
if @SkillsChecklistDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', AnnualSkillsChecklist'
else
if @SkillsChecklistDate between getdate()-730 and getdate()- 670 select @WillExpire = @WillExpire + ', AnnualSkillsChecklist'
if isnull(@Fac_DrugScreenReq,'')<>'Not Required'
begin
if @Fac_DrugScreenReq = 'WG Drug Screen'
begin
if @WGDrugScreenDate between getdate()-730 and getdate()- 670 select @WillExpire = @WillExpire + ', Drug Screen WG'
end
else
if @Fac_DrugScreenReq = 'Anually'
begin
if @DrugScreenLatest between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Drug Screen'
end
else
if @Fac_DrugScreenReq = 'Bi-Anually'
begin
if @DrugScreenLatest between getdate()-730 and getdate()- 670 select @WillExpire = @WillExpire + ', Drug Screen'
end
end
if @Ck_OPDate = 1 and @OP_Date between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', OP'
if @Ck_ChaunceyDate = 1 and @Chauncey_Date between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Chauncey'
if @CK_I9 = 1 and @ID_A_Expires between getdate() and getdate()+90 select @WillExpire = @WillExpire + ', I-9/ID_A'
if @BclsExpires between getdate() and getdate()+ 60
begin
select @WillExpire = @WillExpire + ', Bcls'
select @IsBclsDate = 1
end
if @AclsExpires between getdate() and getdate()+ 60
begin
select @WillExpire = @WillExpire + ', Acls'
select @IsAclsDate = 1
end
if @NalsExpires between getdate() and getdate()+ 60
begin
select @WillExpire = @WillExpire + ', Nals'
select @IsNalsDate = 1
end
if @PalsExpires between getdate() and getdate()+ 60
begin
select @WillExpire = @WillExpire + ', Pals'
select @IsPalsDate = 1
end
if @Fac_FluVaccine = 1 and @FluMaxDate between getdate()-365 and getdate()- 305 select @WillExpire = @WillExpire + ', Flu Shot'
if @Fac_DimentiaExams = 1
begin
if @Title in('rn','lpn','cna') and @DementiaExams between getdate() and getdate()+30 select @WillExpire = @WillExpire + ', Dementia'
--if @Title = 'cna' and @CNA_DementiaCare between getdate() and getdate()+30 select @WillExpire = @WillExpire + ', Dementia'
end
if @CorePartIDate between getdate() and getdate()+ 60 select @WillExpire = @WillExpire + ', Annual Mandatory Part 1'
--if @CorePartIIDate between getdate() and getdate()+ 60 and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Due = @Due + ', Annual Mandatory Part 2'
--if @CorePartIIIDate between getdate() and getdate()+ 60 and isnull(@title,' ') not in('CR','Chart Review','Clerk') select @Due = @Due + ', Annual Mandatory Part 3'
if @Performance_Eval_Comp < getdate()-670 select @WillExpire = @WillExpire + ', Performance Evaluation'
-------------------------------------Not Signed
if @LicenseExpires >= getdate() +60 and isnull(@LicenseNumSignedYN,0) = 0 and @Title <> 'ORT' select @NotSigned = @NotSigned + ', License'
if @IsBclsDate <> 1
Begin
if @BclsLetterDate is not null
select @Missing = @Missing + ', Bcls Card'
else
if isnull(@BclsSignedYN,0) = 0 select @NotSigned = @NotSigned + ', Bcls'
End
if @IsAclsDate <> 1
Begin
if @AclsLetterDate is not null
select @Missing = @Missing + ', Acls Card'
else
if isnull(@AclsSignedYN,0) = 0 and @AclsExpires is not null select @NotSigned = @NotSigned + ', Acls'
End
if @IsNalsDate <> 1
Begin
if @NalsLetterDate is not null
select @Missing = @Missing + ', Nals Card'
--else
--if isnull(@NalsSignedYN,0) = 0 and @NalsExpires is not null select @NotSigned = @NotSigned + ', Nals'
End
if @IsPalsDate <> 1
Begin
if @PalsLetterDate is not null
select @Missing = @Missing + ', Pals Card'
else
if isnull(@PalsSignedYN,0) = 0 and @PalsExpires is not null select @NotSigned = @NotSigned + ', Pals'
End
if @Fac_AttestationForm = 1 and isnull(@AttestationFormSigned,0) = 0 and isnull(@IsAttestDate,0)= 0 select @NotSigned = @NotSigned + ', Attestation Form'
-------------------------------------Due
--select @LastHireDate=(select Max(HireDate) from dbo.EmployeesFacilities where Employeeid = @EmployeeID)
--if @LastHireDate is null or @LastHireDate < getdate()-180 select @Due = @Due + ', Performance Evaluation'
SELECT @MissingDocs =''
if @CkMalReq = 1 and @Fac_MalpracticeReq = 'Required 3M-6M' and @MalpracticeExpires >= getdate() +60 and isnull(@MalLevelOK,0) = 0 and isnull(@IsMalDate,0) = 0
begin
select @Other = @Other + ', Malpractice level of insurance insufficient'
select @IsMalDate = 1
end
select @count=(select count(*) from dbo.EmployeesOtherCertificates where Employeeid = @EmployeeID and CertificateExpires < getdate()+60)
if @Count >0
begin
declare @CertificateName varchar(50)
declare @CertificateExpires as datetime
declare FirstCursor cursor for select CertificateName, CertificateExpires from dbo.employeesOtherCertificates where Employeeid = @EmployeeID and CertificateExpires < getdate()+60
open FirstCursor
while @count>0
begin
fetch FirstCursor into @CertificateName,@CertificateExpires
if @CertificateExpires < getdate() select @Expired = @Expired + ', ' + @CertificateName
if @CertificateExpires >= getdate() select @WillExpire = @WillExpire + ', ' + @CertificateName
set @count=@count-1
end
close FirstCursor
deallocate FirstCursor
end
if isnull(@LSFormYN,0) = 0
begin
select @count=(select count(*) from dbo.EmployeesLSForms where Employeeid = @EmployeeID)
if @Count = 0 select @Missing = @Missing + ', LS Form'
end
--'Other
if @PhisycalPPD_Res = 'P' and @ChestXRayRes = 'P' select @Other = @Other + ', Chest X-Ray positive'
if @CkMalReq = 1 and @Fac_MalpracticeReq = 'Required 3M-6M' and @MalpracticeExpires is not null and isnull(@MalLevelOK,0) = 0 and isnull(@IsMalDate,0) = 0 select @Other = @Other + ', Malpractice insufficient funds'
if @Fac_AhaBLS = 1 and @CPR = 1 and isnull(@IsBclsDate,0) = 0 select @other = @other + ', BLS not AHA cert'
if isnull(@Missing,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Missing: ' + SUBSTRING(@Missing,3, LEN(@Missing))
if isnull(@Expired,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Expired: ' + SUBSTRING(@Expired,3, LEN(@Expired))
if isnull(@WillExpire,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Will Expire: ' + SUBSTRING(@WillExpire,3, LEN(@WillExpire))
if isnull(@NotSigned,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Not Signed: ' + SUBSTRING(@NotSigned,3, LEN(@NotSigned))
if isnull(@Due,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Due: ' + SUBSTRING(@Due,3, LEN(@Due))
if isnull(@Other,'') <> '' select @MissingDocs=@MissingDocs + ';' + char(13) + char(10) + ' Other: ' + SUBSTRING(@Other,3, LEN(@Other))
IF LEN(@MissingDocs) > 0
begin
if isnull(@ResultsType,'')=''
select @MissingDocs= SUBSTRING(@MissingDocs,5, LEN(@MissingDocs))
else if @ResultsType = 'EmpReqDocs'
select @MissingDocs = @EmpReqDocs
else if @resultsType = 'MissingDocsWithStatus'
begin
select @DocMissWithStatus=''
if isnull(@Missing,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@Missing,',',' ;Missing')
if isnull(@Expired,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@Expired,',',' ;Expired')
if isnull(@WillExpire,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@WillExpire,',',' ;Will Expire')
if isnull(@NotSigned,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@NotSigned,',',' ;Not Signed')
if isnull(@Due,'') <> '' select @DocMissWithStatus=@DocMissWithStatus + replace (@Due,',',' ;Due')
select @DocMissWithStatus = @DocMissWithStatus + ' ;'
select @MissingDocs= 'abc'
select @MissingDocs = @DocMissWithStatus
end
if @ResultsType = 'PriorityType'
begin
select @MissingPriority=@Missing + ' ' + @Expired
if @Title in ('NP', 'RN', 'LPN', 'CNA')
begin
if charindex('license',@MissingPriority)> 0 or charindex('I-9',@MissingPriority)> 0
or charindex('BCLS',@MissingPriority)> 0 or charindex('Physical',@MissingPriority)> 0
or charindex('PPD',@MissingPriority)> 0 or charindex('Drug',@MissingPriority)> 0
or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0
or (charindex('Acls',@MissingPriority)> 0 and @AclsExpires is not null)
or (charindex('Nals',@MissingPriority)> 0 and @NalsExpires is not null)
or (charindex('Pals',@MissingPriority)> 0 and @PalsExpires is not null)
or charindex('Malpractice',@MissingPriority)> 0
Select @Priority1Miss = 1
if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
or charindex('Reference',@MissingPriority)> 0 or charindex('W4',@MissingPriority)> 0
or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingPriority)> 0
or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0
or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
or charindex('TB Questionnaire',@MissingPriority)> 0
Select @Priority2Miss = 1
end
if @Title in ('PCA', 'PCT', 'ORT') or @Title like '%tech%'
begin
if charindex('I-9',@MissingPriority)> 0
or charindex('BCLS',@MissingPriority)> 0 or charindex('Physical',@MissingPriority)> 0
or charindex('PPD',@MissingPriority)> 0 --or charindex('Drug',@MissingPriority)> 0
or @MissingPriority like '%Drug%'
or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0
or (charindex('Malpractice',@MissingPriority)> 0 and @MalpracticeExpires is not null)
Select @Priority1Miss = 1
if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
or charindex('Reference',@MissingPriority)> 0 or charindex('W4',@MissingPriority)> 0
or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingPriority)> 0
or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0
or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
or charindex('TB Questionnaire',@MissingPriority)> 0
Select @Priority2Miss = 1
end
if @Title in ('Sitter')
begin
if charindex('I-9',@MissingPriority)> 0 or charindex('Physical',@MissingPriority)> 0
or charindex('PPD',@MissingPriority)> 0 or charindex('Drug',@MissingPriority)> 0
or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0
Select @Priority1Miss = 1
if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
or charindex('Reference',@MissingPriority)> 0 or charindex('W4',@MissingPriority)> 0
or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingPriority)> 0
or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0
or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
or charindex('TB Questionnaire',@MissingPriority)> 0
Select @Priority2Miss = 1
end
if @Title in ('Clerk')
begin
if charindex('I-9',@MissingPriority)> 0 or charindex('Physical',@MissingPriority)> 0
or charindex('PPD',@MissingPriority)> 0 or charindex('Drug',@MissingPriority)> 0
or charindex('BackgroundCheck',@MissingPriority)> 0 or charindex('Titers',@MissingPriority)> 0
Select @Priority1Miss = 1
if charindex('Application',@MissingPriority)> 0 or charindex('Resume',@MissingPriority)> 0
or charindex('Reference',@MissingPriority)> 0 or charindex('W4',@MissingPriority)> 0
or charindex('HIPAA',@MissingPriority)> 0 or charindex('SkillsChecklist',@MissingDocs)> 0
or charindex('Annual Mandatory',@MissingPriority)> 0 or charindex('Hepatitis B',@MissingPriority)> 0
or (charindex('Mask Fit Test',@MissingPriority)> 0 and @MaskFitTestDate is not null)
or charindex('TB Questionnaire',@MissingPriority)> 0
Select @Priority2Miss = 1
end
if @Priority1Miss = 1 and @Priority2Miss = 1
set @MissingDocs= 'Both'
else if @Priority1Miss = 1
set @MissingDocs= 'One'
else if @Priority2Miss = 1
set @MissingDocs= 'Two'
else
set @MissingDocs= 'None'
end
End
RETURN @MissingDocs
END
Thanks,
Ben
Ok, seems that you got a long list for the parameters!
if not wrong total parameters for the function: 96 ?
few comments from quick observations:
in your case:
see the parameters below as they do not have a default value.
@EmployeeID int,
@MeaslesRubeolaLabReports as bit,
@MumpsLabReports as bit,
@RubellaLabReports as bit,
@VaricellaLabReports as bit,
@OP_Date as datetime,
@Chauncey_Date as datetime,
@ID_A_Expires dateTime,
@FluShutDate as datetime,
@FluExempt as datetime,
@DementiaExams as datetime,
@CNA_DementiaCare as datetime,
@ORTVerificationDate as datetime,
it's advised either to set all parameters with a default value,
OR put those parameters without a default values before those parameters with a default value, like:
if you got something like this:
And there could be the reason why you getting the Function Argument Count error, because the function's parameters are not arranged correctly.
if not wrong total parameters for the function: 96 ?
few comments from quick observations:
in your case:
see the parameters below as they do not have a default value.
@EmployeeID int,
@MeaslesRubeolaLabReports as bit,
@MumpsLabReports as bit,
@RubellaLabReports as bit,
@VaricellaLabReports as bit,
@OP_Date as datetime,
@Chauncey_Date as datetime,
@ID_A_Expires dateTime,
@FluShutDate as datetime,
@FluExempt as datetime,
@DementiaExams as datetime,
@CNA_DementiaCare as datetime,
@ORTVerificationDate as datetime,
it's advised either to set all parameters with a default value,
OR put those parameters without a default values before those parameters with a default value, like:
ALTER FUNCTION [dbo].[yourFunction] (
@Para1 int,
@Para2 int,
@Para3 varchar(50) = null,
@Para4 varchar(50) = null
) RETURNS VARCHAR(2000)
AS
...
so that when you calling the function, you only need to call the function by providing 2 parameters (Para1 and Para2)if you got something like this:
ALTER FUNCTION [dbo].[yourFunction] (
@Para1 int,
@Para3 varchar(50) = null,
@Para4 varchar(50) = null,
@Para2 int
) RETURNS VARCHAR(2000)
AS
...
I believe you need to pass 4 parameters into the function.And there could be the reason why you getting the Function Argument Count error, because the function's parameters are not arranged correctly.
ASKER
Good point Ryan, and I may try to apply default to all params.
FYI- the following is what I was trying to paste in OP.
Thanks,
Ben
And there could be the reason why you getting the Function Argument Count error, because the function's parameters are not arranged correctlyWell that should only be the case if I'm omitting some param values, while here believe I have them all (see below), and again this ended up working so there's no reason to think something was wrong with the list of params.
FYI- the following is what I was trying to paste in OP.
dbo.EmployeesMissingDocs('', dbo.Employeestbl.ID,
dbo.Employeestbl.Title,
dbo.Employeestbl.LicenseNumSignedYN,
dbo.Employeestbl.LicenseExpires, dbo.Employeestbl.BclsExpires, dbo.Employeestbl.BclsSignedYN,
dbo.Employeestbl.BclsLetterDate, dbo.Employeestbl.MalpracticeExpires, dbo.Employeestbl.MalLevelOK,
dbo.Employeestbl.AclsExpires, dbo.Employeestbl.AclsSignedYN, dbo.Employeestbl.AclsLetterDate, dbo.Employeestbl.NalsExpires,
dbo.Employeestbl.NalsSignedYN, dbo.Employeestbl.NalsLetterDate, dbo.Employeestbl.PalsExpires, dbo.Employeestbl.PalsSignedYN,
dbo.Employeestbl.PalsLetterDate, dbo.Employeestbl.Other_Cert_Exp, dbo.Employeestbl.Other_Cert, dbo.Employeestbl.Physical,
dbo.Employeestbl.PhisycalPPD_Date,dbo.Employeestbl.PhisycalPPD_Res, dbo.Employeestbl.ResumeYN,
dbo.Employeestbl.MaskFitTestDate, dbo.Employeestbl.OrientationDocumentation, dbo.Facilitiestbl.Name,
dbo.Employeestbl.PPD2ndStepDate, dbo.Employeestbl.PPD2ndStepRes, dbo.Employeestbl.TBQDate, dbo.Employeestbl.TBQResults,
dbo.Employeestbl.ChestXRayDate, dbo.Employeestbl.ChestXRayRes, dbo.Employeestbl.MeaslesRubeolaDate,
dbo.Employeestbl.Birthdate, dbo.Employeestbl.RubellaDate, dbo.Employeestbl.VaricellaDate, dbo.Employeestbl.HepBVac,
dbo.Employeestbl.HepBWaiver, CoreMandatory_1.CorePartIDate, CoreMandatory_1.CorePartIIDate, CoreMandatory_1.CorePartIIIDate,
dbo.Employeestbl.I9Complete, dbo.Employeestbl.W4, dbo.Employeestbl.LSFormYN, dbo.Employeestbl.LSOnApplyingDate,
dbo.Employeestbl.Performance_Eval_Comp, dbo.Employeestbl.Reference1, dbo.Employeestbl.Reference2,
dbo.Employeestbl.Application, dbo.Employeestbl.OrientationCheckList, dbo.Employeestbl.RecOfEmploy,
dbo.Employeestbl.HIPAADate, dbo.Employeestbl.Tetanus, dbo.Employeestbl.WGDrugScreenDate, dbo.Employeestbl.MeaslesRubeolaRes,
dbo.Employeestbl.MumpsDate, dbo.Employeestbl.MumpsRes, dbo.Employeestbl.RubellaRes, dbo.Employeestbl.VaricellaRes,
dbo.Employeestbl.MeaslesRubeolaLabReports, dbo.Employeestbl.MumpsLabReports, dbo.Employeestbl.RubellaLabReports,
dbo.Employeestbl.VaricellaLabReports, dbo.Employeestbl.SkillsChecklistDate, dbo.Employeestbl.DrugScreen,
dbo.Employeestbl.OP_Date, dbo.Employeestbl.OP_Results, dbo.Employeestbl.Chauncey_Date, dbo.Employeestbl.Chauncey_Results,
dbo.Employeestbl.ID_A, dbo.Employeestbl.ID_A_Expires, dbo.Employeestbl.BackgroundCheckDate,
dbo.Employeestbl.AttestationFormDate, dbo.Employeestbl.AttestationFormSigned, dbo.Employeestbl.CPR,
dbo.Employeestbl.FluShutDate, dbo.Employeestbl.FluExempt, dbo.CoreMandatory.DementiaExams,
dbo.CoreMandatory.CNA_DementiaCare, dbo.Employeestbl.OrientationDocumentationFacility, dbo.Employeestbl.ORTVerificationDate,
dbo.FacilitiesDocumentsRequirements.AttestationForm, dbo.FacilitiesDocumentsRequirements.MalpracticeInsurance,
dbo.FacilitiesDocumentsRequirements.AhaBLS, dbo.Employeestbl.MaskFitTest, dbo.FacilitiesDocumentsRequirements.TwoStepPPD,
dbo.FacilitiesDocumentsRequirements.Tetanus, dbo.FacilitiesDocumentsRequirements.MMRVaricellaTiters,
dbo.FacilitiesDocumentsRequirements.FluVaccine, dbo.FacilitiesDocumentsRequirements.OrientationDocuementation,
dbo.FacilitiesDocumentsRequirements.SkillsCheckList, dbo.FacilitiesDocumentsRequirements.DimentiaExams,
dbo.FacilitiesDocumentsRequirements.MalpracticeReq, dbo.FacilitiesDocumentsRequirements.DrugScreenReq)
Thanks,
Ben
ASKER
Good night ryan,
guess will have to finalize that tom as becoming extremely late for me..
Thanks,
Ben
guess will have to finalize that tom as becoming extremely late for me..
Thanks,
Ben
Well that should only be the case if I'm omitting some param values, while here believe I have them all (see below)Yup, hence I believe the original error of Function Argument Count has been successfully resolved?
no worries let's continue tomorrow and just let us know if you need further assistance here cheers
ASKER
Hi Ryan,
1- In this post here, I was trying to copy the contents of a particular column and paste it in another editor, then do changes there and paste back in query designer, while the first part went fine, the final process got stuck (see pic OP), and as stated above I was trying to overcome this prob by doing it step by step and everything went fine until..I was trying to leave the column, then is when I got the Function Argument Count error, and as you see (ID: 41938475) there is no wrong # of columns, just an error with copy/paste feature.
Now for this issue I'm still wondering what is the solution as alter view will not substitute this functionality.
2- After being forced to do my modification in the query designer's SQL pane, when was about to save I was facing a diff issue, that with the syntax error https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
and there is where your suggestion to use alter view comes in handy..
Let me know if its clear.
Thanks,
Ben
Yup, hence I believe the original error of Function Argument Count has been successfully resolved?Actually since there are two questions posted looking similar (and both referencing the same query), lets do a recap of what was posted and whats being already answered.
1- In this post here, I was trying to copy the contents of a particular column and paste it in another editor, then do changes there and paste back in query designer, while the first part went fine, the final process got stuck (see pic OP), and as stated above I was trying to overcome this prob by doing it step by step and everything went fine until..I was trying to leave the column, then is when I got the Function Argument Count error, and as you see (ID: 41938475) there is no wrong # of columns, just an error with copy/paste feature.
Now for this issue I'm still wondering what is the solution as alter view will not substitute this functionality.
2- After being forced to do my modification in the query designer's SQL pane, when was about to save I was facing a diff issue, that with the syntax error https://www.experts-exchange.com/questions/28991255/Unable-to-save-view-in-SSMS.html?anchor=a41936009¬ificationFollowed=181356900&anchorAnswerId=41936009#a41936009
and there is where your suggestion to use alter view comes in handy..
Let me know if its clear.
Thanks,
Ben
hi Ben,
Well, there should be a reason why the method(s) we are using doesn't work. Probably due to the way we conduct it or it's the limitation of the tools itself that we are using.
But it's hard for me to imagine your issues and how you conduct it.
if this is really urgent, you may ask experts for help in Live or Gigs.
Well, there should be a reason why the method(s) we are using doesn't work. Probably due to the way we conduct it or it's the limitation of the tools itself that we are using.
But it's hard for me to imagine your issues and how you conduct it.
if this is really urgent, you may ask experts for help in Live or Gigs.
ASKER
Hi,
Thanks,
Ben
But it's hard for me to imagine your issues and how you conduct it.Well if you have my version of SSMS its very easy to reproduce it, just copy the function EmployeesMissingDocs and then try to work with the designer modifying the SQL I posted.
if this is really urgent,At the time I posted this was really urgent, however as I found a work around (using 2014) this became less important, now if you think there is really no other solution to this guess its time to close this question?
Thanks,
Ben
ASKER
You have the same version, great!
Actually It would not be enough just the function as you would need all the table structures in order to work with my SQL.
However you can test it with any query having a column being that long as this
It probably doesn't need to be that big, as this is about 3000 characters while in my experience after 1000 characters it couldn't handle anymore.
Thanks,
Ben
Actually It would not be enough just the function as you would need all the table structures in order to work with my SQL.
However you can test it with any query having a column being that long as this
dbo.EmployeesMissingDocs('', dbo.Employeestbl.ID,
dbo.Employeestbl.Title,
dbo.Employeestbl.LicenseNumSignedYN,
dbo.Employeestbl.LicenseExpires, dbo.Employeestbl.BclsExpires, dbo.Employeestbl.BclsSignedYN,
dbo.Employeestbl.BclsLetterDate, dbo.Employeestbl.MalpracticeExpires, dbo.Employeestbl.MalLevelOK,
dbo.Employeestbl.AclsExpires, dbo.Employeestbl.AclsSignedYN, dbo.Employeestbl.AclsLetterDate, dbo.Employeestbl.NalsExpires,
dbo.Employeestbl.NalsSignedYN, dbo.Employeestbl.NalsLetterDate, dbo.Employeestbl.PalsExpires, dbo.Employeestbl.PalsSignedYN,
dbo.Employeestbl.PalsLetterDate, dbo.Employeestbl.Other_Cert_Exp, dbo.Employeestbl.Other_Cert, dbo.Employeestbl.Physical,
dbo.Employeestbl.PhisycalPPD_Date, dbo.Employeestbl.PhisycalPPD_Res, dbo.Employeestbl.ResumeYN,
dbo.Employeestbl.MaskFitTestDate, dbo.Employeestbl.OrientationDocumentation, dbo.Facilitiestbl.Name,
dbo.Employeestbl.PPD2ndStepDate, dbo.Employeestbl.PPD2ndStepRes, dbo.Employeestbl.TBQDate, dbo.Employeestbl.TBQResults,
dbo.Employeestbl.ChestXRayDate, dbo.Employeestbl.ChestXRayRes, dbo.Employeestbl.MeaslesRubeolaDate,
dbo.Employeestbl.Birthdate, dbo.Employeestbl.RubellaDate, dbo.Employeestbl.VaricellaDate, dbo.Employeestbl.HepBVac,
dbo.Employeestbl.HepBWaiver, CoreMandatory_1.CorePartIDate, CoreMandatory_1.CorePartIIDate, CoreMandatory_1.CorePartIIIDate,
dbo.Employeestbl.I9Complete, dbo.Employeestbl.W4, dbo.Employeestbl.LSFormYN, dbo.Employeestbl.LSOnApplyingDate,
dbo.Employeestbl.Performance_Eval_Comp, dbo.Employeestbl.Reference1, dbo.Employeestbl.Reference2,
dbo.Employeestbl.Application, dbo.Employeestbl.OrientationCheckList, dbo.Employeestbl.RecOfEmploy,
dbo.Employeestbl.HIPAADate, dbo.Employeestbl.Tetanus, dbo.Employeestbl.WGDrugScreenDate, dbo.Employeestbl.MeaslesRubeolaRes,
dbo.Employeestbl.MumpsDate, dbo.Employeestbl.MumpsRes, dbo.Employeestbl.RubellaRes, dbo.Employeestbl.VaricellaRes,
dbo.Employeestbl.MeaslesRubeolaLabReports, dbo.Employeestbl.MumpsLabReports, dbo.Employeestbl.RubellaLabReports,
dbo.Employeestbl.VaricellaLabReports, dbo.Employeestbl.SkillsChecklistDate, dbo.Employeestbl.DrugScreen,
dbo.Employeestbl.OP_Date, dbo.Employeestbl.OP_Results, dbo.Employeestbl.Chauncey_Date, dbo.Employeestbl.Chauncey_Results,
dbo.Employeestbl.ID_A, dbo.Employeestbl.ID_A_Expires, dbo.Employeestbl.BackgroundCheckDate,
dbo.Employeestbl.AttestationFormDate, dbo.Employeestbl.AttestationFormSigned, dbo.Employeestbl.CPR,
dbo.Employeestbl.FluShutDate, dbo.Employeestbl.FluExempt, dbo.CoreMandatory.DementiaExams,
dbo.CoreMandatory.CNA_DementiaCare, dbo.Employeestbl.OrientationDocumentationFacility, dbo.Employeestbl.ORTVerificationDate,
dbo.FacilitiesDocumentsRequirements.AttestationForm, dbo.FacilitiesDocumentsRequirements.MalpracticeInsurance,
dbo.FacilitiesDocumentsRequirements.AhaBLS, dbo.Employeestbl.MaskFitTest, dbo.FacilitiesDocumentsRequirements.TwoStepPPD,
dbo.FacilitiesDocumentsRequirements.Tetanus, dbo.FacilitiesDocumentsRequirements.MMRVaricellaTiters,
dbo.FacilitiesDocumentsRequirements.FluVaccine, dbo.FacilitiesDocumentsRequirements.OrientationDocuementation,
dbo.FacilitiesDocumentsRequirements.SkillsCheckList, dbo.FacilitiesDocumentsRequirements.DimentiaExams,
dbo.FacilitiesDocumentsRequirements.MalpracticeReq, dbo.FacilitiesDocumentsRequirements.DrugScreenReq)
then in designer when you select that column, copy & paste somewhere else, modify something and try to paste back, you will see the paste feature disabled (like shown in picture OP).It probably doesn't need to be that big, as this is about 3000 characters while in my experience after 1000 characters it couldn't handle anymore.
Thanks,
Ben
Another way is to.... reduce the no of parameters to be passed into your Function, IF this can be simplified by pulling the relevant data from other table(s) instead.
ASKER
I can't execute it successfully with your scripts..You dont need to, just try to copy the content of the column from the grid, paste it in notepad and modify something, then try to paste back, you will get stuck right there..
Thanks,
Ben
hi Ben,
I did as what you had mentioned, but No error from my end...
I did as what you had mentioned, but No error from my end...
ASKER
I see yours is 2008 R2 while mine is not, perhaps this is making the diff..
I guess best would be to upgrade mine, and maybe get latest service packs as well?
Thanks,
Ben
I guess best would be to upgrade mine, and maybe get latest service packs as well?
Thanks,
Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Rayn!