Stored Proc question

[majaro]

I need an example of a stored procedure that uses an IF statement.

What I need is count how many rows are in a table that contain Products. If there aren't any rows that contain Products use LAST_INSERT_ID().

I've Googled til Im blue in the face looking for an example but can't find one. Just trying to learn MySQL on my own and I think I am really confused. Any help would be appreciated. Thanks in advance. If you need more info PM me or post here.

[CreganTur]

I'm taking a blind guess, since there isn't much data about the table provided:

[VBA]IIF(Count(TableName.FieldName) = 0,LAST_INSERT_ID,Count(TableName.FieldName)[/VBA]

The above SQL uses an IIF (not a typo) to check the returned value of the count. If it is 0, then it uses what you defined. If it does not meet the condition, then it returns the count.

This should give you a basic idea to build on.

HTH

[majaro]

Thanks. A little more detail. So Ill start at the beginning.

I am trying to do the following:

create a new procedure called AddAlbum. It will take two parameters - ArtistName and AlbumName. I need to check the Artists table to see if an ArtistName exists. If it does not exist, I need to add a new row. I'm attempting the following:

1. Use the COUNT aggregate on table Artists to see how many rows exist for ArtistName. Store the count in a variable called artist_count. This is as far as I've gotten.
2. If artist_count is zero, insert a new row into Artists. Select the LAST_INSERT_ID() into a variable.
3. If artist_count is one, lookup the ArtistID and store it in a variable.
4. Insert a new row into the Albums table.

Here is the code I've got to work so far.

Drop Procedure if exists AddAlbum;//
Create procedure AddAlbum(in_ArtistName Varchar(50), in_Title varchar(50)) 
BEGIN 
Declare Artist_Count int; 
Declare artist_id int; 
Select Count(*) into Artist_Count From Artists; 
END; //

[code]

Am I on the right track and how do you do the If statements?

Before anyone flames me, yes this is a school assignment and I am fully aware of the policy regarding homework but I am so lost on this. Any guidance is appreciated. Thanks in advance for any help.

[CreganTur]

how do you do the If statements?

In SQL they are IIF statements, not If.

The IIF Statement:
IIF(condition, true, false)

It is easy to string multiple IIF's together. For example:
[VBA]IIF(Table.Number = 1,'one',IIF(Table.Number = 2,'two',IIF(Table.Number = 3,'three',IIF(Table.Number > 3,'max','zero'))))[/VBA]

[majaro]

Thanks. Another question. If the count is equal to 1 then how would you look up the artist_id and store it in a variable? Oh I forgot to mention this is a mySQL database. Not sure if that makes a difference with the IIf statements.

[shorty]

This would work in Oracle, maybe it can help you in the rigth direction?

[vba]
CREATE OR REPLACE PROCEDURE AddAlbum(in_ArtistName VARCHAR(50), in_Title VARCHAR(50)) IS

v_counter INT;
v_artistid INT;

BEGIN

SELECT COUNT (1)
INTO v_counter
FROM Artists
WHERE ArtistName = in_ArtistName;

IF v_counter >= 1 THEN
SELECT ArtistId
INTO v_artistid
FROM Artists
WHERE ArtistName = in_ArtistName;
ELSE
/* do other stuff */
END IF;

/* rest of coding */
END;
[/vba]