M$SQL:Stored Procedures - Executing Procedure in a Procedure? [Resolved]

Can i shorten sp_ArtistFromLyricID by executing sp_AlbumFromLyricID?

Code:

---

CREATE PROCEDURE sp_ArtistFromLyricID
(
        @LyricID                        bigint
)
AS
BEGIN

        SELECT
                Data_Artists.*, Data_Albums.*, Data_Songs.*, Data_Lyrics.*
        FROM
                Data_Artists INNER JOIN Data_Albums
                        ON Data_Artists.ArtistID = Data_Albums.ArtistID
                INNER JOIN Data_Songs
                        ON Data_Albums.AlbumID = Data_Songs.AlbumID
                INNER JOIN Data_Lyrics
                        ON Data_Songs.SongID = Data_Lyrics.SongID
        WHERE
                Data_Lyrics.LyricID = @LyricID
END
GO

---

Code:

---

CREATE PROCEDURE sp_AlbumFromLyricID
(
        @LyricID                        bigint
)
AS
BEGIN

        SELECT
                Data_Albums.*, Data_Songs.*, Data_Lyrics.*
        FROM
                Data_Albums
                INNER JOIN Data_Songs
                        ON Data_Albums.AlbumID = Data_Songs.AlbumID
                INNER JOIN Data_Lyrics
                        ON Data_Songs.SongID = Data_Lyrics.SongID
        WHERE
                Data_Lyrics.LyricID = @LyricID
END
GO

---

I'm not completely sure I understand your question - but yes you can EXEC {sprocname} from within another SPROC.

We sometimes pass an @OPT parameter to a SPROC - and do something like:

Code:

---

IF @OPT=1
Begin
    Select ...
    Return
End

IF @OPT=2
Begin
    Exec SomeSprocName
    Return
End

---

It's much harder to EXEC a SPROC and get it's values joined into another query - usually have to create a TABLE VARIABLE to EXEC the results of the SPROC into and then JOIN to that.

BTW - What's with all the * usage - that's generally frowned upon in production work. If you have a VB client APP that expects let's say 10 columns from some table and then next year you add an elevent column - you can have a nightmare fixing those * column ref's. We would not allow our programming staff to use * references to columns :)

The * was just for testing purposes.... i was too lazy to write out all the columns if it wasnt going to work...