Code not catching string length problem 2

[willydude]

The following bits of code are allowing too long of strings to be entered. The entries are being truncated before going into the table and will go into the table, but only if the trucated value is correct. (i.e. 1211 is too long, but is truncated to 12 which is legitimate.) Any way it’s looked at, it is still wrong.

My code should be catching the length issue and giving me an error message, but it is not. Is this a matter that would be corrected with the Trans/Commit/Rollback code?

I have verified in my tables the length and type of the fields.

Thanks.

Bill

--validate that the customer number exists in the parameter (not 
	--NULL or empty. Can be a length of 1, 2 or 3.
if @CustomerNumber is null or datalength(Ltrim(@CustomerNumber)) = 0
begin
set @Message = 'Customer Number Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@CustomerNumber as varchar (3))) < 1 or
	datalength(cast(@CustomerNumber as varchar (3))) > 3
begin
set @Message = 'Wrong Length of Customer Number'
set @Continue = 0
end

--validate the job number
if @JobNumber is null or datalength(Ltrim(@JobNumber)) = 0
begin
set @Message = 'Job Number Required'
set @Continue = 0
end
--then check its length. MUST BE 4, include leading 0, i.e. 0786
if datalength(cast(@JobNumber as varchar (4))) <> 4
begin
set @Message = 'Wrong Length of Job Number'
set @Continue = 0
end

--validate the Step number
if @StepNumber is null or datalength(Ltrim(@StepNumber)) = 0
begin
set @Message = 'Step Number Required'
set @Continue = 0
end
--then check its length. MUST BE 2, include leading 0, i.e. 07
if datalength(cast(@StepNumber as varchar (2))) <> 2
begin
set @Message = 'Wrong Length of Step Number'
set @Continue = 0
end

--validate Location
if @LocationNumber is null or datalength(Ltrim(@LocationNumber)) = 0
begin
set @Message = 'Location Number Required'
set @Continue = 0
end
--then check its length. Can be 1 or 2.
if datalength(cast(@LocationNumber as varchar (2))) < 1 or
	datalength(cast(@LocationNumber as varchar (2))) > 2

 

[ESquared]

Show your variable declarations.
If this is in a stored procedure, show the stored procedure declaration lines, too.

There are some obvious things, though. The following will never evaluate to true. Why have it in there?

datalength(cast(@CustomerNumber as varchar (3))) > 3

If you're converting from a number datatype, this will also never evaluate to true. Can you give an example of a number that has less than one character in it? And NULL always returns false in direct comparisons.

datalength(cast(@CustomerNumber as varchar (3))) < 1

If you're converting from a character datatype, why convert at all? And why are you using datalength? Don't use it unless spaces on the end are significant. With those assumptions, you can just do this:

IF Len(@CustomerNumber) = 0

or

IF @CustomerNumber = ''

"< 1" isn't necessary because the length of a string can't be negative anyway.

Finally, if you are casting to char from a number, why? Just use math:

IF NOT @CustomerNumber BETWEEN 0 AND 99 -- two digits
IF NOT @CustomerNumber BETWEEN 0 AND 999 -- three digits

Every single one of your IF statements is doing a cast that will automatically chop things at 4. Don't do that, just test for length.

Remember to give data types and SP declaration when you reply.

 

[ESquared]

>> chop things at 4

I mean, chop things at the lengths you're comparing, or not affect the comparison you want to make. So don't do it. Varchar(4) will never be longer than 4, and if you test for a string being less than 4, why convert it first? 1 < 4 and also 1 < 9999999.

To put it another way, there's no need to do:

((x > 4) ? 4 : x) < 4

Just do

x < 4

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[willydude]

E Squared,

Apparently I am not understanding what you have told me. I have used the LEN function, got rid of the DataLength and made some other changes to my original code. It’s still truncating entries. Here is the complete sproc.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Jobs_Insert_NewJobSteps] 
(@CustomerNumber varchar (3), 
@JobNumber varchar (4),
@StepNumber varchar (2),
@LocationNumber varchar (2), 
@StepCode varchar (2),
@StepDescription varchar (50),
@PrevailingWage numeric (5,2) = NULL, 
@Standard Numeric (10,1) = NULL,
@GuaranteedStepWage numeric (5,2) = NULL, 
@StepNumber_PK_ID int output,
@JobNumber_PK_ID int output,
@Message varchar (30) output)
as
--declare variables
declare @Continue bit
--		@CustomerNumber_Required bit
--		@JobNumber_Required bit
--		@LocationNumber_Required bit,
--		@JobNumber_PK_ID int

