数据库常用语法

1.健表

CREATE TABLE [dbo].[CoreType]
(
[CoreTypeID] [int] NOT NULL IDENTITY(1, 1),
[Description] [varchar] (50) NOT NULL
) ON [PRIMARY]

2.修改列名

ALTER TABLE [dbo].[AccountReconciliationWorksheet]
      ALTER COLUMN [ClearedDeposits] [decimal](19, 4) NULL

3.插入数据

SET IDENTITY_INSERT [dbo].[CabType] ON
--insert some data
SET IDENTITY_INSERT [dbo].[CabType] OFF

4.case when 、 isnull 、cast

CASE
    WHEN ISNULL(QC1.CommittedRecords, 0) + ISNULL(QC2.CommittedRecords, 0) > 0 THEN CAST(1 AS BIT)
    ELSE CAST (0 AS BIT)
END AS HasCommittedQuantity,

CASE
  WHEN ID.BinLocationID Is NULL
  THEN ID.BinLocation
  WHEN (SELECT TOP 1 ValidatedBinLocation FROM Enterprise)=1
  THEN BL.BinLocation
  ELSE ID.BinLocation
END as BinLocation,

5 函数的使用

dbo.fnGB_GBL_GetUserName(WS.AddUserID) AS AddUser, 

CREATE FUNCTION dbo.fnGB_CON_GetPrimaryContactID(
@EntityType varchar(50)
)  
RETURNS TABLE
AS
/*********************************************************************************************************************
This function returns the PrimaryContactIDs for entity type passed in.

*********************************************************************************************************************/
RETURN
SELECT 
CON.EntityID,
CON.ContactID PrimaryContactID
FROM Contact CON
INNER JOIN EntityType ET ON
CON.EntityTypeID = ET.EntityTypeID
WHERE ET.[Description] = @EntityType
AND CON.SetDefault = 1

CREATE FUNCTION [dbo].[fnGB_GBL_BitToYesNo]
(
    @BitVal BIT
)
RETURNS NCHAR
AS

/*
    DESCRIPTION
    This function translates 1 to 'Y' and 0 to 'N'.

    MODIFICATION HISTORY
    2006-09-26    Phillip Koebbe
        - Created.
*/

BEGIN
    DECLARE
        @Char NCHAR
    
    IF @BitVal = 1
        SET @Char = 'Y'
    ELSE
        SET @Char = 'N'

    RETURN @Char
END

 

6.存储过程

CREATE PROCEDURE [dbo].[spSM_POR_GetOpenLPOAmount]
    @UnitInventoryID int
AS

    select isnull(sum(ExpectedAmount) ,0)
    from LocalPurchaseOrder 
    where UnitInventoryID = @UnitInventoryID and PurchasingStatusID = 2

CREATE PROCEDURE [dbo].[spSV_WAR_GetTaskComments]
@RepairOrderTaskID int
AS

/*********************************************************************************************************
DESCRIPTION
This procedure retrieves the complaint, cause, and correction comments for the selected RepairOrderTaskID

MODIFICATION HISTORY
2006-12-11    Dave Campbell
    - Created.
2007-01-29    Dave Campbell
    - Renamed to be non-OEM specific.
2010-04-15    Dave Campbell (CFS 1040526)
    - Split into separate SELECT statements so that text from multiple 
      entity comment records can be concatenated, if necessary.
 
--------------------------
RETURNS:
-------------------------
A recordset containing the complaint, cause, and correction comments for the selected RepairOrderTaskID

**********************************************************************************************************/

SET NOCOUNT ON

BEGIN

DECLARE @Complaint varchar(7500),
        @Cause varchar(7500),
        @Correction varchar(7500)

SELECT @Complaint = 
    CASE 
        WHEN @Complaint IS NULL THEN Comment
        ELSE @Complaint + CHAR(13) + CHAR(10) + Comment
    END
FROM dbo.EntityComment EC1 
INNER JOIN dbo.Comment C1 ON EC1.CommentID = C1.CommentID 
WHERE EC1.EntityID = @RepairOrderTaskID AND EC1.CommentTablesID = 32

SELECT @Cause = 
    CASE 
        WHEN @Cause IS NULL THEN Comment
        ELSE @Cause + CHAR(13) + CHAR(10) + Comment
    END
FROM dbo.EntityComment EC1 
INNER JOIN dbo.Comment C1 ON EC1.CommentID = C1.CommentID 
WHERE EC1.EntityID = @RepairOrderTaskID AND EC1.CommentTablesID = 31

SELECT @Correction = 
    CASE 
        WHEN @Correction IS NULL THEN Comment
        ELSE @Correction + CHAR(13) + CHAR(10) + Comment
    END
FROM dbo.EntityComment EC1 
INNER JOIN dbo.Comment C1 ON EC1.CommentID = C1.CommentID 
WHERE EC1.EntityID = @RepairOrderTaskID AND EC1.CommentTablesID = 33

SELECT 
    ISNULL(@Complaint, '') AS Complaint,
    ISNULL(@Cause, '') AS Cause,
    ISNULL(@Correction, '') AS Correction

END

SET NOCOUNT OFF

 

7.临时表

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Jimmy
-- Create date: 2011-11-22
-- Description:    <all available branches that the user privilieges>
-- =============================================
create PROCEDURE spIN_PIN_GetUserPriviegesBranch
    -- Add the parameters for the stored procedure here
    @userLoginID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    --SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    
    declare @adminDivision bit,@adminCompany bit,@adminEnterprise bit,@branchID int
    
    DECLARE @branchs TABLE (
 RowID int IDENTITY(1, 1), 
 BranchID int,
 BranchCode varchar(50),
 DivisionID int,
 CompanyID int ,
 EnterpriseID int
)
    

INSERT INTO @branchs (BranchID, BranchCode, DivisionID,CompanyID,EnterpriseID)
    select  
            Branch.BranchID, 
            Branch.BranchCode,
            Branch.DivisionID,
            Company.CompanyID,
            Enterprise.EnterpriseID
        from Branch 
        left join Division  on Division.DivisionID=Branch.DivisionID
        left join Company   on Company.CompanyID=Division.CompanyID
        left join Enterprise on Enterprise.EnterpriseID=Company.EnterpriseID
        WHERE Branch.Inactive = 0 
    
    select 
        @adminDivision=IsAdminBranchDivision,
        @adminCompany=IsAdminBranchCompany,
        @adminEnterprise=IsAdminBranchEnterprise,
        @branchID=UserLogin.BranchID
    from UserLogin 
    where UserLoginID=@userLoginID--
    

    if @adminEnterprise=1 
        begin
        declare @enterpriseID int
        select @enterpriseID=EnterpriseID from @branchs where BranchID=@branchID
        select BranchID,BranchCode from @branchs where EnterpriseID=@enterpriseID
        end
    else if @adminCompany=1
        begin
            declare @companyID int
            select @companyID=CompanyID from @branchs where BranchID=@branchID
            select BranchID,BranchCode from @branchs where CompanyID=@companyID
        end
     else if @adminDivision=1
        begin
            declare @division int
            select @division=DivisionID from @branchs where BranchID=@branchID
            select BranchID,BranchCode from @branchs where DivisionID=@division
        end
    else
         select BranchID,BranchCode from @branchs where BranchID=@branchID
    
END
GO