Re: Stored Procedures, Dynamic SQL or static SQL and testing.

On Saturday, March 26, 2011, Richard Quadling wrote; > What preferences do you all have when using SQL. Stored Procedures or > PHP generated dynamic SQL or parameterized static SQL. > Whichever you use how do you test them? Do you use unit testing in > your PHP code? If so, do you also have unit code testing for the SQL > statements (I suppose this mainly relates to SPs). > Does using a different RDBMS make a difference to the SQL coding style > you'd use? Personally, I prefer to use dynamic SQL and this doesn't really change no matter which RDBMS or programming language I use. I prefer doing it that way because it's easier to see what I've done (I don't need to interrogate the database to get procedure definitions, which aren't universally available) and I find it easier to debug because echoing "<p>$query</p>" lets me see what I'm passing to the DB. I don't usually test SQL separately but as part of the application. However, when debugging I often copy the echoed query and then paste it into the CLI or other facility to check it returns what I expect. With all that said, sometimes I have no option but use a stored procedure. An example is where I'm inserting a new row and I need it's PK value (e.g. LAST_INSERT_ID() in MySQL). HTH, -- Geoff