set @Continue = 1
set @JobNumber_PK_ID = 0 
--set @JobNumber = 0  --<<DO NOT DO THIS
--set @LocationNumber = 0  --<<DO NOT DO THIS
set @Message = ''

--No leading zeroes req'd for CustomerNumber.
--
--In Customers table, CustomerNumber is varchar (3).
--
--CustomerNumber is still truncated into table. "125" is proper
--length, but it is not an existing CustomerNumber.
--When selecting the Customer_PK_ID from the Customer table for
--use in selecting the proper JobNumber_PK_ID from the JobNumber
--table, SQL uses "12" which is a valid number, instead of
--telling me that it cannot find a JobNumber_PK_ID with an existing
--Customer_PK_ID for Customer 125. (or other relevant error
--message.) Customer 125 does not exist.

None of the following work correctly. All will truncate and if the result after truncating is valid, it accepts it. It should not.

--validation of entries begins
--validate that the customer number exists in the parameter (not 
	--NULL or empty 

IF NOT @CustomerNumber BETWEEN 0 AND 1000 --<< 1 to 999 is the range
Begin
set @Message = 'Valid Customer Number Required'
set @Continue = 0
end

--validate the job number by checking its length. 
--Must be 4, include leading 0, i.e. 0786.
if @JobNumber is null or Len(@JobNumber) <> 4 
begin
set @Message = 'Valid Job Number Required'
set @Continue = 0
end

--validate the Step number. Length Must be 2, including 
-- leading 0, i.e. 07.  Must be #'s, not letters.
if @StepNumber is null or len(@StepNumber) <> 2
begin
set @Message = 'Valid Step Number Required'
set @Continue = 0
end


--validate Location. May be 1 or 2 digits in length. 
--Leading zeroes not required.
IF NOT @LocationNumber BETWEEN 0 AND 100 --must be 1 to 99
begin
set @Message = 'Valid Location Number Required'
set @Continue = 0
end

Following will truncate PRR to PR. It should not.

--validate Step Code. StepCode must be entered.
--Letters such as PR, HR, etc. Not numbers.
if @StepCode is null or Len(@StepCode) <> 2
begin
set @Message = 'Valid Step Code Required'
set @Continue = 0
end

Following is the rest of my code. If I can get the above to work w/o truncating, I believe that I can get any of the others that truncate to work.

--validate description. Can be up to 50 characters long.
if @StepDescription is null  --must have some sort of descr entered.
begin
set @Message = 'Description Required'
set @Continue = 0
end

--the PrevailingWage (PW) is required ONLY when the StepCode 
--from above is PR or TS. otherwise, it must be either 0 or NULL.
--It appears that the logic is working when PR or TS, but 
--validation not working. can enter "PRR" and will truncate to "PR"
--and accept.

if @StepCode = 'PR' or @StepCode = 'TS' --check value of StepCode
BEGIN  --if PR or TS then validate PrevailingWage. 
  if @PrevailingWage is null or @PrevailingWage = 0
	begin
	set @Message = 'Prevailing Wage Required'
	set @Continue = 0
	end
end
--If not PR or TS, value = NULL


--Enter and validate Standard
if @StepCode = 'PR'  --PR only StepCode requiring a standard
begin
	if @Standard is null or @Standard = 0
	begin
	set @Message = 'Standard Required'
	set @Continue = 0
	end
end
--If not PR, value = NULL

if @StepCode = 'TS'  --TS does not require a standard
begin
	if @Standard is NOT null or @Standard <> 0
	begin
	set @Message = 'Can Not Use Standard With This Step Code'
	set @Continue = 0
	end
else  --@StepCode <> 'PR'
	begin
	set @Standard = null
	end
end

if @StepCode = 'TS'  --TS does not require a gsw
begin  --@GuaranteedStepWage is a value, not a code
	if @GuaranteedStepWage is NOT null or @GuaranteedStepWage <> 0
	begin
	set @Message = 'Can Not Use Guaranteed Step Wage With This Step Code'
	set @Continue = 0
	end
end

--validate GuaranteedStepWage (GSW)
if @StepCode = 'GS'  --
--must validate if used.
Begin
	if @GuaranteedStepWage is null or @GuaranteedStepWage = 0
	begin
	set @Message = 'Guaranteed Step Wage Required'
	set @Continue = 0
	end
else
	begin
	set @GuaranteedStepWage = null
	end
end

--have quite a few more StepCodes to put in. All will use the same
--basic principles above.

--all data is validated at this point.
if @Continue = 0
begin
return 1
end
else

if @StepNumber_PK_ID is null or
	@StepNumber_PK_ID = 0
begin

SET @JobNumber_PK_ID  =
(select JobNumber_PK_ID 
from
tbl_Jobs_JobNumber
where
tbl_Jobs_JobNumber.Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber)

print @JobNumber_PK_ID 
print 'CCC' + COALESCE(@CustomerNumber,'') + 'CCC' 
print 'JJJ' + COALESCE(@JobNumber,'') + 'JJJ' 
print 'SSS' + COALESCE(@StepNumber,'') + 'SSS' 
print 'LLL' + COALESCE(@LocationNumber,'') + 'LLL'

--the following puts all the info into the JobsJobSteps table
--The Customer_PK_ID and the LocationNumber do not need to be
--in the JobSteps table b/c the JobNumber_PK_ID is already
--composed of the Customer_PK_ID, the Job Number enterd by the
--user and the Location Number entered by the user.

insert into tbl_Jobs_JobSteps
(JobNumber_PK_ID, 
StepNumber,
StepDescription,
StepCode,
PrevailingWage,
Standard,
GuaranteedStepWage
--StartDate
)
values
(@JobNumber_PK_ID, 
@StepNumber, 
@StepDescription,
@StepCode,
@PrevailingWage,
@Standard,
@GuaranteedStepWage
)
--assigns new StepNumber_PK_ID to new record just added.
set @StepNumber_PK_ID = scope_identity ()
end

Thanks for the help.

Bill

 

[SQLDenis]

>>Following will truncate PRR to PR. It should not.

what do you expect with varchar(2)?
@StepCode varchar (2),



run this to 'understand'

declare @StepCode varchar (2)

select @StepCode ='wasabi'

select @StepCode

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

It all comes from your variable declarations.

Here's just one:

Following will truncate PRR to PR. It should not.

--validate Step Code. StepCode must be entered.
--Letters such as PR, HR, etc. Not numbers.
if @StepCode is null or Len(@StepCode) <> 2
begin
set @Message = 'Valid Step Code Required'
set @Continue = 0
end

That's actually not truncating anything. THIS is:

ALTER procedure [dbo].[Jobs_Insert_NewJobSteps] 
(@CustomerNumber varchar (3), 
@JobNumber varchar (4),
@StepNumber varchar (2),
@LocationNumber varchar (2), 
[b]@StepCode varchar ([red]2[/red])[/b],
@StepDescription varchar (50),
@PrevailingWage numeric (5,2) = NULL, 
@Standard Numeric (10,1) = NULL,
@GuaranteedStepWage numeric (5,2) = NULL, 
@StepNumber_PK_ID int output,
@JobNumber_PK_ID int output,
@Message varchar (30) output)

You declared @StepCode as varchar(2). That will truncate anything that is longer to 2 characters right there. Make it long enough to accept any expected length of input, or at least 3 if you need to detect that it was longer than 2 and nothing else.

 

[willydude]

I have made these changes:

@CustomerNumber varchar (4), --max Cust# len is 3.
@JobNumber varchar (5), --Job# length must be 4
@StepNumber varchar (3), --Step# length must be 2
@LocationNumber varchar (3), --max Loc# length is 2
@StepCode varchar (3), --StepCode length must be 2

The CustomerNumber appears to be the only one causing trouble. The CustomerNumber column in the Customers table has been set to varchar (4). The @CustomerNumber is still being truncated. i.e. “125” is cut down to “12”. An incorrect @CustomerNumber is not being caught.

Both IF statements below have been tried.

IF NOT @CustomerNumber BETWEEN 0 AND 1000 --<< 1 to 999 is the range
--if @CustomerNumber is null or Len(@CustomerNumber) > 3 
Begin
set @Message = 'Valid Customer Number Required'
set @Continue = 0
end

--
The following all appear to be working properly.

--validate the job number by checking its length. 
--Must be 4, include leading 0, i.e. 0786.
if @JobNumber is null or Len(@JobNumber) <> 4 
begin
set @Message = 'Valid Job Number Required'
set @Continue = 0
end

--validate the Step number. Length Must be 2, including 
-- leading 0, i.e. 07.  Must be #'s, not letters.
if @StepNumber is null or len(@StepNumber) <> 2
begin
set @Message = 'Valid Step Number Required'
set @Continue = 0
end

--validate Location. May be 1 or 2 digits in length. 
--Leading zeroes not required.
--IF NOT @LocationNumber BETWEEN 0 AND 100 --must be 1 to 99
if @LocationNumber is null or len(@LocationNumber) > 2
begin
set @Message = 'Valid Location Number Required'
set @Continue = 0
end

--validate Step Code. StepCode must be entered.
--Letters such as PR, HR, etc. Not numbers.
if @StepCode is null or Len(@StepCode) <> 2
begin
set @Message = 'Valid Step Code Required'
set @Continue = 0
end

Thanks for the help.

Bill

 

[willydude]

This line used to say NOT NULL. I thought that changing it to whats below would fix the truncating/letting bad number go through problem. It did not.

if @CustomerNumber is null or Len(@CustomerNumber) > 3

Bill

 

[SQLDenis]

Maybe it is time for you to get yourself a good book or study BOL because changing stuff from NULL to NOT NULL without understanding the implication can cause disasters later down the road

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[willydude]

Denis, your right, I do need to spend some time on the NULL. Thanks for the input. It looks to me that part of the problem was with the JobNumber_PK_ID selection code. I do not believe my original code was pulling the Customer_PK_ID. It works now with the code below.

SET @JobNumber_PK_ID  =
(select JobNumber_PK_ID 
from
tbl_Jobs_JobNumber
join
tbl_Customers_CustomerBasicInfo
ON  
tbl_Jobs_JobNumber.Customer_PK_ID = 
	tbl_Customers_CustomerBasicInfo.Customer_PK_ID
where
tbl_Customers_CustomerBasicInfo.CustomerNumber = @CustomerNumber
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber)

Thanks.

Bill

 

[ESquared]

Len(@CustomerNumber) > 3

This will allow customer numbers of length 3 to go through. You need to use > 2 or >= 3.

This is really basic stuff. Is there anyone else at your company that has experience with SQL that can help you?

 

[willydude]

E,

I've gone back and checked quite a bit of my code and found a number of simple type errors. I'm doing a lot of reading on my own to learn. No one at work does SQL. I did go to a SQL users group a year or so back, I need to go back.

Thanks for any past and future help.

Bill

 

[ESquared]

You're welcome.

Any person with programming experience (or even advanced Excel formula experience) could help out with some of the string handling logic.