Chinook SQL Server Database

The Chinook Database is a sample database available for SQL Server, Oracle, MySQL, PostgreSQL, SQL Server Compact, SQLite and more. It is ideal for demos, application prototypes and perhaps even a digital media store application. It includes tables for artists, albums, media tracks, invoices and customers.

Download and Installation

Download the Chinook database from GitHub.

Chinook Database

Make sure you can Access your SQL Server [Configure Windows Firewall]

Connection String

<connectionStrings>
    <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290\SQLEXPRESS;Initial Catalog=Chinook;Integrated Security=True" />
    <!-- using account without SQL Express
    <add name="ChinookConnection" providerName="System.Data.SqlClient" connectionString="Data Source=VGN-FW290;Initial Catalog=Chinook;Persist Security Info=True;User ID=sa;Password=***********" />
    -->
</connectionStrings>

* VGN-FW290 is the name of my computer. Replace this with your computer name

SQL Stored Procedures

SQL Server 2012 Express Management Studio

Open up SQL Server Management Studio and connect to the server that is defined in the connection string. In the Object Explorer F8 Right click on the Stored Procedures node of the expanded Databases.Chinook tree and Select “New Stored Procedure”.

New Stored Procedure

The first stored procedure you will create, GetArtists uses CTE (Common Table Expression) to handle the paging of the records. Especially in web applications, it is a best practice to utilize paging when returning records from large tables.

dbo.GetArtists
CREATE PROCEDURE [dbo].[GetArtists]
(
	@Id INT = 0,
	@PageSize INT = 0,
	@PageNumber INT = 1
)
AS

DECLARE
	@FirstRow INT,
	@LastRow INT

IF (@Id = 0)
BEGIN

	IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Artist)

	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
			@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

	WITH Artist  AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber,
				COUNT(*) OVER () AS TotalCount, *
		FROM	dbo.Artist
	)
	SELECT	*
	FROM	Artist
	WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

END
ELSE
BEGIN
	SET NOCOUNT ON

	SELECT * FROM Artist
		WHERE ArtistId = @Id
END

Create stored procedure GetAlbums for selecting Album(s).

dbo.GetAlbums
CREATE PROCEDURE [dbo].[GetAlbums]
(
	@Id INT = 0,
	@ArtistId INT = 0,
	@PageSize INT = 0,
	@PageNumber INT = 1
)
AS

DECLARE
	@FirstRow INT,
	@LastRow INT

IF (@ArtistId <> 0)
BEGIN

	IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Album)

	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
			@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

	WITH Album  AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY Title ASC) AS RowNumber,
				COUNT(*) OVER () AS TotalCount, *
		FROM	dbo.Album
		WHERE	ArtistId = @ArtistId
	)
	SELECT	*
	FROM	Album
	WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

END
ELSE
BEGIN
	IF (@Id <> 0)
	BEGIN
		SET NOCOUNT ON

		SELECT * FROM Album
			WHERE AlbumId = @Id

	END
	ELSE
	BEGIN
		SET NOCOUNT ON

		IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Album)

		SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
				@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;

		WITH Album  AS
		(
			SELECT	ROW_NUMBER() OVER (ORDER BY Title ASC) AS RowNumber,
					COUNT(*) OVER () AS TotalCount, *
			FROM	dbo.Album
		)
		SELECT	*
		FROM	Album
		WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

	END
END

Create stored procedure GetTracks for selecting Track(s).

dbo.GetTracks
CREATE PROCEDURE [dbo].[GetTracks]
(
	@Id INT = 0,
	@AlbumId INT = 0,
	@PageSize INT = 0,
	@PageNumber INT = 1
)
AS

DECLARE
	@FirstRow INT,
	@LastRow INT

IF (@AlbumId <> 0)
BEGIN

	IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Track WHERE AlbumId = @AlbumId)

	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
			@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;

	WITH Track AS
	(
		SELECT	ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber,
				COUNT(*) OVER () AS TotalCount, *
		FROM	dbo.Track
		WHERE AlbumId = @AlbumId
	)
	SELECT	*
	FROM	Track
	WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

