Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Function or Procedure?

    Hi all,

    If I have a script that involved dropping and creating two tables and the desired output is a table with three fields should I be using a procedure or a multi-statement function? I am just delving into programmatic sql. I have spent some time trying to research on google, etc but can't figure it out. Thx again, you guys are the best!

    ddave

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This isn't part of your application code, is it?
    Regardless, a function would NOT be the way to go. I don't think you can issue DDL commands in a function anyway.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    No, I am writing it against the back end, SQL Server 2005 (sorry, forgot to mention the version). We have a very simple Access front end where we have been entering queries to call the data we want. We update the queries using this front end and we have written functions for the other reports we want to run. This particular query is different, however, in that it is the only that has two sub tables where I am rolling up data for further, subsequent processing.

    ddave

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, but I don't understand why you script needs to drop tables. Dropping tables should not be a part of either application or reporting processing.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    I am temporarily placing data into tables to process. I can use temp tables instead or I can delete from table1 and then insert into table1 instead. I currently am using an "if exists" clause, drop the table, and re-create it. That is just the way I was once taught.

    ddave

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You were once taught wrong.
    Don't be creating and dropping tables as part of your normal processing. Horrible idea.
    Use temporary tables instead. Or create scalable permanents tables.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    I don't think you can issue DDL commands in a function anyway.
    Correct - you can't even issue insert\ delete\ update (apart from to table variables)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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