| Author |
Topic |
|
mikeallenbrown
Yak Posting Veteran
72 Posts |
 Posted - 2015-05-07 : 12:09:38
|
My stored procedure isn't behaving the way I expect it too. When I execute it (exec Patient_Claims_Error_Check "a6dbdf58-6ae3-4049-8236-76a2be43b4c5", 9614) it says it completed successfully. I've verified that my DOB column for the record in the Patient table is NULL. When I execute this I'm expecting "error_messages" and "Has_error" to be populated accordingly but they remain NULL.
I'm sure I did something wrong ...could someone point out my error?
ALTER PROCEDURE [dbo].[Patient_Claims_Error_Check]
@whatGUID varchar,
@whatPatient int
AS
BEGIN
SET NOCOUNT ON;
-- uploadMessage will contain all error messages
DECLARE @uploadMessage varchar(max) = ''
-- hasError is a flag that is set to 1 if there is an error
DECLARE @hasError INT = 0
if ((SELECT DOB FROM Patient WHERE ID = @whatPatient) IS NULL)
BEGIN
SET @uploadMessage = @uploadMessage + 'Date of birth missing.<br>'
SET @hasError = 1
END
ELSE
if @hasError = 1
UPDATE Patient_Claim_PARENT_GeneralInfo SET error_messages = @uploadMessage WHERE Claim_GUID = @whatGUID
UPDATE Patient_Claim_PARENT_GeneralInfo SET Has_error = 1 WHERE Claim_GUID = @whatGUID
END
Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12) |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-05-07 : 18:32:04
|
@whatGUID varchar(Pick a Size) -- Without a size it defaults to one (1) and your GUID gets truncated.
I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-05-07 : 18:51:12
|
I believe the default size is 30.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-05-07 : 19:00:48
|
CAST(@MyVar as VARCHAR) does default to 30 but a declaration of a variable, in this case a parameter, defaults to one.create proc dbo.Junk
@vc varchar
as
begin
print len(@vc);
end
go
exec dbo.Junk 'Hello, World!' Returns 1
I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers |
|
|
|
mikeallenbrown
Yak Posting Veteran
72 Posts |
Posted - 2015-05-07 : 19:49:21
|
DOH! ....that was it.
Thank you all! The SP works great now!
Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-08 : 03:17:03
|
Might be the code is "slimmed down" for posting here, but if not you can do the updates in a single step
UPDATE Patient_Claim_PARENT_GeneralInfo
SET error_messages = @uploadMessage
, Has_error = 1
WHERE Claim_GUID = @whatGUID
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2015-05-08 : 05:37:30
|
Why is a parameter that expects GUID values not declared as UNIQUEIDENTIFIER?
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-08 : 06:06:42
|
quote:
Originally posted by SwePeso
Why is a parameter that expects GUID values not declared as UNIQUEIDENTIFIER?
Dunno about the O/P but we declare ours like that as the web site passes blank string if the form field is empty. Can't remember why (too long ago!) but some other datatypes the APP's language does a better job, but for quids it passes '' ... so we convert them to NULL in the Sproc.
Would be better handled in the APP and passed as a GUID DATATYPE end-to-end from APP, I know ... but just giving an example of a tacky workaround  |
|
|
|
|
Stored Procedure Help