Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: sproc in a function

    CREATE proc dbo.sp_address ( @Abbr char(2) )
    as
    DECLARE @StateID int
    SET @Abbr = UPPER(ISNULL( @Abbr, '' ))
    SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr )
    set @StateID=53
    IF ( @StateID is null )
    INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr )
    if @@ERROR = 0
    SET @StateID = @@Identity
    return(@StateID)
    GO


    Can I execute the above stored procedure in a function like this:

    create function
    sf_GetStateID( @Abbr char(2))
    returns integer
    begin
    declare @StateID int
    exec sp_address
    return(@StateID)
    end

    I just want to cross check.

    Tks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: sproc in a function

    You can execute an sproc from within a function, however, in this case you will not get the results that you desire. Why not simply add the code for your sproc into the function?

    Regards,

    hmscott

    Originally posted by kir441
    CREATE proc dbo.sp_address ( @Abbr char(2) )
    as
    DECLARE @StateID int
    SET @Abbr = UPPER(ISNULL( @Abbr, '' ))
    SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr )
    set @StateID=53
    IF ( @StateID is null )
    INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr )
    if @@ERROR = 0
    SET @StateID = @@Identity
    return(@StateID)
    GO


    Can I execute the above stored procedure in a function like this:

    create function
    sf_GetStateID( @Abbr char(2))
    returns integer
    begin
    declare @StateID int
    exec sp_address
    return(@StateID)
    end

    I just want to cross check.

    Tks

Posting Permissions

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