这篇博客旨在存储过程的写法,表变量的运用,及如何执行存储过程
一 存储过程
use BookStoreZone
Go
CREATE PROCEDURE [dbo].[UP_InsertBookCatalog]
@StoreID INT,
@Priority INT,
@BookID1 INT,
@BookType1 INT,
@BookID2 INT,
@BookType2 INT
AS
BEGIN
DECLARE @itemNum VARCHAR(15), --sp内部的变量
SET @itemNum=RTRIM(CAST(@ItemNumber AS varchar(15)))
SET @Priority=1 ;
DECLARE @TableId TABLE (table_id INT NOT NULL primary key,
BookId INT,BookType INT)
INSERT INTO @TableId
(
table_id,
BookIId,
BookType )
VALUES
(
1,
@BookID1 ,
@BookType1
)
INSERT INTO @TableId
(
table_id,
BookId2,
BookType2
)
VALUES
(
2,
@BookId2,
@BookType2
)
BEGIN
INSERT INTO dbo.BookStore
(StoreID, BookId,BookName, BookType, Priority)
SELECT @StoreID, a.BookId,c.BookName,c.BookType,@Priority
FROM dbo.BookMenu a WITH(NOLOCK)
INNER JOIN User c WITH(NOLOCK)
ON a.BookId = c.BuyBookId
AND (
CASE a.BookType WHEN 'D' THEN 2
ELSE 1 END
) = c.BuyBookType
WHERE a.BookId IN (SELECT ti.BookID FROM @TableId ti WHERE ti.BookType=3)
AND a.[Status] = 1
AND NOT EXISTS ( --返回1 则有值,exists=true ,整个and的结果为false
SELECT TOP 1 1 FROM dbo.BookMenu d WITH(NOLOCK)
WHERE d.BookID = @StoreID
AND d.BookId = a.BookId
AND d.BookType = (
CASE a.BookType WHEN 'D' THEN 2
ELSE 1 END))
END
END
GO
二 执行存储过程
在sql server 2008 中有自动执行的方法,只要给出参数变量就成了,一下是自动执行后的sql脚本。
DECLARE @return_value int
EXEC @return_value = [dbo].[UP_InsertBookCatalog]
@StoreID = 105,
@Priority = 1,
@BookID1=3,
@BookType1=5,
@BookID2=3,
@BookType2=5,
SELECT 'Return Value' = @return_value
也可
EXEC [dbo].[UP_InsertBookCatalog] 105,1,3,5,3,5