Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: How to write an if else in 2000

    I am running SQL Server 2000 and need to know how to write an IF else statement with a variable for a table name. I am constantly getting errors when I attempt this feat. Does someone here have an example or can fix my code to working format.

    Code:
    Use [TestDatabase]
    Go
    CREATE PROCEDURE UserInputAsTable
    (
    @TableName
    )
    AS
    BEGIN
    IF (LEFT(' + @TableName + ', 2) = 'tb')
    SELECT * FROM ' + @TableName + ' WHERE ID > 4500
    ELSE
    SELECT * FROM ' + @TableName + ' WHERE ID <= 4499
    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Fist of all, this is a VERY DANGEROUS idea known as dynamic SQL. Google "SQL Injection" for just a few of the ways that this can cause problems for your application.

    If you really do think you can prevent all of the problems that Dynamic SQL can cause, it can be done in SQL 2000 with some restrictions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    I understand the risks of using Dynamic SQL and how it opens you up for SQL Injection.

    This Stored Procedure will only be run locally on the server

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, as long as you understand the risks:
    Code:
    Use [TestDatabase]
    Go
    CREATE PROCEDURE UserInputAsTable
    (
       @TableName   sysname
    )
    AS
    BEGIN
    IF (LEFT(@TableName, 2) = 'tb')
       EXECUTE ('SELECT * FROM ', @TableName, ' WHERE ID > 4500')
    ELSE
       EXECUTE ('SELECT * FROM ', @TableName, ' WHERE ID <= 4499')
    END
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Thank you kindly. I had to add one more parameter of
    Code:
    AND city LIKE '%Re'
    And I am getting Incorrect syntax near '%'

    Can you help remedy?

  6. #6
    Join Date
    Feb 2012
    Posts
    188
    Please disregard my above post, I just had to use double quotes.

    Thank you for the assistance and prompt response

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    How would I do the same with an update query?
    UPDATE ' + @TableName + ' SET DateReceived = GetDate()

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would do an UPDATE the exact same way as the SELECT statement.
    Code:
    EXECUTE ('UPDATE ', @TableName, ' SET DateReceived = GetDate()')
    Be forewarned, this is still a REALLY bad idea. You can do it, but it can go horribly wrong in so many different ways... SQL Injection is only the most spectacular.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Red face So many fundamental design flaws

    How would I do the same with an update query?
    An UPDATE is a statement and not a query.

    UPDATE ' + @TableName + ' SET DateReceived = GetDate()
    We use CURRENT_TIMESTAMP and not the old 1970's Sybase getdate() in T-SQL for the last few years.

  10. #10
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    What you have is called an "Automobiles, Squids and Lady Gaga" code. In violation of the Law of Identity (the foundation of Western logic), you have a magical model where automobiles can be just like squids! Oh, you might want to learn ISO-11179 and use "receipt_date" as the column name.

    Your code is dangerous, as Pat has told you, it is out of date, it violates industry standards, and it violates good coding practices. Get a copy of SQL PROGRAMMING STYLE to get started.

  11. #11
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Celko View Post
    Get a copy of SQL PROGRAMMING STYLE to get started.
    Are you just trying to promote your own book :P

    JK! I actually just ordered a copy as I have heard from you, as well as 2 other people that it is a great book to learn SQL. One question about your book though, is I read it is not for beginners. I have only been working with (as I am sure most of you can tell) SQL for maybe 6 months and in that time only writing stored procedures for 2 if that...is this a good starting point for myself?
    Last edited by jo15765; 04-03-13 at 20:55.

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Are you just trying to promote your own book :P
    You say it like it is a bad thing

    One question about your book though, is I read it is not for beginners. I have only been working with (as I am sure most of you can tell) SQL for maybe 6 months and in that time only writing stored procedures for 2 if that...is this a good starting point for myself?
    The best book for an absolute noob I know is the MANGA GUIDE TO DATABASE. Seriously. I would use SQL STYLE with another book ..

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    An UPDATE is a statement and not a query.
    We use CURRENT_TIMESTAMP and not the old 1970's Sybase getdate() in T-SQL for the last few years.
    GETDATE() is not only fully supported within T_SQL, it is apparently preferred:
    SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server | SQL Server Journey with SQL Authority
    SQL Server will actually convert CURRENT_TIMESTAMP to getdate()...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Sybase Legacy = family curse

    There is some talk about how to get T-SQL up to ANSI/ISO Standard temporal, but it will be slow. The old Sybase/UNIX getdate() is spread all over place and so is square brackets, ISNULL() and a few other things.

    Remember what a pain *= was before "[LEFT | RIGHT | FULL] OUTER JOIN" syntax? And how long it took to get rid of it?

    My bet is that when we get CURRENT_DATE, we will see automatic getdate() replaced with automatic CURRENT_TIMESTAMP in generated code. The real improvement will be with the INTERVAL datatypes and the ANSI/ISO Standard SQL temporal math; it is so much better than the old Sybase functions, but it will be a major re-wrie.

  15. #15
    Join Date
    Feb 2012
    Posts
    188
    How would I add an additional if statement?
    Code:
    Use [TestDatabase]
    Go
    CREATE PROCEDURE UserInputAsTable
    (
       @TableName   sysname
    )
    AS
    BEGIN
    IF (LEFT(@TableName, 2) = 'tb')
       EXECUTE ('SELECT * FROM ', @TableName, ' WHERE ID > 4500')
    ELSE
    --Add in this additional IF
    If (Left(@TableName, 2) 'rt')
       EXECUTE ('SELECT * FROM ', @TableName, ' WHERE ID IS NOT NULL')
    ELSE
       EXECUTE ('SELECT * FROM ', @TableName, ' WHERE ID <= 4499')
    END
    Last edited by jo15765; 04-05-13 at 15:15. Reason: forgot code tags

Posting Permissions

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