svasilakos
asked on
Problems converting a variable to BigInt using SQL
What I need to do is convert a stored procedure variable from varchar to bigint. But I'm missing something. What I want to do is comvert the variables @BankAcctNumber and @CheckNumber. I've tried CAST and CONVERT but I get an error about problem converting varchar to big int. I know I missing something fairly straight forward. My SQL knowledge is basic.
Here is the entire query..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
ALTER PROCEDURE [dbo].[sp_REPORT_SearchCus tomerCheck s_DFSOPS]
@firstName varchar(30),
@lastName varchar(30),
@BankABANumber varchar(9),
@BankAcctNumber varchar(20),
@CheckNumber varchar(20),
@IDNumber varchar(20),
@IDType varchar(2),
@IDState varchar(2),
@IDCountry varchar(2)
AS
BEGIN
SET NOCOUNT ON
IF ((@firstName IS NULL) OR (@firstName = ''))
BEGIN
SET @firstName = '%'
END
IF ((@lastName IS NULL) OR (@lastName = ''))
BEGIN
SET @lastName = '%'
END
IF ((@BankABANumber IS NULL) OR (@BankABANumber = ''))
BEGIN
SET @BankABANumber = '%'
END
IF ((@BankAcctNumber IS NULL) OR (@BankAcctNumber = ''))
BEGIN
SET @BankAcctNumber = '%'
END
IF ((@CheckNumber IS NULL) OR (@CheckNumber = ''))
BEGIN
SET @CheckNumber = '%'
END
IF ((@IDNumber IS NULL) OR (@IDNumber = ''))
BEGIN
SET @IDNumber = '%'
END
IF ((@IDType IS NULL) OR (@IDType = ''))
BEGIN
SET @IDType = '%'
END
IF ((@IDState IS NULL) OR (@IDState = ''))
BEGIN
SET @IDState = '%'
END
IF ((@IDCountry IS NULL) OR (@IDCountry = ''))
BEGIN
SET @IDCountry = '%'
END
SELECT DISTINCT c.FirstName,c.LastName,chk .BankAba as BankABANumber,chk.BankAcct Num as BankAcctNumber
,chk.CheckNumber
,c.IDNumber,
(case when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 0 then 'DL'
when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 1 then 'MIL'
when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 2 then 'ST'
when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 3 then 'PASS'
else '' end) as IDType,
c.IDState,c.IDCountry
FROM Customer c
JOIN Checks chk ON c.CustomerID = chk.CustomerID
WHERE c.FirstName LIKE @firstName
AND c.LastName LIKE @lastName
AND chk.BankAba LIKE @BankABANumber
--These two lines below are where I am having trouble I need to convert
AND convert(bigint,chk.BankAcc tNum) LIKE convert(bigint,@BankAcctNu mber)
AND convert(bigint,chk.checkNu mber) LIKE convert(bigint,@CheckNumbe r)
AND c.IDNumber LIKE @IDNumber
AND c.IDType LIKE @IDType
AND c.IDState LIKE @IDState
AND c.IDCountry LIKE @IDCountry
ORDER BY c.LastName ASC
END
-Steve
Here is the entire query..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--------------------------
ALTER PROCEDURE [dbo].[sp_REPORT_SearchCus
@firstName varchar(30),
@lastName varchar(30),
@BankABANumber varchar(9),
@BankAcctNumber varchar(20),
@CheckNumber varchar(20),
@IDNumber varchar(20),
@IDType varchar(2),
@IDState varchar(2),
@IDCountry varchar(2)
AS
BEGIN
SET NOCOUNT ON
IF ((@firstName IS NULL) OR (@firstName = ''))
BEGIN
SET @firstName = '%'
END
IF ((@lastName IS NULL) OR (@lastName = ''))
BEGIN
SET @lastName = '%'
END
IF ((@BankABANumber IS NULL) OR (@BankABANumber = ''))
BEGIN
SET @BankABANumber = '%'
END
IF ((@BankAcctNumber IS NULL) OR (@BankAcctNumber = ''))
BEGIN
SET @BankAcctNumber = '%'
END
IF ((@CheckNumber IS NULL) OR (@CheckNumber = ''))
BEGIN
SET @CheckNumber = '%'
END
IF ((@IDNumber IS NULL) OR (@IDNumber = ''))
BEGIN
SET @IDNumber = '%'
END
IF ((@IDType IS NULL) OR (@IDType = ''))
BEGIN
SET @IDType = '%'
END
IF ((@IDState IS NULL) OR (@IDState = ''))
BEGIN
SET @IDState = '%'
END
IF ((@IDCountry IS NULL) OR (@IDCountry = ''))
BEGIN
SET @IDCountry = '%'
END
SELECT DISTINCT c.FirstName,c.LastName,chk
,chk.CheckNumber
,c.IDNumber,
(case when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 0 then 'DL'
when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 1 then 'MIL'
when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 2 then 'ST'
when(Select IDType FROM CustomerView WHERE CustomerID = c.CustomerID)= 3 then 'PASS'
else '' end) as IDType,
c.IDState,c.IDCountry
FROM Customer c
JOIN Checks chk ON c.CustomerID = chk.CustomerID
WHERE c.FirstName LIKE @firstName
AND c.LastName LIKE @lastName
AND chk.BankAba LIKE @BankABANumber
--These two lines below are where I am having trouble I need to convert
AND convert(bigint,chk.BankAcc
AND convert(bigint,chk.checkNu
AND c.IDNumber LIKE @IDNumber
AND c.IDType LIKE @IDType
AND c.IDState LIKE @IDState
AND c.IDCountry LIKE @IDCountry
ORDER BY c.LastName ASC
END
-Steve
" I don't think you will find an easy solution to this. If you must have the information in BIGINT format, a compromise would be to create a virtual (computed) column with the datatype BIGINT. This virtual column would have a formula that converted the existing VARCHAR column contents to BIGINT using the CONVERT command. An added advantage is the virtual column doesn't take up any space. "
You always have this option:
1) Convert @BankAcctNumber to a BigInt variable
2) Convert @CheckNumber to a BigInt variable
3) Change the variable setup code like this
IF (@BankAcctNumber IS NULL)
BEGIN
SET @BankAcctNumber = 0
END
IF (@CheckNumber IS NULL)
BEGIN
SET @CheckNumber = 0
END
4) Change the WHERE clauses like this:
AND convert(bigint,chk.BankAcc tNum) = @BankAcctNumber OR @BankAcctNumber = 0
AND convert(bigint,chk.checkNu mber) = @CheckNumber OR @CheckNumber= 0
1) Convert @BankAcctNumber to a BigInt variable
2) Convert @CheckNumber to a BigInt variable
3) Change the variable setup code like this
IF (@BankAcctNumber IS NULL)
BEGIN
SET @BankAcctNumber = 0
END
IF (@CheckNumber IS NULL)
BEGIN
SET @CheckNumber = 0
END
4) Change the WHERE clauses like this:
AND convert(bigint,chk.BankAcc
AND convert(bigint,chk.checkNu
ASKER
Let me tell you what I'm trying to do. Basically I'm trying to compare two strings ( actually bank account numbers) but we store bank account numbers as varchar becuase some bank account numbers have leading 0's and we want to store them just as we read them off of checks.
My issue is I get these bank account numbers from two different sources. Sometimes they have leading 0's sometimes they do not. The source determines that an I only control one of the sources. So comparing the strings is difficult. My solution was to just convert both the strings to bigint , purley to strip the leading 0's so I could make a match.
Basically the input into the stored procedure for the @BankAcctNumber and @CheckNumber is coming from a web page I control. Unless you guys know I could post another question under ASP.NET development on how to convert it before I post it into the stored procedure....I'm not sure how to do that
Steve
My issue is I get these bank account numbers from two different sources. Sometimes they have leading 0's sometimes they do not. The source determines that an I only control one of the sources. So comparing the strings is difficult. My solution was to just convert both the strings to bigint , purley to strip the leading 0's so I could make a match.
Basically the input into the stored procedure for the @BankAcctNumber and @CheckNumber is coming from a web page I control. Unless you guys know I could post another question under ASP.NET development on how to convert it before I post it into the stored procedure....I'm not sure how to do that
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect thanks very much
(and why compare as bigint anyway if they are both varchar?)