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

    Unanswered: Exec sproc in a update function

    Folks

    Here is a query which updates certain values. GetAddress is another
    sproc which returns addrId. I have to pass certain values ie
    strAddress1 strCity .....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
    not a recognized function name. Is the syntax correct to exec sproc
    GetAddress.


    update Persons
    set
    Persons.strLastName=H.strLastName,
    Persons.strNameSuffix=H.strNameSuffix,
    Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
    Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
    from ALSHeadr H
    where Persons.lngSSN=H.lngFedTaxID



    FYI I can post GetAddress sproc but it is working properl.
    I just want to know how to pass the values in ALSHeadr table into
    the sproc.


    Thanx

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Use (create) function instead of sp in this case.

  3. #3
    Join Date
    May 2003
    Posts
    40
    Snail

    Y do I need to make it a function?


    create procedure ALSHeadr2Persons
    as
    /*declaration goes here*/
    /* Update existing Persons*/
    set @Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

    update
    Persons set
    Persons.strNameSuffix=ALSHeadr.strNameSuffix,
    Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
    Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
    from ALsHeadr
    where Persons.lngSSN=ALSHeadr.lngFedTaxID


    end



    How do I pass the values of ALSHeadr table the GetAddress sproc??
    based on the condition Persons.lngssn=alsheadr.lngfedtaxid


    Any other syntax solution?

    Thx

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by kir441
    Snail

    Y do I need to make it a function?


    create procedure ALSHeadr2Persons
    as
    /*declaration goes here*/
    /* Update existing Persons*/
    set @Cntr = ( select Count(distinct P2.lngSSN) from Persons P2 join ALSHeadr H2 on H2.lngFedTaxID=P2.lngSSN where P2.lngSSN>0 and P2.lngSSN<999999999)

    update
    Persons set
    Persons.strNameSuffix=ALSHeadr.strNameSuffix,
    Persons.lngHomeID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4 ,
    Persons.lngMailID= GetAddress ALSHeadr.strAddress1,strAddress2,ALSHeadr.strCity, ALSHeadr.strState,ALSHeadr.strZip,ALSHeadr.intZip4
    from ALsHeadr
    where Persons.lngSSN=ALSHeadr.lngFedTaxID


    end



    How do I pass the values of ALSHeadr table the GetAddress sproc??
    based on the condition Persons.lngssn=alsheadr.lngfedtaxid


    Any other syntax solution?

    Thx
    What about this draft?

    drop table test
    drop table test2
    create table test(id int)
    create table test2(id int, code varchar(10))
    go
    insert test values(1)
    insert test values(2)
    insert test values(3)
    insert test2 values(1,'a')
    insert test2 values(2,'b')
    insert test2 values(3,'c')
    go
    CREATE FUNCTION getit(@id int)
    RETURNS varchar
    AS
    BEGIN
    declare @ret varchar(10)
    select @ret=code from test2 where id=@id
    RETURN @ret
    END
    GO
    select *,dbo.getit(id)
    from test

Posting Permissions

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