SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MS SQL 2000 Syntax

    I've been using a lot of online resources and forums to help me learn how to write sprocs. I noticed some of them use the BEGIN and END keywords and others don't. Is there any reason I should always use them, or is it just the preference of the programmer?

    Example:
    Code:
    CREATE PROCEDURE Customer_Select
     @CustomerID nchar(15)
    AS
     BEGIN
      SELECT * FROM Customer WHERE CustomerID = @CustomerID
     END
    Would there be any difference if I was to remove the BEGIN and END keywords?

    Secondly, are there any good online resources I can trust for MS SQL? I don't have the extra money to buy a book on SQL right now, and I want to learn more about the Syntax. I also would like to use to learn indexes.

    Once I create an index for example IDX_Customer with FirstName, LastName, CustomerID, Telephone, Company, EmailAddress. Do I need to call that index some how, or can I just use the following sproc and it will automatically use the index.
    Code:
    CREATE PROCEDURE Customer_Select
    	/* initializing to NULL indicates this parameter is optional */
    	@customerID nchar(15) = NULL,
    	@companyName nvarchar(60) = NULL,
    	@firstName nvarchar(30) = NULL,
    	@lastName nvarchar(30)= NULL,
    	@telephone nvarchar(24) = NULL,
    	@emailAddress nvarchar(60) = NULL
     AS
    	SELECT * FROM Customer WHERE
    		CustomerID = @customerID OR
    		CompanyName = @companyName OR
    		FirstName = @firstName OR
    		LastName = @lastName OR
    		Telephone1 = @telephone OR
    		EmailAddress = @emailAddress
    GO
    Thanks

  2. #2
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess, begin and end are used for grouping and are optional. So if you have a single statement, use the method (with them, or without them) as you prefer.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So its similar to tokens in C#. If you create a block with multiple statements, you have to provide the opening and closing tokens. If the block only has a single statement you don't need to use the tokens.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by blacklazy View Post
    Secondly, are there any good online resources I can trust for MS SQL?
    you can trust microsoft, right?

    http://msdn.microsoft.com/library/en...reate_4hk5.asp
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Books online

    Syntax
    IF Boolean_expression
    { sql_statement | statement_block }
    [ ELSE
    { sql_statement | statement_block } ]

    Arguments
    Boolean_expression

    Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.

    {sql_statement | statement_block}

    Is any Transact-SQL statement or statement grouping as defined with a statement block. Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement. To define a statement block, use the control-of-flow keywords BEGIN and END. CREATE TABLE or SELECT INTO statements must refer to the same table name if the CREATE TABLE or SELECT INTO statements are used in both the IF and ELSE areas of the IF...ELSE block.

    Remarks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •