Solved

Problems converting a variable to BigInt using SQL

Posted on 2007-11-29
6
2,807 Views
Last Modified: 2012-05-05
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_SearchCustomerChecks_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.BankAcctNum 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.BankAcctNum) LIKE convert(bigint,@BankAcctNumber)
  AND convert(bigint,chk.checkNumber) LIKE convert(bigint,@CheckNumber)
 
  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




0
Comment
Question by:svasilakos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 20376498
If you don't pass the @BankAcctNumber or @CheckNumber in, then you are trying to convert '%' to a bigint - which won't work.
(and why compare as bigint anyway if they are both varchar?)

 
0
 
LVL 7

Expert Comment

by:Wod
ID: 20376557
" 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. "
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 20376594
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.BankAcctNum) = @BankAcctNumber OR @BankAcctNumber = 0
  AND convert(bigint,chk.checkNumber) = @CheckNumber OR @CheckNumber= 0
 
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:svasilakos
ID: 20376806
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


0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 20377410
You just need to adjust a few lines:
...
...
IF @BankAcctNumber = ''
    SET @BankAcctNumber = NULL

IF @CheckNumber = ''
    SET @CheckNumber = NULL
...
...
--These two lines below are where I am having trouble I need to convert
  AND (@BankAcctNumber IS NULL OR convert(bigint,chk.BankAcctNum) = convert(bigint,@BankAcctNumber))
  AND (@CheckNumber IS NULL OR convert(bigint,chk.checkNumber) = convert(bigint,@CheckNumber))
...
0
 

Author Closing Comment

by:svasilakos
ID: 31411765
Perfect thanks very much
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question