Solved

Int and VarChar types in where clause

Posted on 2009-07-13
8
918 Views
Last Modified: 2012-06-27
hi,

I have the below Stored proc which passes in the field name and values to filter on. It works great. However, some of the columns are of type int and others are varchar. The varchar columns need the values in quotes, but the int columns do not. Therefore the below only works for varchars.

Is there any easy fix? Thanks
CREATE PROC test

@columName  varchar(30),

@value  varchar(30)

AS

BEGIN

    EXECUTE(

    'SELECT     D.id_desk,

                D.id_region           

    FROM        DESK D,

                INSTRUMENT I

    WHERE '      + @columName + ' LIKE ''' + @value +'''

    AND         I.id_desk = D.id_desk' )

END

go

Open in new window

0
Comment
Question by:bowemc
  • 4
  • 2
8 Comments
 
LVL 6

Accepted Solution

by:
IncisiveOne earned 250 total points
ID: 24844729
Hang on, that does not "work great".  This sort of thing is completely incorrect.  First an explanation.  Actually, two explanations.

1  SQL is sometimes called a loosely-typed language, because there are few enforcements of the Datatypes in the language itself.  That is incorrect.  The whole idea is that SQL is a DATABASE manipulation language, and the DATABASE is not loosely-typed, it is fixed (the Datatypes are set once, and they do not change.  The correct understanding is, SQL is actually a tightly-typed language, the Typing is in the database.  Therefore it is incumbent upon you, all coders, to always be aware of the Datatype of the column, and to treat it accordingly; using the correct Datatype wherever the column is referenced.

Note that Datatype mismatches are probably the most common mistake, and they always lead to poor performance (eg. Sybase cannot use the index on an indexe column, when the wrong Datatype is used, and thus has to table scan).  This is easily confirmed in the showplan, which every coder should produce and examine for every code segment.  Datatypes mismatches are also the easiest mistakes to avoid; by having good simple standards:

Always use the correct Datatype for the column.
1.1  Therefore you cannot do what you are trying to do (write a generic SQL statement [forget the proc for now] that can perform a LIKE on any column).

1.2  Like is for char and varchar only, you cannot perform like on int, smallint, etc.

2  I appreciate that you are learning SQL, stored procs, and how to go about creating the various objects you need; that this is test proc.  However, in order for you to learn the correct methods (and to identify the incorrect methods), I will treat this proc as real (not test), and identify the issues.

2.1  The use of execute here is superfluous, and can be removed.  The code will  perform better, and it would lead you to a better understanding of the problem/solution.  Stated another way: take the execute out, get the code working without it, and if you need to, after you have working code, then put it back in.

2.2  Thing about this.  Regardless of what you write, immediate code, ordinary code, dynamic SQL, stored procs, the Optimiser has to (a) parse it (b) compile it and then (c) execute it.  Therefore the simple straight SQL is the best, to write and test.  Once you have that working correctly, then place it in dynamic or immediate code.  That means you will have specific code segments for each table or table combination; generic SQL in an application is not a reasonable goal.  Generic SQL is qiuite reasonable for utilities, but that is a different story, not addressed here; by definition, your application code is not an utility.

2.3  You do not have any error checking or column Datatype checking, which you must have

2.4  Remember the Datatype of the column in the database is fixed, and that Datatype is the final truth about the column, so that is what our code should be based on.  It should not be based on "what we want to do", it should be based on "what the column is", and therefore what is possible on the column.

Cheers



CREATE PROC

    DeskInstrument_search

        @columnName  varchar(30) = NULL,

        @searchStr   varchar(30) = NULL

AS

    IF @columnName = NULL

        BEGIN

        PRINT "You must provide a @columnName"

        -- cannot search a column without knowing which col to search

        GOTO EXIT_ERR

        END
 

    -- check if column is known var/char column

    -- exclude int, numeric, date, etc

    IF  @columnName != "deskName" AND 

        @columnName != "deskDescription" AND

        @columnName != "instrName" AND

        @columnName != "instrDescription" AND

        @columnName != "instrAddress1" AND

        @columnName != "instrAddress2"

        BEGIN

        PRINT "@columnName must be a var/char column in DESK or INSTRUMENT"

        GOTO EXIT_ERR

        END
 

    IF CHARINDEX( "%", @searchStr ) = 0  -- et cetera

        BEGIN

        PRINT "@searchStr must be a valid LIKE operand"

        -- otherwise we cannot perform LIKE with it

        GOTO EXIT_ERR

        END
 

    IF @searchStr = NULL

        SELECT  D.id_desk,

                D.id_region           

            FROM  DESK D,

                  INSTRUMENT I

            WHERE I.id_desk = D.id_desk

    ELSE

        SELECT  D.id_desk,

                D.id_region           

            FROM  DESK D,

                  INSTRUMENT I

            WHERE I.id_desk = D.id_desk

            AND   @columnName LIKE @searchStr
 

    IF @@rowcount = 0

        BEGIN

        PRINT "Search string %1! not found in column %1!", @searchStr, @columnName

        END
 

EXIT_OK:

    RETURN 0
 

EXIT_ERR:

    RETURN -1

Open in new window

0
 
LVL 6

Expert Comment

by:IncisiveOne
ID: 24844887
Er, that's for discussion/progress, obviously the code will not work without the execute.
0
 
LVL 13

Assisted Solution

by:alpmoon
alpmoon earned 250 total points
ID: 24856221
What about this? It would convert numeric columns into char and then search the value:

CREATE PROC test
@columName  varchar(30),
@value  varchar(30)
AS
BEGIN
  IF exists(select * from syscolumns
                  where id in (object_id('DESK), object_id(' INSTRUMENT'))
                       and type in (39, 47) -- char or varchar
    EXECUTE(
    'SELECT     D.id_desk,
                D.id_region          
    FROM        DESK D,
                INSTRUMENT I
    WHERE '      + @columName + ' LIKE ''' + @value +'''
    AND         I.id_desk = D.id_desk' )

  ELSE

    EXECUTE(
    'SELECT     D.id_desk,
                D.id_region          
    FROM        DESK D,
                INSTRUMENT I
    WHERE convert(char,'      + @columName + ') LIKE ''' + @value +'''
    AND         I.id_desk = D.id_desk' )

END
go
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:alpmoon
ID: 24957813
Have you tried it? Is it useful for your end?
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 25156430
I think my solution is more flexible and efficient. If table structure is changed, the procedure doesn't need to be modified.

I suggest split between me and IncisiveOne
0
 
LVL 13

Expert Comment

by:alpmoon
ID: 25164789
I think my solution is more flexible and efficient. If table structure is changed, the procedure doesn't need to be modified.

I suggest split between me and IncisiveOne
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sybase ASE 15.7 do not know datepart function 2 1,204
Best Fit Equation for a Line 5 832
How do I get SCOPE_IDENTITY working correctly. 3 830
SyBase SQL Query 7 153
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now