?
Solved

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

Posted on 2011-10-28
16
Medium Priority
?
489 Views
Last Modified: 2012-05-12
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


0
Comment
Question by:jfreeman2010
  • 9
  • 3
  • 2
  • +2
16 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 37045784
You can't call a stored proc from a SELECT statement.

You'll have to cursor through each row, and then call the proc using the values FETCHed from the cursor:

DECLARE @last_update_timestamp <datatype>

EXEC dbo.get_class_update_timestamp @ssn, @last_update_timestamp OUTPUT
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1000 total points
ID: 37045886
not totally clear how (and where) you need to do this...

would it be possible to convert the stored procedure into a function so that it could be used in the Insert/select statement?
0
 

Author Comment

by:jfreeman2010
ID: 37045932
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 37045993
Stored procs are good for doing things, functions are good for returning stuff.
0
 

Author Comment

by:jfreeman2010
ID: 37046617
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.
0
 
LVL 11

Expert Comment

by:dougaug
ID: 37046784
Could you post a piece of code from the stored procedure you want to convert here, please?
0
 

Author Comment

by:jfreeman2010
ID: 37046798
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


0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1000 total points
ID: 37046816
no you cannot convert the procedure into  a function as it requires dynamic sql

 (unless the number of tables is small and you can hard code all possibilities)


can you take a set back and explain what the process is trying to do and why?
0
 

Author Comment

by:jfreeman2010
ID: 37046864
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,
0
 

Author Comment

by:jfreeman2010
ID: 37047048
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.
0
 

Author Comment

by:jfreeman2010
ID: 37047130
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37047162
basically yes ...

but do the updates on an SSN basis (one call per ssn in the input) not actually row by row...
0
 

Author Comment

by:jfreeman2010
ID: 37047204
does the fetch is base on the row by row?
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 37047430
Yes, FETCH is row-by-row.

The items in the DECLARE ... CURSOR FOR SELECT list must match exactly the position of the items in the FETCH list.  The match is done strictly **by position, not by name**.

DECLARE ...
select distinct
        std_id, --<<-- first col SELECTed
        ssn --<<-- second col SELECTed
...
     FETCH NEXT FROM L_CURSOR
      INTO @std_id, @SSN
...
0
 

Author Comment

by:jfreeman2010
ID: 37047604
YES, it works. thank you very much for help!!
0
 

Author Closing Comment

by:jfreeman2010
ID: 37047619
thanks all for the help
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question