Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Stored Procedure Syntax

Posted on 2011-10-08
5
Medium Priority
?
1,006 Views
Last Modified: 2012-05-12
Trying to learn from examples, some good, some bad....

Am I right in my thoughts in these clips of code?
...

1)  Declare @i int
     Declare @tabID int
     Set @i = 5
     While (@i <= 2) Begin
        Select * from tblOne
           where tabID = @i
        Set @i = @i + 1
     End

Code is correct, but will do nothing because @i is never less or ewual to 2.

2)  Declare table @Housing(theSize, integer IDENTITY(1,1))

Isn't IDENTITY used with a CREATE command and not the Declare??

3)  Select vcrOne, Count(*) as RecCnt
         from tblOne
          group by vcrOne
          with rollup

Doesn;t the rollup HAVE to specify the fields?

4)  Declare @counter int
     Set @counter = 5
     If @counter > 2 Begin
        Select * from tblOne
           where tabID = @i
         End
      Else
        Begin
        Update tblOne Set field1 = 'Oak'
        End

Syntax requires the @counter > 2  to be written as such (@counter > 2 ) ?
And, dont I need to include a THEN because of the ELSE?

So, the only valid one here is #3, if not mistaken

My friend comes up with weird examples.  Trying to really confuse me!

Any help appreciated
0
Comment
Question by:Bob Bender
  • 2
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
mmr159 earned 1000 total points
ID: 36937351
> Code is correct, but will do nothing because @i is never less or ewual to 2.

Correct.

> Isn't IDENTITY used with a CREATE command and not the Declare??

IDENTITY is used to specify an identity column in the creation of a table.  Yes, it is used with the CREATE table command but it can also be used in creating a table variable like in the example you posted.

> Doesn;t the rollup HAVE to specify the fields?

No, it does not.  FYI, the WITH ROLLUP/CUBE features will be depreciated.

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

> Syntax requires the @counter > 2  to be written as such (@counter > 2 ) ?

No, parenthesis are not required.

> And, dont I need to include a THEN because of the ELSE?

No.  SQL syntax does not use THEN in IF statements.

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

#4 also did not declare @i
0
 
LVL 5

Author Comment

by:Bob Bender
ID: 36937406

Oops my mistake on #4.  there is no WHERE Clause..., so the following would be correct:


     Declare @counter int
     Set @counter = 5
     If @counter > 2 Begin
        Select * from tblOne
        End
     Else
        Begin
        Update tblOne Set field1 = 'Oak'
        End


So, to recap???
a) Correct
b) Correct
c) Correct
d) Correct  (or is a "END IF" needed?


"Teacher" says one has incorrect syntax  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36937499
>>FYI, the WITH ROLLUP/CUBE features will be depreciated.<<
Do you have any supporting documentation for this?

0
 
LVL 7

Expert Comment

by:mmr159
ID: 36939759
Supporting doc is in my first post.  This one, scroll down.

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

IF/ELSE don't use END IF syntax.  They use BEGIN END.  They are optional when there is a single line of code to be executed.

IF 1 = 1
    blah

is the same as


IF 1 = 1
BEGIN
    blah
END
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36940348
I misuderstood, I thought you were suggesting that ROLLUP/CUBE had been deprecated.  I can see now that is not what you meant.

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question