SQL Server - Stored Procedure Question

Question

For the application I work on... we're creating a custom logging system. The user can view logs and apply "Tags" to them (Just like how you can apply tags to questions here!)

In this example, I'm trying to get a list of all the Logs given a "Tag." I realize I can accomplish this by using joins... but this is also an exercise for me to learn Stored Procedures a little better :)

I have a stored procedure that looks something like this to select a log by the PK

ALTER PROCEDURE [dbo].[getLogByLogId] 
    -- Add the parameters for the stored procedure here
    @ID int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  TOP 1

    LOG_ID,
    a.A,
    a.B,
    a.C

    FROM dbo.LOG a
    WHERE a.LOG_ID = @ID

Now I would like to call this Stored Procedure from another... something like this

ALTER PROCEDURE [dbo].[getLogsByTagName] 
        -- Add the parameters for the stored procedure here
        @TAG nvarchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT  TOP 1000

    LOG_ID --somehow store this and execute the dbo.getLogByLogId procedure here

    FROM dbo.LOG_TAG a
    WHERE a.TAG = @TAG

Thanks

Answer 1

If you want to call another sproc from within a sproc just use:

CREATE PROCEDURE myTestProc
AS
BEGIN

--Do some work in this procedure
SELECT blah FROM foo

--now call another sproc
EXEC nameOfSecondSproc
END

Answer 2

The only way you can achive what you are attempting is by using a CURSOR.

If this is for your learning only, then by all means, give this a go, but I would not recomend this for production.

It would go something like this

DECLARE @Table TABLE(
        ID INT
)

INSERT INTO @Table SELECT 1
INSERT INTO @Table SELECT 2
INSERT INTO @Table SELECT 3
INSERT INTO @Table SELECT 4
INSERT INTO @Table SELECT 5
INSERT INTO @Table SELECT 6


DECLARE Cur CURSOR FOR 
SELECT ID
FROM @Table

OPEN Cur

DECLARE @ID INT
FETCH NEXT FROM Cur INTO @ID

WHILE @@FETCH_STATUS = 0 
BEGIN
    PRINT @ID
    FETCH NEXT FROM Cur INTO @ID
END

CLOSE Cur
DEALLOCATE Cur

By using the @ID retrieved in the WHILE loop, you can then execute the sp you wish and insert the values into a table variable.

INSERT INTO @Table EXEC sp_MySP @ID

Answer 3

You can call a stored procedure from another using the following syntax:

    ALTER PROCEDURE [dbo].[getLogsByTagName]  
            -- Add the parameters for the stored procedure here 
            @TAG nvarchar(50) 

    AS 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON; 

        -- Insert statements for procedure here 
        SELECT  TOP 1000 

        LOG_ID --somehow store this and execute the dbo.getLogByLogId procedure here 

        FROM dbo.LOG_TAG a 
        WHERE a.TAG = @TAG 

        -- Execute dbo.getLogByLogId stored procedure
    DECLARE @logId INTEGER
    SET @logId = <some value>
    EXEC dbo.getLogByLogId @logId
END

However, the difficult part of your question is that your dbo.getLogByLogId procedure can only accept a single LogID parameter and therefore will only be able to return a single Log record. You need to return information for all Logs where the LogId has a corresponding record in the Tags table.

The correct way to do this would be to JOIN the Log and Tag tables together, like so:

SELECT *
FROM dbo.LOG_TAG a
INNER JOIN dbo.LOG b ON a.LOG_ID = b.LOG_ID
WHERE a.TAG = @TAG

If you are concerned about returning the same logId multiple times, you can use the DISTINCT keyword in the SELECT statement to filter out the duplicated logIds.

You may also be able to rewrite your dbo.getLogByLogId procedure as a user-defined function (UDF). UDFs can accept a table as a parameter and return a table result.

An introduction to user-defined functions can be found in this article.

Answer 4

If you have complex logic in your logbyid SP which you are trying to avoid reproducing in multiple places in your system (choice of columns, derived columns, etc), I would recommend turning that into an inline table-valued function instead (potentially without taking the ID parameter, in which case, you can actually use an ordinary view).

Then you can either join to that ITVF/view in your other stored proc (or also make another udf) which does the search or use the OUTER APPLY functionality (not as efficient).

Inline table-valued functions are basically parameterized views and can be optimized fairly easily by the optimizer.