USE Northwind;
GO
CREATE
PROCEDURE dbo.ListEmployees
AS
SET
NOCOUNT ON
SELECT EmployeeID, LastName, FirstNameFROM dbo.Employees;
GO
EXECUTE
dbo.ListEmployees;
EXEC
dbo.ListEmployees;
GO
CREATE
@City
PROC dbo.ListEmployeesByCityvarchar(25)
AS
SET
NOCOUNT ON
SELECT LastName, FirstName, CityFROM dbo.EmployeesWHERE City = @City;
GO
EXEC
dbo.ListEmployeesByCity @City = 'London';
EXEC
dbo.ListEmployeesByCity 'London';
GO
CREATE
@City
PROC dbo.ListEmployeesOptionalCityvarchar(25) = NULL
AS
SET
NOCOUNT ON
IF @City IS NULL
SELECT LastName, FirstName, CityFROM dbo.Employees;
ELSE
SELECT LastName, FirstName, CityFROM dbo.EmployeesWHERE City = @City;
GO
-- Lists employees in London
EXEC
dbo.ListEmployeesOptionalCity @City = 'London';
-- Lists all employees
EXEC
dbo.ListEmployeesOptionalCity;
GO
CREATE
@CompanyName
PROC dbo.InsertShippernvarchar(40) = NULL,
@Phone
nvarchar(24) = NULL,
@ShipperID
AS
SET
int = NULL OUTPUT NOCOUNT ON
IF @CompanyName IS NULL
SET @ShipperID = 0;
ELSE
BEGIN
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SET @ShipperID = SCOPE_IDENTITY();
END
GO
DECLARE
EXEC
@CompanyName
@NewShipperID int dbo.InsertShipper= 'Express2You',
@Phone
= NULL,
@ShipperID
= @NewShipperID OUTPUT;
SELECT
@NewShipperID AS ShipperID;
GO
CREATE
@CompanyName
PROC dbo.InsertShipperReturnnvarchar(40) = NULL,
@Phone
nvarchar(24) = NULL
AS
SET
NOCOUNT ON
IF @CompanyName IS NULL
RETURN 0;
ELSE
BEGIN
DECLARE @NewID int;
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SET @NewID = SCOPE_IDENTITY();
RETURN @NewIDEND
GO
DECLARE
@NewShipperID int;
EXEC
@CompanyName
@Phone
@NewShipperID = dbo.InsertShipperReturn = 'Express2You2', = NULL;
SELECT
@NewShipperID AS ShipperID;
GO
CREATE
@CompanyName
PROC dbo.InsertShipperResultsnvarchar(40) = NULL,
@Phone
nvarchar(24) = NULL
AS
SET
NOCOUNT ON
IF @CompanyName IS NULL
SELECT 0 AS NoNewShipperID;
ELSE
BEGIN
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SELECT SCOPE_IDENTITY() AS NewShipperID;
END
GO
EXEC
dbo.InsertShipperResults @CompanyName = 'Express2You2';
EXEC
dbo.InsertShipperResults @CompanyName = NULL;
GO
DECLARE
@Num int, @String varchar(50);
SET
@Num = 1;
SET
@String = 'Hello';
PRINT
CAST(@Num AS varchar(10)) + ' ' + @String;
SELECT
@Num = 2, @String = 'Hello World';
PRINT
CAST(@Num AS varchar(10)) + ' ' + @String;
GO
DECLARE
@First varchar(10), @Second varchar(10);
SET
@First = '101';
PRINT
'@First=' + @First;
PRINT
'@Second=' + @Second;
PRINT
'Done.'
GO
-- Causes an error.
DECLARE
@ID int;
SELECT
@ID = 1, FirstName, LastName FROM dbo.Employees;
GO
DECLARE
@ID int;
SELECT
@ID = NULL;
SELECT
@ID AS ID, FirstName, LastName FROM dbo.Employees;
GO
-- Generate a syntax error.
DECLARE
@ID int;
SELECT
@ID = 'Helloooo';
SELECT
@ID AS ID, FirstName, LastName FROM dbo.Employees;
GO
-- temp tables and table variables
--Debugging
CREATE
@CompanyName
PROC dbo.InsertShipperPrintnvarchar(40) = NULL,
@Phone
nvarchar(24) = NULL,
@ShipperID
AS
SET
PRINT
int = NULL OUTPUT NOCOUNT ON 'Initial variable values:';
PRINT
' @CompanyName=' + ISNULL(@CompanyName, 'NULL');
PRINT
' @Phone=' + ISNULL(@Phone, 'NULL');
PRINT
' @ShipperID=' + ISNULL(CAST(@ShipperID AS varchar(5)), 'NULL');
IF @CompanyName IS NULL
BEGIN
SET @ShipperID = 0;
PRINT 'ShipperID if CompanyName null='+ CAST(@ShipperID AS varchar(5));
END
ELSE
BEGIN
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SET @ShipperID = SCOPE_IDENTITY();
PRINT 'New ShipperID=' + CAST(@ShipperID AS varchar(5));
END
GO
DECLARE
@NewShipperID int;
EXEC
@CompanyName
dbo.InsertShipperPrint= 'Express2YouDebug',
@Phone
= NULL,
@ShipperID
= @NewShipperID OUTPUT;
PRINT
'Retrieved value=' + CAST(@NewShipperID AS varchar(5));
GO
CREATE
@CompanyName
PROC dbo.InsertShipperOutputParamsnvarchar(40) = NULL,
@Phone
nvarchar(24) = NULL,
@ShipperID
int = NULL OUTPUT,
@ReturnCode
int OUTPUT,
@ReturnMessage
AS
SET
varchar(255) OUTPUT NOCOUNT ON
SET @ReturnCode = 0;
SET @ReturnMessage = '';
IF @CompanyName IS NULL
SET @ReturnMessage = 'ShipperID is null.';
ELSE
BEGIN
DECLARE @id int, @rows int, @error int;
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
@id
SELECT @error = @@ERROR, @rows = @@ROWCOUNT, = SCOPE_IDENTITY();
IF @error = 0 AND @rows > 0BEGIN
SET @ShipperID = @id;
SET @ReturnCode = @rows;
SET @ReturnMessage = 'Success!';
END
ELSE
SET @ReturnMessage = 'Error=' + ISNULL(@error, 'No number for this error.');
END
GO
-- Test with valid inputs
DECLARE
@NewShipperID int;
DECLARE
@return int;
DECLARE
@message varchar(255)
EXEC
@CompanyName
dbo.InsertShipperOutputParams= 'Express2You',
@Phone
= NULL,
@ShipperID
@ReturnCode
= @NewShipperID OUTPUT, = @return OUTPUT,
@ReturnMessage
= @message OUTPUT;
SELECT
@return
@NewShipperID AS ShipperID, AS ReturnValue, @message AS ReturnMessage;
GO
-- Test with invalid CompanyName
DECLARE
@NewShipperID int;
DECLARE
@return int;
DECLARE
@message varchar(255)
EXEC
@CompanyName
dbo.InsertShipperOutputParams= NULL,
@Phone
= NULL,
@ShipperID
@ReturnCode
= @NewShipperID OUTPUT, = @return OUTPUT,
@ReturnMessage
= @message OUTPUT;
SELECT
@return
@NewShipperID AS ShipperID, AS ReturnValue, @message AS ReturnMessage;
GO
-- CLEANUP
DROP
PROC dbo.ListEmployees;
DROP
PROC dbo.ListEmployeesByCity;
DROP
PROC dbo.ListEmployeesOptionalCity;
DROP
PROC dbo.InsertShipper;
DROP
PROC dbo.InsertShipperReturn;
DROP
PROC dbo.InsertShipperResults;
-- drops for temp tables and table variables
DROP
PROC dbo.InsertShipperPrint;
DROP
PROC dbo.InsertShipperOutputParams;
DBCC
CHECKIDENT ('dbo.Shippers', RESEED, 1);
DBCC
CHECKIDENT ('dbo.Shippers', RESEED);