Creating MS SQL Temp Tables in SQLSRV PDO in PHP

Regardless of the fact that your senior programmers do not like them, I often make use of stored procedures where it makes sense to do so.

From my first hand experience most sql activities, that are not a single select, update, insert, or delete statement, can benefit from being in a stored proc. As with any tool in the toolbox, it depends on what you are doing. When I build temp tables, it is done within a stored proc because it is a complex action and very often has a large volume of sql involved.

In your example, checking object existence, dropping it, building a new db object, adding data, and then pulling that data out is, imho, a series actions that would strongly suggest a stored proc. Others may argue for some layer of data access objects. Regardless, you do WANT to encapsulate this to keep your product maintainable. Stored procs do that well. They also require less network traffic than sending the component sql statements one at a time. They will generally run faster than in-line sql too and not just because of network latency, one of the many things a database does is generate an execution plan for any sql it runs and while prepared statements help, stored procedures help more. By far, the biggest complaint people have about them, from what I have seen, is sql not as robust a programming language as things like java or C# or Objective C or any number of other languages. There are other things that people list as cons but imo being able to do what you need to do with ease and efficiency is the top one.

Again, they are one of the tools in the toolbox and, like any tool, there is a time and place to use them. But don't unilaterally dismiss them, that would be a mistake. Personally, over the years I have gotten very good a using them and as my abilities and knowledge of sql has increased, I've found even better uses for them. Learn about all the tools.

Have a look at the t-sql doc for Creating Stored Procedures If you want more on pros and cons of stored procs then the internet is full of that, among other things. Here are a few links.

Determine when to use stored procedures vs. SQL in the code

Why use Stored Procedures?

Stored Procedures Are Evil

Google "stored procedures vs inline sql" and you will get lot more. Just remember, the right tool for the right job.

Use a stored procedure, it is not that hard:

CREATE PROC updateQuestion ()
 AS
 BEGIN
    BEGIN TRANSACTION

        IF Object_id('#ViewQuestionComments', 'U') IS NOT NULL DROP TABLE #viewquestioncomments;
        CREATE TABLE #viewquestioncomments 
                     ( 
                                  commentid INT DEFAULT ((0)), 
                                  userid    INT DEFAULT ((0)), 
                                  comment   VARCHAR(max) DEFAULT '', 
                                  datemodified SMALLDATETIME, 
                                  username NVARCHAR(200) DEFAULT '', 
                                  points   INT DEFAULT ((0)) 
                     );
                     INSERT INTO #viewquestioncomments 
                    ( 
                                commentid, 
                                userid, 
                                comment, 
                                datemodified 
                    ) 
        SELECT id, 
               userid, 
               comment, 
               datemodified 
        FROM   hanoncs_askme.hanoncs_hanoncs.comments 
        WHERE  postid=? 
        AND    status=1;
        UPDATE #viewquestioncomments 
        SET       username = m.username 
        FROM      #viewquestioncomments c 
        LEFT JOIN hanoncs_securelogin.hanoncs_hanoncs.members m 
        ON        m.id = c.userid;
        UPDATE #viewquestioncomments 
        SET    points = 
               ( 
                      SELECT Count(*) 
                      FROM   hanoncs_askme.hanoncs_hanoncs.commentvotes 
                      WHERE  postid=c.commentid) 
        FROM   #viewquestioncomments c;
        SELECT * 
        FROM   #viewquestioncomments";

     IF @@ERROR != 0
        ROLLBACK TRANSACTION
        ELSE
            COMMIT TRANSACTION
 END

GO

Usage:

$stmt= $PDO->prepare('EXEC updateQuestion');
$stmt->execute();