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

Support Materials

Information about setting @ variables in T-SQL with SELECT

Information about setting @ variables i-SQL using SET

Using IF-Then Statements in T-SQL

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
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