EXEC sproc

wdevinewdevine Posts: 4 Bronze 2
edited October 11, 2006 12:52PM in SQL Dependency Tracker
Hello,

I have a stored procedure that EXECs another stored procedure like this:

EXEC spRankDataSet @EntityId, @TypeID

But for some reason, the stored procedure that has this above code doesn't show the spRankDataSet stored procedure as a dependency.

Maybe I haven't set an option appropriately. Any help would be appreciated.

Thank you,

Bill

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Bill,

    This should definitely work. When you hover over this particular stored procedure, do you see something in a tooltip similar to 'NOT SHOWN:spRankDataSet '?
  • wdevinewdevine Posts: 4 Bronze 2
    No, it doesn't show me any extra information when I hover over the stored procedure. When I create my project, I just select the 'stored procedures' group.

    I've pasted a portion of the stored procedure below and how the EXEC call is being done. Let me know if you need any other information!
    	IF (@UseCache)=1	-- our DataSet has already been defined
    	BEGIN
    		EXEC spRankDataSet @EntityId, @TypeID
    	END
    	ELSE -- use the entire Entity table as DataSet
    	BEGIN		
    		-- create the temp table
    
    		CREATE TABLE #DataSet
    		(
    			numID INTEGER IDENTITY(1,1),
    			FkItemEntityID bigint not null,
    			ComparisonValue decimal(10,4) not null,
    			ComparisonSlice int,
    			PRIMARY KEY CLUSTERED (numID),
    			UNIQUE (FkItemEntityID, numID)
    		)
    
    		-- exec spNewRankUsers
    		INSERT INTO #DataSet EXEC spNewRankUsers @EntityID, @TypeID, @MaxRecords, @PageNum
    	END
    
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Can you script the spRankDataSet stored procedure as well?
  • wdevinewdevine Posts: 4 Bronze 2
    Hi Brian,

    Sorry I didn't get back to you quickly. I was travelling. Here's the spRankDataSet script that you asked for.

    Also, if you'd like the whole db schema, give me an email you'd like it sent to.

    Let me know if you need any other information.

    Thank you,

    Bill
    CREATE    Procedure [dbo].[spRankDataSet]
    (
    @E bigint,
    @T int = sfEntityTypeVisualizerValue	-- default, this is the typeId for Visualizer
    )
    as
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    
    
    SELECT e.EntityId as FkItemEntityId, cast(count(wd.WordText)*avg(cast(ww.XLoc as numeric(9,4))) * 0.01 as numeric(9,4)) as ComparisonValue,
            null as ComparisonSlice
    		INTO #DataSet2 FROM #DataSet ds
    	INNER JOIN Entity e ON ds.FkItemEntityId = e.EntityId
    	INNER JOIN WordDef wd
    	INNER JOIN WordSelection ws
    	INNER JOIN #entityWords ww
    		ON ws.WordId = ww.WordId
    		ON wd.WordID=ws.WordID 
    		ON ws.EntityID = e.entityid
    	where wd.WordText in (select t.WordText from #entityWords t)
    		group by e.entityid 
    
    
    Update ds
    Set ComparisonValue = ds2.ComparisonValue
    From #DataSet ds
    JOIN #DataSet2 ds2 on (ds.FkItemEntityId = ds2.FkItemEntityId)
    
    drop table #DataSet2
    
    
    GO
    
Sign In or Register to comment.