Hi all:
I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:
DECLARE @.SchoolCode_Modified char(6)
SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))
SELECT @.SchoolCode_Modified AS Col1
The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:
CREATE PROCEDURE [LAF].[uspSaveLoanApplication]
...more params here
@.SchoolCode char(6),
@.BranchCode char(2),
@.PK int OUTPUT,
@.PreviousLoanApplicationStatus char(3) OUTPUT
AS
SET NOCOUNT OFF
SET @.PreviousLoanApplicationStatus = 0
IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)
BEGIN
SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)
SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus
END
-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting
DECLARE @.SchoolCode_Modified char(6)
DECLARE @.BranchCode_Modified char(2)
SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))
SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))
IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)
BEGIN
UPDATE [LAF].[LoanApplication] SET
....more here
[SchoolCode] = @.SchoolCode_Modified,
[BranchCode] = @.BranchCode_Modified,
...more here
WHERE
[LoanApplicationID] = @.LoanApplicationID
AND [UpdatedOn] = @.LastUpdated
SELECT @.LoanApplicationID PK
SET @.PK = @.LoanApplicationID
END
ELSE
BEGIN
INSERT INTO [LAF].[LoanApplication] (
.....more here
[SchoolCode],
[BranchCode],
.....more here
) VALUES (
....more here
@.SchoolCode_Modified,
@.BranchCode_Modified,
....more here
)
SET @.PK = SCOPE_IDENTITY()
SELECT @.PK PK
END
With a quick scan, your code appears correct.
How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?
|||It appears that your problem is that
@.SchoolCode is declared as char(6).
So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.
It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.
Dan
|||DanR1 wrote:
It appears that your problem is that
@.SchoolCode is declared as char(6).
So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.
It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.
Dan
Good catch...
Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.
|||Arnie,
Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)
So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.
Dan
|||DECLARE @.SchoolCode_Modified char(6)
SET @.SchoolCode_Modified = '123'
SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified
SELECT @.SchoolCode_Modified AS Col1
