Stored Procedure Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Stored Procedure Help

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources