| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Lecture - Getting Started with Stored Procedures in SQL Server

Page history last edited by Dr. Ron Eaglin 11 years, 2 months ago
 

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.