--Datenbank erstellen
CREATE DATABASE TestData
GO

Use TestData;
GO

-- Tabelle erstellen
CREATE TABLE dbo.Products
(ProductID int PRIMARY KEY NOT NULL,
ProductName varchar(25) NOT NULL,
Price money NULL,
ProductDescription text NULL)
GO

-- Standard syntax
INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
GO


-- oder
INSERT dbo.Products
VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
GO

-- Update
UPDATE dbo.Products
SET ProductName = 'Flat Head Screwdriver'
WHERE ProductID = 50
GO


-- The basic syntax for reading data from a single table
SELECT ProductID, ProductName, Price, ProductDescription
FROM dbo.Products
GO

-- Returns all columns in the table
-- Does not use the optional schema, dbo
SELECT * FROM Products
GO

-- Returns only two of the columns from the table
SELECT ProductName, Price
FROM dbo.Products
GO

-- Create Procedures
CREATE PROCEDURE pr_Names @VarPrice money
AS
BEGIN
-- The print statement returns text to the user
PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
-- A second statement starts here
SELECT ProductName, Price FROM vw_Names
WHERE Price < @varPrice;
END
GO


-- Stored Procedures ausführen
EXECUTE pr_Names 10.00;
GO