Link to home
Start Free TrialLog in
Avatar of jfreeman2010
jfreeman2010Flag for United States of America

asked on

t-sql stored procedure calling other stored procedure - sql server 2008 r2

Hi

I am in sql server 2008 r2 and need help in a stored procedure that need to call anther procedure, pass value and return value.

example:

declare @temp_std table
(
      std_id                        int,
      ssn                        varchar(9),      
      class_last_timestamp            datetime

)

;;;;;;


      insert @temp_std
      (
       std_id,
       ssn
      )
      select distinct
        std_id,
        ssn
      from test_tb
      where ssn is not null

;;;;;;
      In here:

      I need to call a stored procedure 'get_class_update_timestamp',
      pass ssn and return last_update_timestamp for each row in
        @temp_std  

 thank you


ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jfreeman2010

ASKER

Thank you very much for the responses.

I also was thinking to convert that stored procedure to a function.  I am going to see if I can convert it.
Stored procs are good for doing things, functions are good for returning stuff.
I got a error when try to convert the stored procedure to a function as:

cannot access temporary tables from within a function.

any idea I can fix this?

thanks.
Could you post a piece of code from the stored procedure you want to convert here, please?
Thanks:

ALTER PROCEDURE [dbo].[get_class_update_timestamp]

@SSN CHAR(9),

,@class_LATEST_TIMESTAMP DATETIME OUTPUT

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


SET NOCOUNT ON

CREATE TABLE #temp_table_class (class_updated_timestamp datetime)

DECLARE @SQLString NVARCHAR(4000)
DECLARE @class_table_id varchar(10), @table_name varchar(50), @SSN_FIELD VARCHAR(10),@class_FIELD_NAME varchar(10)


DECLARE class_cursor CURSOR FOR
SELECT ID,TABLE_NAME,SSN_FIELD, class_FIELD_NAME
FROM class_DEFINITION
WHERE CONTAINS_class_FIELD = 1
             
OPEN class_cursor

FETCH NEXT FROM class_cursor
INTO @class_table_id, @table_name, @SSN_FIELD,@class_FIELD_NAME
IF @@FETCH_STATUS <> 0
      PRINT  'END OF TABLES'  

WHILE @@FETCH_STATUS = 0
BEGIN
      IF @SSN <> ''
      BEGIN
            set @SQLString='SELECT TOP 1 TIME_STAMP
                  FROM       '+@table_name+' T,
                        class_DEFINITION F
                  WHERE       '+@SSN_FIELD+'='''+@SSN+
                        ''' AND ('+@class_FIELD_NAME+' IS NOT NULL ) AND F.ID= '+@class_table_id+
                  ' AND T.FORM_ID = F.FORM_ID
                  ORDER BY RECORD_ID DESC'
      END

INSERT #temp_table_class
EXECUTE (@SQLString)


FETCH NEXT FROM class_cursor
INTO @class_table_id, @table_name,@SSN_FIELD, @class_FIELD_NAME

END


CLOSE class_cursor
DEALLOCATE class_cursor

SELECT @class_LATEST_TIMESTAMP =(SELECT MAX(class_updated_timestamp)  FROM #temp_table_class)
 
 

GO


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the called procedure give a latest date,

the calling procedure need this latest date to determine if this row/record can be use/continue for the processing.

Do I make myself clear?

Thank you,
So since I can't convert this procedure into a function since it requires dynamic sql, the only choice I have is loop thru the table variables and call the procedure  and update the table variables.
Does this seem right?

declare @temp_std table
(
      std_id                        int,
      ssn                        varchar(9),      
      class_last_timestamp            datetime

)

DECLARE @last_update_timestamp  datetime;
DECLARE @SSN                  VARCHAR(9)

DELARE L_CURSOR CURSOR FOR
SELECT SSN
FROM @temp_std
WHERE SSN IS NOT NULL


Begin
      insert @temp_std
      (
       std_id,
       ssn
      )
      select distinct
        std_id,
        ssn
      from test_tb
      where ssn is not null

   
      OPEN L_CURSOR

      FETCH NEXT FROM L_CURSOR
      INTO @SSN, @last_update_timestamp

      WHILE @@FETCH_STATUS = 0

      BEGIN
         EXEC dbo.get_class_update_timestamp @ssn, @last_update_timestamp OUTPUT

        update t
          set class_last_timestamp = @last_update_timestamp
        from @temp_std t
        where t.ssn =  @ssn

       FETCH NEXT FROM L_CURSOR
       INTO @SSN, @last_update_timestamp

      END

      CLOSE L_CURSOR
      DEALLOCATE L_CURSOR

End
basically yes ...

but do the updates on an SSN basis (one call per ssn in the input) not actually row by row...
does the fetch is base on the row by row?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
YES, it works. thank you very much for help!!
thanks all for the help