Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: table as variable name in stored proc

    I'm trying to pass the name of a table into a stored procedure as below:

    CREATE PROCEDURE sp_Forum_Select_Topics
    (
    @DiscussionBoard varchar(50)
    )
    AS

    SELECT l.ParentMessage, l.ID, l.ThreadParent, l.Comments, l.Topic, l.ReplyCount, l.LastThreadPost, l.Views, l.AuthorID, l.Closed, l.DatePosted, lc.UserName, lc.Email, lc.AuthorID

    FROM @DiscussionBoard

    as l left join Education_Discussion_Board_LoginDetails as lc on l.AuthorID = lc.AuthorID WHERE ParentMessage=0 ORDER BY DatePosted Desc


    but SQLserver7 throws a syntax error. Anybody know a way around this problem?

    Cheers,
    Gram

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Try this...

    Code:
    CREATE PROCEDURE sp_Forum_Select_Topics(
    @DiscussionBoard varchar(50),
    @Debug           bit = 0)
    AS
    declare @TSQL varchar(1000)
    
    set @TSQL = 'SELECT l.ParentMessage, l.ID, l.ThreadParent, l.Comments, l.Topic, l.ReplyCount, l.LastThreadPost ' +
                     ', l.Views, l.AuthorID, l.Closed, l.DatePosted, lc.UserName, lc.Email, lc.AuthorID ' +
                  'FROM ' + @DiscussionBoard + ' as l ' +
                  'left join Education_Discussion_Board_LoginDetails as lc on l.AuthorID = lc.AuthorID ' +
                 'WHERE ParentMessage=0 ' +
                 'ORDER BY DatePosted Desc'
    
    if (@Debug = 1)
      select @TSQL
    else
      execute(@TSQL)
    
    return 0
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Posts
    3

    slanchevar

    Paul Young,

    Many thanks for your response, it works a treat!

    I'm a bit new to all this, what do the following lines do?

    ----------------------------------------------------------------

    if (@Debug = 1)
    select @TSQL
    else
    execute(@TSQL)
    ---------------------------------------------------------------


    will @Debug not always = 0?

    Thanks again and Happy New Year!
    Gram

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    When I write an sp that uses the Execute(@Variable) format I include a way to print out the SQL that will be executed. In fact most of my stored procedures have this as a was of getting diagnostic info when things don't work right.

    If you run the sp as you normally would but include a 1 for the second parameter or ", @Debug = 1" then the sql will not be executed but printed out.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Jan 2003
    Posts
    3
    Cheers Paul. Many have eyes but do not see....

Posting Permissions

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