Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Question Unanswered: calling one stored proceedure from another

    yet another question unfortunately

    I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,

    ie
    say i have select projectid, project name from projects into temp from projects.

    how can i then loop around all the rows in temp, to call my stored proceedure for each record?

    in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.

    can this be done for sql?

    I am trying to do something like

    for each record in #temp (projectid, project name) find the stored sprceedure value

    so my end result will look like

    projectid, project name, @storedproceedure return value
    lprojectid, project name, @storedproceedure return value
    projectid, project name, @storedproceedure return value
    projectid, project name, @storedproceedure return value

    any help appreciated

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Loops are inneficient and should be avoided and replaced with a set-based solution wherever possible.
    I think what's really needed here is a user defined function (or UDF for short)!
    I've knocked a quick example up for you to give you the idea.
    Code:
    IF EXISTS (SELECT type FROM sysobjects WHERE type = 'FN' AND name = 'gvFunc') BEGIN
        DROP FUNCTION dbo.gvFunc
    END
    GO
    
    CREATE FUNCTION dbo.gvFunc (
        @dob     datetime
      , @curDate datetime
      )
    RETURNS int
    AS
    BEGIN
        DECLARE @age int
        SET @age = DateDiff(yy, @dob, @curDate)
        RETURN @age
    END	
    GO
    
    DECLARE @myTable table (
        employeeID int IDENTITY(1,1) PRIMARY KEY
      , birthDate datetime
      , fName varchar(10) 
      )
    
    INSERT INTO @myTable (birthDate, fName)
    SELECT '1985-05-01', 'George'  UNION
    SELECT '1954-05-02', 'Timothy' UNION
    SELECT '1968-01-29', 'Julie'   UNION
    SELECT '1986-11-25', 'Adam'
    
    SELECT employeeId
         , fName
         , dbo.gvFunc(birthDate, GetDate()) As [Age]
    FROM   @myTable
    
    DROP FUNCTION dbo.gvFunc
    Note to others... I know that I can't use GetDate() within a function - but why is that?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •