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

    Unanswered: insert in a sproc

    I am trying to insert a value with Insert statement in a sproc

    create procedure AddNewPersonsFromALSHeadr
    as
    begin

    insert into dbo.Persons(lngSSN,
    strNamePrefix,
    strFirstName,
    lngHomeID,
    lngMailID)

    select lngFedTaxID,
    strNamePrefix,
    strFirstName,

    exec @HomeId = GetAddress H.strAddress1,strAddress2,H.strCity,H.strState,H.s trZip,H.intZip4,
    @HomeId


    from dbo.ALSHeadr H


    end
    go


    The probl is while inserting the fourth column values I have to execute a sproc
    "GetAddress" and take the value and insert it in "lngHomeID" and the the same value in
    "lngMailID". I tried all possible ways for the syntax but 'm getting the error
    incorrect sytanx near exec and also syntax error near " . " at H.strAddress1 etc......


    FYI this is the sproc GetAddress(I omitted few lines in this sproc)


    CREATE procedure dbo.GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
    as
    begin
    ------------------------------------------
    ------------------------------------------
    --------------------------------------
    EXEC @ZipID= dbo.GetZipID @City,@State,@Zip5
    return(@AddrID)
    print (@AddrID)
    end
    GO



    Tks

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    You could use function GetAddress instead of procedure GetAddress.

    Something like this:

    create table tmptable(id int identity,code varchar(10))
    go
    insert tmptable(code) values('a')
    insert tmptable(code) values('b')
    insert tmptable(code) values('c')
    insert tmptable(code) values('d')
    insert tmptable(code) values('e')
    insert tmptable(code) values('f')
    go
    create function getit(@code varchar(10))
    returns int
    as
    begin
    return ASCII(@code)
    end
    go
    select code,dbo.getit(code) from tmptable
    Last edited by snail; 01-23-04 at 11:08.

  3. #3
    Join Date
    May 2003
    Posts
    40
    Cann I use a sproc in that?

    The problem is the sproc
    GetAddress has an insert function in it which makes me to make it sproc only but not a function.

    Is it not possible with sproc only but not a function.

    FYI

    CREATE procedure dbo.GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
    as
    begin
    declare @ZipID integer
    declare @AddrID integer
    set @AddrID=1
    if ltrim(@Addr1)='' or @Addr1 is null
    if ltrim(@Addr2)>''
    begin
    set @Addr1=@Addr2
    set @Addr2=''
    end
    else
    begin
    set @addr1='UNKNOWN'
    end
    if lTrim(@Addr1)<>''
    EXEC @ZipID= dbo.GetZipID @City,@State,@Zip5
    set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2)
    if @AddrId is null

    insert into Addrs(Address1,Address2,lngZipCodeID,intPlus4) values(@Addr1,@Addr2,@ZipId,@Zip4)


    if @@error = 0
    if lTrim(@Addr2)>''
    begin
    set @AddrID = (select Min(lngAddrID) from Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2)
    end
    else
    begin
    set @AddrID = (select Min(lngAddrID) from Addrs where lngZipCodeID=@ZipID and Address1=@Addr1)
    end

    return(@AddrID)
    print (@AddrID)
    end
    GO


    this is my GetAddress sproc. Because I have to use "Insert into Addr"
    I cannot make it as a function. Hope you undrstand the problem.

    any suggestions?


    Tks

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    If I understand correctly - you have incoming information in table ALSHeadr. You could add some fields for ids what will be filled by trigger (call your sp or whatever else inside trigger) on insert and update. As result - you could do your insert by only selecting from table.

Posting Permissions

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