On Fri, May 25, 2012 at 9:43 AM, MB Software Solutions, LLC <[email protected]> wrote: > On 5/24/2012 11:48 AM, Stephen Russell wrote: >> Views are to set in place the obnoxious code you don't want to retype >> like below: >> >> >> SELECT CONVERT(datetime, MAX(CASE QAVariable WHEN 'DATE:D' THEN >> DataValue END)) AS [DATE:D], CONVERT(datetime, >> MAX(CASE QAVariable WHEN 'TIME:T' THEN DataValue >> END)) AS [TIME:T], CONVERT(nvarchar(250), MAX(CASE QAVariable WHEN >> 'LOCATION:A' THEN DataValue END)) > <horrible SQL snipped> >> DATASETID, RowGUID >> FROM dbo.v_QISDataValue >> WHERE (DataSetID = 1) >> GROUP BY RowGUID > > > Thanks a lot, Steve. That's 20 seconds of my life I won't get back. > Glad I don't work there!!! I do wonder about the advantage of the view > over a SP though.... -----------------------
That is why you would use a view. You get repeat query over and over and all you do is select * from vw_myView where .... That view is taking a normalized dataset and making a flat file or Excel sheet from it. They use that in the engineering side of the house. SPs are great. It allows you do do some high end work on the best sever for it, instead of dragging a ton of rows to a local pc to do the same. My tables are in the 20-30 mill rows for engineering and 200 mill for accounting data. Now when you have repeat demand for a report or a spreadsheet than the SP is the way to go. That demand usually has consistent params and you can tweak inside of the SP to fit the actual params that you receive. As a heads up you try to NEVER generate dynamic code on the server. Your planning in an SP should handle the various ways they are going to ask and how you are going to do it. You just use a set of IF statements to run the proper query. -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/cajidmyjfaakwyir0bhswnyb_rs7d107drxxhfk31erjg6r0...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
