Professional Web Applications Themes

Stored-ProceduConstruct Where stmt dynamically, how? - Microsoft SQL / MS SQL Server

Newbie question about T-SQL. Here is a my code. Basically I want to give user flexibility of inputting search criteria. If all parameters are empty, there will be no where stmt. I got syntax error on last where statement. Can anybody tell me is it going to work in stored- procedure. --Select user based on selection conditions CREATE PROCEDURE dbo.UsersSelectBySearch ( id nvarchar(50), firstname nvarchar(200), lastname nvarchar(200), username nvarchar(200) ) AS DECLARE WhereString nvarchar(500) SELECT WhereString = '' IF id <> '' SELECT WhereString = ' OR id = ' + id IF firstname <> '' SELECT WhereString = WhereString ...

  1. #1

    Default Stored-Procedure: Construct Where stmt dynamically, how?

    Newbie question about T-SQL. Here is a my code. Basically
    I want to give user flexibility of inputting search
    criteria. If all parameters are empty, there will be no
    where stmt.
    I got syntax error on last where statement.

    Can anybody tell me is it going to work in stored-
    procedure.

    --Select user based on selection conditions
    CREATE PROCEDURE dbo.UsersSelectBySearch (
    id nvarchar(50),
    firstname nvarchar(200),
    lastname nvarchar(200),
    username nvarchar(200)
    )
    AS

    DECLARE WhereString nvarchar(500)
    SELECT WhereString = ''
    IF id <> ''
    SELECT WhereString = ' OR id = ' + id
    IF firstname <> ''
    SELECT WhereString = WhereString + ' OR firstname = '
    + firstname
    IF lastname <> ''
    SELECT WhereString = WhereString + ' OR lastname = '
    + lastname
    IF username <> ''
    SELECT WhereString = WhereString + ' OR username = '
    + username
    IF WhereString <> ''
    SELECT WhereString = RIGHT( WhereString, LEN(
    WhereString ) - 3 )
    PRINT WhereString
    SELECT id, username, password, firstname, lastname,
    title, address1, address2, city, state, postalcode,
    country, email, phone, fax,
    organization, creationdate, modifydate
    FROM Users
    WHERE (SELECT WhereString )

    GO
    Frank Jiang Guest

  2. #2

    Default Re: Stored-Procedure: Construct Where stmt dynamically, how?

    Check the "The Curse and Blessings of Dynamic SQL" article at
    [url]http://www.algonet.se/~sommar/dynamic_sql.html[/url].

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: [url]http://www.sqlserverfaq.com[/url]
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - [url]http://www.sqlpass.org[/url]

    "Frank Jiang" <frank_z_jhotmail.com> wrote in message
    news:38bf01c3405b$30a272a0$a601280aphx.gbl...
    > Newbie question about T-SQL. Here is a my code. Basically
    > I want to give user flexibility of inputting search
    > criteria. If all parameters are empty, there will be no
    > where stmt.
    > I got syntax error on last where statement.
    >
    > Can anybody tell me is it going to work in stored-
    > procedure.
    >
    > --Select user based on selection conditions
    > CREATE PROCEDURE dbo.UsersSelectBySearch (
    > id nvarchar(50),
    > firstname nvarchar(200),
    > lastname nvarchar(200),
    > username nvarchar(200)
    > )
    > AS
    >
    > DECLARE WhereString nvarchar(500)
    > SELECT WhereString = ''
    > IF id <> ''
    > SELECT WhereString = ' OR id = ' + id
    > IF firstname <> ''
    > SELECT WhereString = WhereString + ' OR firstname = '
    > + firstname
    > IF lastname <> ''
    > SELECT WhereString = WhereString + ' OR lastname = '
    > + lastname
    > IF username <> ''
    > SELECT WhereString = WhereString + ' OR username = '
    > + username
    > IF WhereString <> ''
    > SELECT WhereString = RIGHT( WhereString, LEN(
    > WhereString ) - 3 )
    > PRINT WhereString
    > SELECT id, username, password, firstname, lastname,
    > title, address1, address2, city, state, postalcode,
    > country, email, phone, fax,
    > organization, creationdate, modifydate
    > FROM Users
    > WHERE (SELECT WhereString )
    >
    > GO

    Dejan Sarka Guest

  3. #3

    Default Re: Stored-Procedure: Construct Where stmt dynamically, how?

    Thank you very much. I change my code according to you
    suggestion.

    What I originally want is to construct a query
    dynamically. So what I need do is to construct a whole
    query and exec it. That should also do the work. like this

    ....
    DECLARE SQLSTRING VARCHAR(50)
    SELECT SQLSTRING = 'SELECT * FROM USERS'
    SELECT SQLSTRING = SQLSTRING + 'dynamic WHERE stmt'
    ....
    EXEC (SQLSTRING)

    Thank you guys.

    Frank

    >-----Original Message-----
    >Using a dynamic where clause defeats the purpose of using
    a sp in the first
    >place, at least the performance aspects. Try this...
    >--Select user based on selection conditions
    >CREATE PROCEDURE dbo.UsersSelectBySearch
    > id nvarchar(50) = NULL ,
    > firstname nvarchar(200) = NULL ,
    > lastname nvarchar(200) = NULL ,
    > username nvarchar(200) = NULL
    >AS
    >
    >SELECT id, username, password, firstname, lastname,
    > title, address1, address2, city, state, postalcode,
    > country, email, phone, fax, organization, creationdate,
    > modifydate
    >FROM Users
    >WHERE (id IS NULL OR (id IS NULL AND Id = id ))
    >AND (firstname IS NULL OR (firstname IS NULL AND
    firstname = firstname ))
    >AND (lastname IS NULL OR (lastname IS NULL AND lastname
    = lastname ))
    >AND (username IS NULL OR (username IS NULL AND username
    = username ))
    >
    >
    >
    >....also, do you really need unicode? If not you're just
    wasting space.
    >
    >"Frank Jiang" <frank_z_jhotmail.com> wrote in message
    >news:38bf01c3405b$30a272a0$a601280aphx.gbl...
    >> Newbie question about T-SQL. Here is a my code.
    Basically
    >> I want to give user flexibility of inputting search
    >> criteria. If all parameters are empty, there will be no
    >> where stmt.
    >> I got syntax error on last where statement.
    >>
    >> Can anybody tell me is it going to work in stored-
    >> procedure.
    >>
    >> --Select user based on selection conditions
    >> CREATE PROCEDURE dbo.UsersSelectBySearch (
    >> id nvarchar(50),
    >> firstname nvarchar(200),
    >> lastname nvarchar(200),
    >> username nvarchar(200)
    >> )
    >> AS
    >>
    >> DECLARE WhereString nvarchar(500)
    >> SELECT WhereString = ''
    >> IF id <> ''
    >> SELECT WhereString = ' OR id = ' + id
    >> IF firstname <> ''
    >> SELECT WhereString = WhereString + ' OR firstname
    = '
    >> + firstname
    >> IF lastname <> ''
    >> SELECT WhereString = WhereString + ' OR lastname
    = '
    >> + lastname
    >> IF username <> ''
    >> SELECT WhereString = WhereString + ' OR username
    = '
    >> + username
    >> IF WhereString <> ''
    >> SELECT WhereString = RIGHT( WhereString, LEN(
    >> WhereString ) - 3 )
    >> PRINT WhereString
    >> SELECT id, username, password, firstname, lastname,
    >> title, address1, address2, city, state, postalcode,
    >> country, email, phone, fax,
    >> organization, creationdate, modifydate
    >> FROM Users
    >> WHERE (SELECT WhereString )
    >>
    >> GO
    >
    >
    >.
    >
    Frank Jiang Guest

  4. #4

    Default Re: Stored-Procedure: Construct Where stmt dynamically, how?

    Chris Spiess (nospam.chris.spiessgetauto.com) writes:
    > Using a dynamic where clause defeats the purpose of using a sp in the
    > first place, at least the performance aspects. Try this...
    > --Select user based on selection conditions
    > CREATE PROCEDURE dbo.UsersSelectBySearch
    > id nvarchar(50) = NULL ,
    > firstname nvarchar(200) = NULL ,
    > lastname nvarchar(200) = NULL ,
    > username nvarchar(200) = NULL
    > AS
    >
    > SELECT id, username, password, firstname, lastname,
    > title, address1, address2, city, state, postalcode,
    > country, email, phone, fax, organization, creationdate,
    > modifydate
    > FROM Users
    > WHERE (id IS NULL OR (id IS NULL AND Id = id ))
    > AND (firstname IS NULL OR (firstname IS NULL AND firstname = firstname ))
    > AND (lastname IS NULL OR (lastname IS NULL AND lastname = lastname ))
    > AND (username IS NULL OR (username IS NULL AND username = username ))
    Didn't you just say performance? This is OK if there are no indexes on
    any of the columns. But if there are, the indexes will not be used. Frank
    was right on the money when went for a dynamic solution.

    For a further discussion on the topic, I have a long article on
    [url]http://www.algonet.se/~sommar/dyn-search.html[/url].


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

Similar Threads

  1. Replies: 5
    Last Post: November 13th, 10:57 PM
  2. Replies: 0
    Last Post: November 11th, 12:51 AM
  3. -stmt mgmt issue
    By AVL in forum ASP.NET Building Controls
    Replies: 1
    Last Post: February 16th, 01:38 PM
  4. Replies: 0
    Last Post: December 8th, 09:32 AM
  5. Stored Outlines For Queries In PL/SQL Stored Procs
    By Salaam Yitbarek in forum Oracle Server
    Replies: 0
    Last Post: December 5th, 01:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139