Parameterization: @parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT] : Parameter « Procedure Function « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Procedure Function
3. Parameter

10>
11>
12> CREATE TABLE Shippers (
13>     ShipperID int NOT NULL ,
14>     CompanyName nvarchar (40) NOT NULL ,
15>     Phone nvarchar (24) NULL
16> )
17> GO
1>
2>
3> INSERT Shippers VALUES(1,'Express','(503) 555-9831')
4> INSERT Shippers VALUES(2,'Package','(503) 555-3199')
5> INSERT Shippers VALUES(3,'Shipping','(503) 555-9931')
6> go

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>    CREATE PROC spInsertShipper
2>       @CompanyName   nvarchar(40),
3>       @Phone         nvarchar(24)
4>    AS
5>       INSERT INTO Shippers (CompanyName,Phone )
6>       VALUES
7>          (@CompanyName, @Phone)
8>
9>    GO
1>
2>    EXEC spInsertShipper 'Speedy Shippers, Inc.', '(503) 555-5566'
3>    GO
Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Procedure spInsertShipper, Line 5
Cannot insert the value NULL into column 'ShipperID', table 'master.dbo.Shippers'; column does not allow nulls. INSERT fails.
The statement has been terminated.
1>
2>    drop PROC spInsertShipper;
3>    drop table Shippers;
4>    GO