Predicate or key word all in uppercase?

Hi,

What is the dowside of not using all uppercase for predicates and key words?

I cannot find to see a problem beside adhering to a clean coding convention. After all I already have color coding so what would be the uppercase for?

Same question for the semicolon ; at the end of a sql block. Is that real necessary not to get in trouble sometime down the road or is it a non-issue. I find like I am now a C# guys if I use these ;

Just curious, I find tedious to switch from all upper case to normal case all the time. And not forget to type the ;

Thanks,

Philippe

Case doesn't matter for keywords. It is good to establish some consistent coding style in your organization though so it is easier to read the code. As for semi-colons, it will be good to use it in new code. There are also some new constructs that require use of semi-colon in previous statement to resolve ambiguities. And in a future version of SQL Server, we may require the use of semi-colons as statement separators.|||Thank you, got it.
About GO should I have "Some query" ; GO
or is Go Gone :-)
I never saw any advantage in using GO
it looks like it is implicit.
I suspect the answer is along the same line.|||GO is not a TSQL or SQL command. It is just a batch separator token for the client. You can configure it to be something else also if you want (like / or $$ etc).|||

Umachandar Jayachandran - MS wrote:

In a future version of SQL Server, we may require the use of semi-colons as statement separators .

Well, this seems frightening, I can't think of how many organizations would be in the impossibility to upgrade to such version without going through a major review of scores of existing (distributed) code. that seems very unlikely to happen. Lot of bugs ahead with that one.
I fully agree for brand new constructs but the day you can flip the switch to make this mandatory for every constructs seems very far away.
If it is 5 years from now, I am better off starting immediately in each piece of code I ever touch.
If it is 10 years from now, I can just start doing it for every new piece of code I create.
The legacy stuff should have vanished by then. At least for my application which has a somewhat short life cycle.
Philippe|||Our deprecation policy is fairly strict right now in the sense that we make announcement first in version N, then warning in version N + 1 and then deprecate in version N + 2. So you will have really multiple releases to transition and convert your code. Please take a look at the deprecation topics in say SQL Server 2000 and SQL Server 2005 for some examples on features that we announced for deprecation or removed and so on. Backward compatibility is a huge deal due to the installed application base for SQL Server and every release we go to great lengths to maintain that. The backward compat setting for databases are also a step towards that which allows you to run most code from previous version without changes. There are exceptions but those are considered breaking changes that you will have to fix in your application after/before upgrading. Lastly if you look at most of the examples in SQL Server 2005 Books Online or modules in Adventureworks db, you can see that the code uses statement terminators to suggest good coding practice.

Positioning of Stored Procedures

Hi all,

I have been facing this dilemma since when I started coding in asp.net 2.0. I can have Data Access Layer wherein I can write stored procedures to access the data from database. I can create data access object, data table and all other stuff. Also I can create stored procedure in SQL 2000 server, and then access them from the Data Access layer.

Which of the two method is preferable, and why. i have been searching net for answers to this question since long, but could not find anything.

All answers can contribute may be little but invaluable knowledge.

Thanks.

If security is critical, it's best to use stored procedures always because it lowers the attackable area of your database. I think this is what you are asking.

|||

I wanted to know, which is better:

1) Creating stored procedures in SQL Server 2000 and calling them in the Data access Layer, or may be in the code behind straight away.

2) Creating Table Adapters in Data Access Layer, and creating Table Adapter queries and accessing database or may be stored procedures within the Data Access Layer.

I have been informed that if you create the Table Adapter queries, they are equally secure as stored procedures; though i am not pretty confident about it.

Thanks again.

|||

Tell the truth, the issue is depending on your situation.

If you can connect database and your database permittion contol well, you need to do that on db.

if not, don't do that.

|||

Read this an argument against using SP. This will clarify your question as well

http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html

Hope that helps

|||

hello.

well, to be honest, sps aren't really something i'd advocate for crud behavior. in my opinion, using parametrized sql is the way to go. the performance/security bla bla that's has been used for several years is a myth and there are some posts out there that just show it. for instance, there's an old discussion between frans bouma and rob howard that started with a post from rob and a very well answer by frans. i'm putting only frans' post here since it is linked to rob's post.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

having said that, i'm not saying that there really isn't a place for sps; just saying that kmost of the time the argument for using them are pure myths!

|||

Hi,

I feel creating stored procedures in SQL Server 2000 and calling them in the Data access Layer is better choice. The book Titled :

"Database programming using C#, VB 2005 and SQL Server 2005", Chapter 10:Developing Components for three-tier applications explains this concept.

Let us say we have developed an three-tier application using SQL server 2000. In future, the same application should able to access/insert to Oracle database. In this situation, writing stored procedures at the server level is better.

I will find out further info on this matter.

|||

With the technologies ASP.NET 2 provide, you r always free to choose the way you like (depending upon your handy side). With the nearly the same amount of effort or even less you can still handle the shift to Oracle database.

But that said, your approach of choosing store procedure suppose to be a little faster in most cases.

|||

hello.

ask4jm:

But that said, your approach of choosing store procedure suppose to be a little faster in most cases

again, this is a known myth. read frans' post to see what i'm speaking about.

|||

You r absolutely rite Luis. Unless the database developers wants to give the data through specific routines hiding rest of the infra, store procedures can be completely avoided.

|||

db2Command cmd= db2Commant();

cmd.Connection=con;
param = new DB2Parameter("@.ClientId", DB2Type.Decimal, 8);
((DbCommand)base.dbSelectCommand[0]).Parameters.Add(param);

//Insert Command and parameters
string sqlInserCommand = "ProcCon";

base.dbInsertCommand = new DbCommand[1];
base.dbInsertCommand[0] = new DB2Command(sqlInserCommand);

param = new DB2Parameter("@.Name", DB2Type.VarChar, 100, "ClientName");
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);

param = new DB2Parameter("@.Cid", DB2Type.Int);
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);