END
ELSE
BEGIN
	IF (@Id <> 0)
	BEGIN
		SET NOCOUNT ON

		SELECT * FROM Track
			WHERE TrackId = @Id

	END
	ELSE
	BEGIN
		SET NOCOUNT ON

		IF (@PageSize = 0) SET @PageSize = (SELECT COUNT(*) FROM dbo.Track)

		SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
				@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;

		WITH Track AS
		(
			SELECT	ROW_NUMBER() OVER (ORDER BY Name ASC) AS RowNumber,
					COUNT(*) OVER () AS TotalCount, *
			FROM	dbo.Track
		)
		SELECT	*
		FROM	Track
		WHERE	RowNumber BETWEEN @FirstRow AND @LastRow

	END
END

SQL Function

New Scalar-value function

An efficient way to return an Album Count column in each row is by using a function. This will come in handy when paginating Album records in the UI. Create this user defined function to add a computed AlbumCount column to the Artist Table.

dbo.CountAlbums
CREATE FUNCTION [dbo].[CountAlbums](@ArtistId INT)
RETURNS INT
WITH SCHEMABINDING
AS BEGIN
    DECLARE @ArtistAlbumCount INT

    SELECT @ArtistAlbumCount = COUNT(*)
    FROM dbo.Album
    WHERE ArtistId = @ArtistId

    RETURN @ArtistAlbumCount
END

Now we need to bind the SQL Function to the Artist table. In the Object Explorer F8, expand the Tables node and Artist table node. Right click on the Columns folder and select New Column from the context menu. Enter AlbumCount under Column Name and int under Data Type. Navigate to the Column Properties tab, expand the Computed Column Specification and enter ([dbo].[CountAlbums]([ArtistId])) for the (Formula) value. Save the Artist table Ctrl+S.

new-computed-column

Create stored procedure UpsertTrack for creating and updating track records.

dbo.UpsertTrack
CREATE PROCEDURE [dbo].[UpsertTrack]
	@AlbumId int = null,
	@Bytes int = null,
	@Composer nvarchar(220) = null,
	@GenreId int = null,
	@MediaTypeId int = 0,
	@Milliseconds int = 0,
	@Name nvarchar(220),
	@TrackId int = 0,
	@UnitPrice numeric(10,2) = 0.99
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @Exists tinyint
	SET @Exists = 0

	IF (@TrackId <> 0)
		BEGIN
			SET @Exists = (SELECT COUNT(*) FROM Track
			WITH (NOLOCK)
			WHERE TrackId = @TrackId)
			IF (@Exists > 0)
				BEGIN
					UPDATE Track
					SET
						AlbumId = @AlbumId,
						Bytes = @Bytes,
						Composer = @Composer,
						GenreId = @GenreId,
						MediaTypeId = @MediaTypeId,
						Milliseconds = @Milliseconds,
						Name = @Name,
						UnitPrice = @UnitPrice
					WHERE
						TrackId = @TrackId
				END
		END

	IF (@Exists = 0)
		BEGIN
			INSERT INTO Track(
				AlbumId,
				Bytes,
				Composer,
				GenreId,
				MediaTypeId,
				Milliseconds,
				Name,
				UnitPrice
			)
			VALUES(
				@AlbumId,
				@Bytes,
				@Composer,
				@GenreId,
				@MediaTypeId,
				@Milliseconds,
				@Name,
				@UnitPrice
			)
			SELECT @TrackId = SCOPE_IDENTITY()
		END

	RETURN @TrackId

END

Create stored procedure DeleteTrack for removing track records.

dbo.DeleteTrack
CREATE PROCEDURE [dbo].[DeleteTrack]
(
	@Id INT = 0
)
AS
BEGIN
	DELETE Track
	WHERE TrackId = @Id
END
comments powered by Disqus