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

[<ABX]

Can i shorten sp_ArtistFromLyricID by executing sp_AlbumFromLyricID?

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

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

[szlamany]

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:

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

[<ABX]

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