Professional SQL Server™ 2005 Programming

10.1. Script Basics

A script technically isn't a script until you store it in a file where it can be pulled up and reused. SQL scripts are stored as text files. SQL Server Management Studio provides many tools to help you with your script writing, but you can do the writing in any text editor (keep in mind, however, that in order to actually test your script, it's going to have to be something that can connect). Indeed, I frequently use a highly robust text editor for its ability to handle real expressions and other text-editing features that Management Studio, and even Visual Studio, will never have.

Scripts are usually treated as a unit. That is, you are normally executing the entire scriptor nothing at all. They can make use of both system functions and local variables. As an example, let's look at a simple script that could be used to INSERT order records into a typical order header and order detail table scenario:

USE SomeDatabase

DECLARE @Ident int

INSERT INTO Orders
(CustomerID,OrderDate)
VALUES
(25, DATEADD(day,-1,GETDATE()))  -- this always sets the OrderDate to yesterday

SELECT @Ident = @@IDENTITY

INSERT INTO Details
(OrderID, ProductID, UnitPrice, Quantity)
VALUES
(@Ident, 1, 50, 25)

SELECT 'The OrderID of the INSERTed row is ' + CONVERT(varchar(8),@Ident)

We have six distinct commands working here, covering a range of different things that we might do in a script. We're using both system functions and local variables, the USE statement, INSERT statements, and both assignment ...