Arthur Fuller
artful at rogers.com
Thu Jun 24 14:52:36 CDT 2004
I think I wrote it in an earlier message, but lest you missed it... Dynamic SQL is NOT the way to go once you port to SQL Server. You need to rethink all these parts of your app and replace them with sprocs that can handle all the parms you might pass to your SQL-construction code. I have a simple and foolproof way of translating this stuff into good SQL, but it's a forthcoming tip at SQL Tips at builder.com, so if I tell you now I'll have to kill you. Sorry. But I will give you a hint: Test the parms against themselves, i.e. WHERE ColumnOfInterest = @parm1 OR @parm1 IS NULL. That's all I can tell you before the piece is published. Sorry if that's not enough. Arthur -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Andy Lacey Sent: Thursday, June 10, 2004 4:28 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Difference between views and queries But, Francisco, if I was porting to SQL Server my Access app which builds SELECT statements dynamically all of the time for many and various situations are you saying I couldn't, or shouldn't or something? -- Andy Lacey http://www.minstersystems.co.uk > -----Original Message----- > From: dba-sqlserver-bounces at databaseadvisors.com > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf > Of Francisco H Tapia > Sent: 10 June 2004 20:58 > To: dba-sqlserver at databaseadvisors.com > Subject: Re: [dba-SQLServer] Difference between views and queries > > > jwcolby wrote On 6/10/2004 9:33 AM: > > >Can anyone explain the difference between a view and a query? Views > >use a query, plus the view keyword. I have a couple of books that I > >have read the chapter on Views, but I so far haven't managed > to "get" > >why you wouldn't just use the query itself instead of > turning it into a > >view. > > > > > A query is a request for an Access Database, however for Sql > Server you > would either use a View or Stored Procedure to return the data you > wanted... you are also able to use dynamic SQL to retrieve the > information you need. ANY request given to the SQL Server engine is > managed by the engine, unless you are running Remote servers (iirc). > > In Sql Server, it is TABOO, nay, GENERALLY bad practice to > use dynamic > sql because of the implication of SQL INJECTION attacks, this poses a > "real" security threat to your database. and your server. > > another reason to use a VIEW over dynamic sql is that it is > pre-optimized by the SQL Server Engine and thus runs faster and more > efficient. Additionally if you use Dynamic SQL then your individual > users who access the server will need EXPLICIT "SELECT" > permissions by > you, which is another 'bad' practice. In SQL Server you make data > available to your users via VIEWs and Stored Procedures or some other > secure way in order to protect your tables and it's data. > > ya get wot I mean? > > -- > -Francisco > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com