Insert, Conditions, 3 Tables, Syntax, Performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert, Conditions, 3 Tables, Syntax, Performance

Author

Topic

Eulibrius7
Starting Member

6 Posts

Posted - 2004-06-13 : 04:19:54

The following code should insert into 3 tables based on conditions. There's something screwy in my syntax and I'm pretty new at this can anyone help with transforming this in terms of performance and being syntactically correct? Thanks a million!


CREATE PROCEDURE [insert_vwMusic]
	(@Artist 	[nvarchar](50),
	 @Genre 	[nvarchar](50),
	 @NLink 	[nvarchar](50),
	 @Album	[nvarchar](50),
	 @Song		[nvarchar](50),
	 @ArtistID	[nvarchar](50),
	 @AlbumID	[nvarchar](50),
	 @SLink	[nvarchar](50))

AS

DECLARE @NewArtistID VarChar(50),
DECLARE @NewAlbumID VarChar(50)

IF Not Exists (SELECT [Artist] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
	BEGIN
	INSERT INTO [integration].[dbo].[tblMusic_Artist] 
		 ( [Artist],
		 [Genre],
		 [NLink]) 
	 
	VALUES 
		( @Artist,
		 @Genre,
		 @NLink)
	
	SET @NewArtistID = @@IDENTITY

	INSERT INTO [integration].[dbo].[tblMusic_Albums] 
		 ( [Album]
	 
	VALUES 
		( @Album)

	SET @NewAlbumID = @@IDENTITY

	INSERT INTO [integration].[dbo].[tblMusic_Song] 
		 ( [Song],
		 [ArtistID],
		 [AlbumID],
		 [SLink]) 
	 
	VALUES 
		( @Song,
		@NewArtistID,
		@NewAlbumID,
		 @SLink)	
	END

ELSE
	BEGIN
	IF Not Exists (SELECT [Album] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
		BEGIN
		INSERT INTO [integration].[dbo].[tblMusic_Albums] 
			 ( [Album]
		 
		VALUES 
			( @Album)
	
		SET @NewAlbumID = @@IDENTITY
		SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)
	
		INSERT INTO [integration].[dbo].[tblMusic_Song] 
			 ( [Song],
			 [ArtistID],
			 [AlbumID],
			 [SLink]) 
		 
		VALUES 
			( @Song,
			@NewArtistID,
			@NewAlbumID,
			@SLink)	
		END
	END
	ELSE
		BEGIN
		SET @NewAlbumID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Album] WHERE [Album] = @Album)
		SET @NewArtistID = (SELECT [ID] FROM [integration].[dbo].[tblMusic_Artist] WHERE [Artist] = @Artist)		

		INSERT INTO [integration].[dbo].[tblMusic_Song] 
			 ( [Song],
			 [ArtistID],
			 [AlbumID],
			 [SLink]) 
		 
		VALUES 
			( @Song,
			@NewArtistID,
			@NewAlbumID,
			@SLink)	
		END

GO

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-06-13 : 07:36:23

Do you get any errors? The only thing I can see is that it seems to be one "END" too many right before the very last "ELSE"-statement...other than that it all looks good to me.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-13 : 12:18:41

Also, you should replace @@identity with SCOPE_IDENTITY(). This could cause big problems if you have triggers on any of these tables.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Eulibrius7
Starting Member

6 Posts

Posted - 2004-06-13 : 13:36:28

Error 156 Near DECLARE, Values, Values

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-13 : 13:41:53

You can SET PARSEONLY ON and QA will tell you of any syntax errors without running the script, but here ya go:

Line

13: DECLARE @NewArtistID VarChar(50), <-- remove comma
31: ( [Album] <-- add )
56: ( [Album] <-- add )
61: SET @NewAlbumID = @@IDENTITY <-- change to SCOPE_IDENTITY()
76: END <-- remove

Eulibrius7
Starting Member

6 Posts

Posted - 2004-06-13 : 13:52:17

With a little diligence... the syntax is fixed...now let's take the procedure for a test run!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources