Execute in sqlserver

CREATE TABLE May1998Sales
    (OrderID int PRIMARY KEY,CustomerID int NOT NULL,OrderDate  datetime NULL
        CHECK (DATEPART(yy,OrderDate) = 1998),OrderMonth int
        CHECK (OrderMonth = 5),DeliveryDate datetime  NULL,CHECK (DATEPART(mm,OrderDate) = OrderMonth)
    )

This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. The example uses the order date to build the name of the table that should contain the data,and then incorporates that name into an INSERT statement.

CREATE PROCEDURE InsertSales @PrmOrderID INT,@PrmCustomerID INT,@PrmOrderDate DATETIME,@PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm,@PrmOrderDate),1,3) +
       CAST(DATEPART(yy,@PrmOrderDate) AS CHAR(4) ) +
       'Sales' +
       /* Build a VALUES clause. */
       ' VALUES (@InsOrderID,@InsCustID,@InsOrdDate,' +
       ' @InsOrdMonth,@InsDelDate)'

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm,@PrmOrderDate)

EXEC sp_executesql @InsertString,N'@InsOrderID INT,@InsCustID INT,@InsOrdDate DATETIME,@InsOrdMonth INT,@InsDelDate DATETIME',@PrmOrderID,@PrmCustomerID,@PrmOrderDate,@OrderMonth,@PrmDeliveryDate

GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!