Getting Started with Stored Procedures in SQL Server
Summary of Video
This Video uses the Music Database example shown in Lecture - Getting Started with SQL Server Management Studio . We create a stored procedure to insert data into the database using the referential integrity rules.
Prerequisites
Lecture - Getting Started with SQL Server Management Studio
Video Link
http://online1.daytonastate.edu/player2.php?id=f0b1d5879866f2c2eba77f39993d1184
Support Materials
Information about setting @ variables in T-SQL with SELECT http://msdn.microsoft.com/en-us/library /ms187330.aspx
Information about setting @ variables i-SQL using SET http://msdn.microsoft.com/en-us/library/ms189484.aspx
Using IF-Then Statements in T-SQL - http://msdn.microsoft.com/en-us/library/ms182587.aspx
The stored procedure created in the lecture;
USE [MusicDatabase]
GO
CREATE PROCEDURE [dbo].[sp_InsertSong] (
@SongName NVARCHAR(200),
@AlbumName NVARCHAR(200),
@ArtistName NVARCHAR(200),
@TrackNumber INT)
AS
BEGIN
DECLARE @ArtistCount INT
SET @ArtistCount = (SELECT COUNT(*) FROM Artists WHERE Name = @ArtistName)
DECLARE @ArtistID INT
IF @ArtistCount = 1
BEGIN
SET @ArtistID = (SELECT id FROM Artists WHERE name = @ArtistName)
END
ELSE
BEGIN
INSERT INTO Artists (name) VALUES (@ArtistName)
SET @ArtistID = @@IDENTITY
END
DECLARE @AlbumCount INT
SET @AlbumCount = (SELECT COUNT(*) FROM Albums WHERE name = @AlbumName)
DECLARE @AlbumID INT
IF @AlbumCount = 1
BEGIN
SET @AlbumID = (SELECT id FROM Albums WHERE name = @AlbumName)
END
ELSE
BEGIN
INSERT INTO Albums (Name, ArtistID) VALUES (@AlbumName, @ArtistID)
SET @AlbumID = @@IDENTITY
END
INSERT INTO Songs (name, AlbumID, TrackNumber)
VALUES (@SongName, @AlbumID, @TrackNumber)
END
GO
|
All Materials Copyright 2012 Dr. Ron Eaglin
Comments (0)
You don't have permission to comment on this page.