Need help with SP

1

HI,

I have below SP which when I I run it gives me error : "Invalid character value for cast specification"
cant figure out, pls help.
Thanks.

CREATE PROC [dbo].[CHS_exp_patient_provider]
(
@provider_id VARCHAR(36)
,@practice_id VARCHAR(5)
,@reason_exp_id VARCHAR(36)
,@user_id INT
,@exp_date VARCHAR(8)
,@PatientsUpdated INT OUT
,@DESIGN Bit
)

as

--DECLARE @txt_exp_date VARCHAR(8)
--set @PatientsUpdated = ''

/*
Stored procedure to update the patient_provider table, setting an expiration date for the
selected provider on all records for the current practice
*/

--convert DATE field to yyyymmdd text format
--select @txt_exp_date = CONVERT(VARCHAR(8), @exp_date, 112)
--select @txt_exp_date = RIGHT(@exp_date,4) + '' + SUBSTRING(@exp_date,4,2) + '' + LEFT(@exp_date,2)

UPDATE patient_provider
SET
exp_date = @exp_date
,modified_by = @user_id
,modify_timestamp = GETDATE()
,reason_exp_id = @reason_exp_id

--select * from patient_provider
where provider_id = @provider_id
AND practice_id = @practice_id
AND exp_date = ''
select @PatientsUpdated=@@ROWCOUNT

GO

2

It's hard to see what is being cast without the table schema. Can you supply that? Alternately, have you tried to find the exact cast it is flagging by commenting out a line at a time? If we know the column, do we know the data value it is trying to cast?

3

I see that it's commented out, and without knowing what's being passed in to the SP for the @exp_date variable or what the exp_date datatype is, it's tough to know where the problem is, but
select @txt_exp_date = RIGHT(@exp_date,4) + '' + SUBSTRING(@exp_date,4,2) + '' + LEFT(@exp_date,2) doesn't look like it returns a valid date value so if you're using that to do anything, it will probably blow up:

DECLARE @txt_exp_date VARCHAR(8)
DECLARE @exp_date VARCHAR(8) = '12052016'

select @txt_exp_date = CONVERT(VARCHAR(8), @exp_date, 112)
select @txt_exp_date
select @txt_exp_date = RIGHT(@exp_date,4) + '' + SUBSTRING(@exp_date,4,2) + '' + LEFT(@exp_date,2)
select @txt_exp_date

The bottom expression sets today's date to 20162205 (US format) or 20165212 (European)

4

thanks Stephen, what do you mean by supplying table schema?
kind of new to this.
Thanks, Pasi.

5

Hi Tucker, the exp_date is an input from application/user. its a field in application which brings up the calendar and when you select a date it puts it in the field called my date in the form. hope this helps. the filed type is "date" and length is 10.
Thanks,
Pasi

6

Table definition: What are the columns, their data types, etc.
If the table column has a data type of DATETIME and the parameter has a data type of VARCHAR, we can see that an implicit CAST will be performed.

7

pls see attached.

8

I don't know but I'm suspicious of the @provider_id and @reason_exp_id assignments in the UPDATE statement. The reason is that you are implicitly converting a varchar(36) parameter to a uniqueidentifier. This is legal but the values passed in would need to conform to a specific layout (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) where every digit is numeric or A through F. If we could find the actual value that was failing, that would point us in the right direction.

9

I've known external systems to pass GUIDs with "{" ... "}" delimiters, which can muck things up - particularly with a varchar(36) parameter which would truncate the data. (We always used varchar(40) for GUIDs [as strings] to allow for that, but also its easier for me, at my age, to remember the round-number rather than 36 !! - for the same reason I use varchar(20) for numbers as that is big enough for the largest numbers and stops me getting caught out using varchar(5) when the spec changes from MAX = 99,999 to MAX = 999,999 !)

10

Would also help to see exactly what the call statement is - e.g. spy on the APP using SQL Profiler to see exactly what SQL statement is being passed to SQL to EXEC that stored procedure.

Can then test that against the Stored Procedure with a PRINT statement in the SProc outputting the @Parameters to see what value they actually contain (at various points in the SProc)