Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Executing prepared statements etc.

    Hey guys,

    Currently I have two procedures:

    1) GetAuthors()
    2) GetAuthors( @id INT )

    The 1st returns ALL authors (SELECT * FROM authors) whereas the second returns an author based on the id, so "SELECT * FROM authors WHERE author_id = @id)

    Currently, I have these as two separate procedures, but I want to combine them into 1 procedure using dynamic SQL (or anything else that would work).

    You'll notice the only difference is the addition of the WHERE clause in the second procedure.

    How can I do this?

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    How about something like

    Code:
    Declare procedure GetAuthors
    @id int = null
    AS
    BEGIN
        if @id is null 
            select * from authors
        else 
            select * from authors where id = @id
    END

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Great, thanks!

    So that means if I don't pass any value for @id, then it will default to null?

    Out of curiosity is there any means of doing something like:
    Code:
    statement = "SEELCT * FROM authors"
    IF @id IS NOT NULL
        statement = statement + " WHERE id = @id"
    
    EXECUTE staement

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by dbguyfh
    Great, thanks!

    So that means if I don't pass any value for @id, then it will default to null?
    Right ... because you told it to default to null

    Quote Originally Posted by dbguyfh
    Out of curiosity is there any means of doing something like:
    Code:
    statement = "SEELCT * FROM authors"
    IF @id IS NOT NULL
        statement = statement + " WHERE id = @id"
    
    EXECUTE staement
    You COULD (minus typos) ..... but why would you want to? Make your case for dynamic SQL.

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    You COULD (minus typos) ..... but why would you want to? Make your case for dynamic SQL.
    Ahh, so i take it that method would be a lot slower than just doing an IF...THEN?

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I prefer this method
    Code:
    SELECT comma
         , separated
         , list
         , of
         , columns
    FROM   authors
    WHERE (id = @id
    OR     @id IS NULL)
    P.S. don't be using SELECT * now young man

    P.P.S. please ask if you don't understand the code I've posted
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

  8. #8
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by georgev
    I prefer this method
    Code:
    SELECT comma
         , separated
         , list
         , of
         , columns
    FROM   authors
    WHERE (id = @id
    OR     @id IS NULL)
    P.S. don't be using SELECT * now young man

    P.P.S. please ask if you don't understand the code I've posted
    haha... why shouldn't I use SELECT *? And yeah, typically I do use SELECT (comma, list) becuase most of the time, I only need a subset of the data

    Also what does the following mean?
    Code:
    WHERE (id = @id
    OR     @id IS NULL)
    Will it take case of both cases without needing an IF...THEN clause?

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    Thanks for the links! That first one aligns exactly with what I was looking for

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Make sure you read it! The idea is that by the time you have read it you will dismiss the dynamic SQL option!

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dbguyfh
    haha... why shouldn't I use SELECT *?
    If you have a schema change down the line, do you want to return the new rows to your front-end needlessly? State the exact columns you want for all production queries (you only have to do it once, there are even cheaty ways of getting the list of columns from the sys schema!).
    Quote Originally Posted by dbguyfh
    Also what does the following mean?
    Code:
    WHERE (id = @id
    OR     @id IS NULL)
    Will it take case of both cases without needing an IF...THEN clause?
    The code is a common way of returning all reults from a table when a parameter is missing.

    If @id has a value, you get a filtered result set based on the id field. However, if the parameter is not passed or is a NULL value, then the second clause (the OR clause) returns true (NULL IS NULL!) and as such returns all records.
    Code:
    DECLARE @id int
    SET @id = 1
    
    SELECT id
    FROM   authors
    WHERE (id = @id
    OR    @id IS NULL)
    
    SET @id = NULL
    
    SELECT id
    FROM   authors
    WHERE (id = @id
    OR    @id IS NULL)
    WHERE @id IS
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    P.S. don't be using SELECT * now young man
    LOLZ!

  13. #13
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by georgev
    I prefer this method
    Code:
    SELECT comma
         , separated
         , list
         , of
         , columns
    FROM   authors
    WHERE (id = @id
    OR     @id IS NULL)
    I wouldn't recommend this on a large table. You will end up with an index scan, which is what you want if @id is null. But you want an index seek if @id is not null.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dbguyfh
    haha... why shouldn't I use SELECT *?
    If you have a schema change down the line, do you want to return the new rows to your front-end needlessly? State the exact columns you want for all production queries (you only have to do it once, there are even cheaty ways of getting the list of columns from the sys schema!).
    Quote Originally Posted by dbguyfh
    Also what does the following mean?
    Code:
    WHERE (id = @id
    OR     @id IS NULL)
    Will it take case of both cases without needing an IF...THEN clause?
    The code is a common way of returning all reults from a table when a parameter is missing.

    If @id has a value, you get a filtered result set based on the id field. However, if the parameter is not passed or is a NULL value, then the second clause (the OR clause) returns true (NULL IS NULL!) and as such returns all records.
    Code:
    DECLARE @id int
    SET @id = 1
    
    SELECT id
    FROM   authors
    WHERE (id = @id
    OR    @id IS NULL)
    
    SET @id = NULL
    
    SELECT id
    FROM   authors
    WHERE (id = @id
    OR    @id IS NULL)
    WHERE @id IS
    George
    Home | Blog

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by cascred
    I wouldn't recommend this on a large table. You will end up with an index scan, which is what you want if @id is null. But you want an index seek if @id is not null.
    Dear me, I didn't even consider that; was taught that this was the best method!

    I've gone away and tested this myself on a 0.5m row table and you're right, I get a scan either way!

    +1 to you
    George
    Home | Blog

Posting Permissions

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