Use of brackets - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Use of brackets

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-12-21 : 15:24:43

Is there any benefit to using brackets when naming stored procedures, if to take them out, you can just remove them? of there is some effect in having them.

[OTC].[THS_GET_DECLINES_second_value]

I am using the sp in c# code.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-21 : 15:33:24

You don't need them for your example, but yes there is a reason. Take this example: [Some Column]. While you can have spaces (please don't!) in your column names, the parser won't allow it unless you put the square brackets around it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 15:34:13

I'd say it's good practice

It let's you name DB Objects that are pretty horrendous..which is not good practice

But if you inherit stuff or generate code..use'em

CREATE TABLE [dbo].[This is a pretty messed up table %$&*^$ name] (
col1 int
, col2 char(1)
)

Command(s) completed successfully.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-21 : 15:50:58

Or tables (or columns) named things like Transaction, Table, Procedure, etc...

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 15:53:10

quote:
Originally posted by GilaMonster

Or tables (or columns) named things like Transaction, Table, Procedure, etc...

--
Gail Shaw
SQL Server MVP

Which are also very bad..any reserved word is a bad db object name

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-12-21 : 15:57:06

I found out why, we also have Oracle customers and the stored procedures have to try to have them work in both. so they program as much as possible to just use each one without having to change anything. Silly situation.

Apparently Oracle doesn't accept brackets.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 16:12:28

betcha it accepts double quotes

CREATE TABLE "dbo"."This is a pretty messed up table %$&*^$ name" (
"col1" int
,"col2" char(1)
)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 16:13:53

However...I would think it is IMPOSSIBLE To have a procedure that would be the same on both platforms

Different Animals

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-21 : 16:22:50

It's possible. There's a common area of the ANSI standards that both implement. It's a very minimal set but it is possible to use (that's what Celko's often harping on about)

--
Gail Shaw
SQL Server MVP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-21 : 16:29:19

Just for those that may not know, it's called a Quoted Identifier.

[url]http://msdn.microsoft.com/en-us/library/aa259228(v=sql.80).aspx[/url]
EDIT: Humm, doesn't seem to like my link..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-21 : 16:33:10

quote:
Originally posted by X002548

However...I would think it is IMPOSSIBLE To have a procedure that would be the same on both platforms

Different Animals

If you write ANSI SQL, you'd probably be surprised how easy you can set up a database that'll work on almost any RBDMS out there (with minimal tweaks of course).

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-21 : 16:36:58

have you worked with Oracle or DB2?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-21 : 16:48:39

Not extensively. I worked at a place a few years back and we had made it a point to do things in an ANSI way as much as possible. When we were investigating other platforms we decided to try some migrations for fun. It literally took a day (each) to convert over to other platforms: Oracle, DB2, Postgrse, can't remember if we did MySql or not.

Granted we didn't get into tuning the DB, but making it work on other platforms was not very hard.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-22 : 06:12:55

Yes, and MySQL. Also had the pleasure of porting code between them. Far from fun.

There are apps (and I've worked with them) that can work on Oracle and SQL Server with just changes in how the DB is called, the queries are identical (typically no procedures used). It's ugly as hell and tends to work very slowly, but it works.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 09:24:07

And you have to convert all of the Queries in to Cursors

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-22 : 10:43:01

Err, no.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 10:53:22

....and why not?

how do you code packages and procedures when be called by an application, or even when you create an extract?

Spool?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-22 : 11:29:03

OK..to clarify..cursors are needed to return a result set...

other DML operations do not

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources