-- exec sp_helptext add_book1
CREATE proc add_book1 -- 创建存储过程
@DocCode VARCHAR(30) -- 创建参数
- as
- BEGIN
- INSERT INTO book_new_msg (
- book_num,
- book_msg,
- book_name,
- book_class,
- price,
- ware_people,
- Docdate,
- doctype
- )
- SELECT
- a.book_num,
- a.boo_msg,
- a.book_name,
- a.book_class,
- a.price,
- b.ware_people,
- b.DocDate,
- b.doctype
- FROM
- BOOK_MSG1 a
- INNER JOIN BOOK_WARE1 b ON a.DocCode = b.Doccode
- WHERE
- a.DocCode = @DocCode
- END
- BEGIN
- CREATE TABLE #T (book_num INT, book_sum INT)
- INSERT INTO #T (book_num, book_sum)
- SELECT
- book_num,
- SUM (book_count)
- FROM
- book_msg1
- WHERE
- DocCode= @DocCode
- GROUP BY
- book_num
- UPDATE a
- SET book_sum = ISNULL(a.book_sum, 0) + b.book_sum
- FROM
- book_sun a
- INNER JOIN #T b ON a.book_num = b.book_num
- INSERT INTO book_sun (book_num, book_sum) SELECT
- a.book_num,
- a.book_sum
- FROM
- #t a
- LEFT JOIN book_sun b ON a.book_num = b.book_num
- WHERE
- ISNULL(b.book_num, '') =''
- END
来源: http://www.bubuko.com/infodetail-3106405.html