SQL Server的SQL语句

1. 重新复位表的标识值，就是复位表自增长的主键的起始值

truncate table TableName

 

2. nvarchar中文字符的like匹配，加 N

select * from table1 where column1 like N'%王%'

 

 

3. 存储过程使用动态的表名操作数据

有张表:

CREATE TABLE [dbo].[Table_1](
 [c0] [int] IDENTITY(1,1) NOT NULL,
 [c1] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 [c2] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
 [c0] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

第一种使用方法，直接写语句执行：

CREATE PROCEDURE [dbo].[TABLE_INSERT_V1] (
  @postfix NVARCHAR(32),
  @p1 NVARCHAR(50),
  @p2 NVARCHAR(50)
 )
 AS
DECLARE @SQLString nvarchar(1000), @ParmDefinition nvarchar(200)

SET @SQLString = N'INSERT INTO [dbo].[Table_' + @postfix + N'] ([c1],[c2]) VALUES ( '''+@p1+''','''+@p2+''')'

EXEC SP_EXECUTESQL @SQLString
SELECT @@Identity AS sqlID

第二种方法，直接写语句，通过输入参数执行：

CREATE PROCEDURE [dbo].[TABLE_INSERT_V1] (
  @postfix NVARCHAR(32),
  @p1 NVARCHAR(50),
  @p2 NVARCHAR(50)
 )
 AS
DECLARE @SQLString nvarchar(1000), @ParmDefinition nvarchar(200)

SET @SQLString = N'INSERT INTO [dbo].[Table_' + @postfix + N'] ([c1],[c2]) VALUES (@_p1,@_p2)'
SET @ParmDefinition = N'@_p1 NVARCHAR(50), @_p2 NVARCHAR(50)'

EXEC SP_EXECUTESQL @SQLString, @ParmDefinition, @_p1=@p1, @_p2=@p2
SELECT @@Identity AS sqlID

第二种语句清晰，但是作为存储过程中执行的语句，我觉得没有必要再通过参数输入，也就是说参数输入与否没有任何区别，特别是在sql injection上面，只会增加执行时间。所以我建议用第一种方法。

不管用哪种方法，只要是用‘EXEC SP_EXECUTESQL’等直接执行语句的方法，那么存储过程的字符串输入参数，都必须做sqlencode。防止sql injection。

 

4 存储过程调用动态表名实例

 

实例表的名字格式：data_yyyyMM_X yyyyMM是年月 X是商户最后位取10的模所得的数字

存储过程中那个使用到的函数：

1 GetDataTableName 获取表名

CREATE FUNCTION [dbo].[GetDataTableName] (
@merchantId INT
)
RETURNS NVARCHAR(100)
BEGIN
    return cast(('[dbo].[data_' + [dbo].[GetYearMonthString]() + '_' + [dbo].[GetMerchantIdModResult](@merchantId) + ']') AS NVARCHAR)
END

2 获取表名中使用到获取时间的函数

CREATE FUNCTION [dbo].[GetYearMonthString] (
)
RETURNS NVARCHAR(6)
BEGIN
    return (convert(nvarchar(4),year(getdate()))+Right(100+Month(GetDate()),2))
END

3 获取商户最后位取模函数

CREATE FUNCTION [dbo].[GetMerchantIdModResult] (
@merchantId INT
)
RETURNS NVARCHAR
BEGIN
    return cast(right(@merchantId,1)%10 as NVARCHAR)
END

 

添加：

 

CREATE PROCEDURE Data_AddData_V1 (
 @MerchantId INT,
 @MonitorDataDefinitionId INT,
 @ProjectId INT,
 @DataTime DATETIME,
 @Data FLOAT,
 @CreateTime DATETIME,
 @Description NVARCHAR(1000)
)
AS
DECLARE @SQLString NVARCHAR(1000), @ParmDefinition NVARCHAR(200)
SET @SQLString = N'INSERT INTO '+[dbo].[GetDataTableName](@MerchantId)+
N' (
 [MerchantId],
 [MonitorDataDefinitionId],
 [ProjectId],
 [DataTime],
 [Data],
 [CreateTime],
 [Description])
VALUES (
 @_MerchantId,
 @_MonitorDataDefinitionId,
 @_ProjectId,
 @_DataTime,
 @_Data,
 @_CreateTime,
 @_Description
)'
SET @ParmDefinition = N'@_MerchantId INT,@_MonitorDataDefinitionId INT,@_ProjectId INT,@_DataTime DATETIME,@_Data FLOAT,@_CreateTime DATETIME,@_Description NVARCHAR(1000)'
EXEC SP_EXECUTESQL @SQLString, @ParmDefinition,
 @_MerchantId=@MerchantId,
 @_MonitorDataDefinitionId=@MonitorDataDefinitionId,
 @_ProjectId=@ProjectId,
 @_DataTime=@DataTime,
 @_Data=@Data,
 @_CreateTime=@CreateTime,
 @_Description=@Description
SELECT @@Identity AS Id
GO

 

修改：

CREATE PROCEDURE Data_UpdateData_V1 (
 @Id INT,
 @MerchantId INT,
 @MonitorDataDefinitionId INT,
 @ProjectId INT,
 @DataTime DATETIME,
 @Data FLOAT,
 @CreateTime DATETIME,
 @Description NVARCHAR(1000)
)
AS
DECLARE @SQLString NVARCHAR(1000), @ParmDefinition NVARCHAR(200)
SET @SQLString = N'UPDATE '+[dbo].[GetDataTableName](@MerchantId)+N'
SET
 MerchantId= @_MerchantId,
 MonitorDataDefinitionId = @_MonitorDataDefinitionId,
 ProjectId = @_ProjectId,
 DataTime = @_DataTime,
 Data = @_Data,
 CreateTime = @_CreateTime,
 Description = @_Description
WHERE
 Id = @_Id'
SET @ParmDefinition = N'@_MerchantId INT,@_MonitorDataDefinitionId INT,@_ProjectId INT,@_DataTime DATETIME,@_Data FLOAT,@_CreateTime DATETIME,@_Description NVARCHAR(1000)'
EXEC SP_EXECUTESQL @SQLString,@ParmDefinition,
 @_MerchantId=@MerchantId,
 @_MonitorDataDefinitionId=@MonitorDataDefinitionId,
 @_ProjectId=@ProjectId,
 @_DataTime=@DataTime,
 @_Data=@Data,
 @_CreateTime=@CreateTime,
 @_Description=@Description
GO

 

查询：

1 只查询 指定Id和商户Id的数据

CREATE PROCEDURE Data_GetDataByIdAndMerchantId_V1 (
 @MerchantId INT,
 @Id INT
)
AS
DECLARE @SQLString NVARCHAR(1000), @ParmDefinition NVARCHAR(200)
SET @SQLString = N'SELECT * FROM '+[dbo].[GetDataTableName](@MerchantId)+N' WHERE [Id]=@_Id'
SET @ParmDefinition = N'@_Id INT'
EXEC SP_EXECUTESQL @SQLString, @ParmDefinition, @_Id=@Id
GO

 

2 查询指定商户Id所有数据

CREATE PROCEDURE Data_GetAllDatasByMerchantId_V1(
 @MerchantId INT
)
AS
DECLARE @SQLString NVARCHAR(1000), @ParmDefinition NVARCHAR(200)
SET @SQLString = N'SELECT * FROM '+[dbo].[GetDataTableName](@MerchantId)+N' WHERE [MerchantId]=@_MerchantId'
SET @ParmDefinition = N'@_MerchantId INT'
EXEC SP_EXECUTESQL @SQLString, @ParmDefinition, @_MerchantId=@MerchantId
GO

 

删除：

CREATE PROCEDURE Data_DeleteDataByIdAndMerchantId_V1 (
 @MerchantIdINT
 @Id INT
)
AS
DECLARE @SQLString NVARCHAR(1000), @ParmDefinition NVARCHAR(200)
SET @SQLString = N' DELETE FROM '+[dbo].[GetDataTableName](@MerchantId)+N' WHERE Id=@_Id'
SET @ParmDefinition = N'@_Id INT'
EXEC SP_EXECUTESQL @SQLString,@ParmDefinition,@_Id=@Id
GO

--------------------------------------------------------------------------------------------------------

得到一个时间的一天的开始时间，得到一个时间所在周的Monday的开始时间，得到一个时间所在月的开始时间

DECLARE @dt SMALLDATETIME
SET @dt = getdate()

DECLARE @day SMALLDATETIME

--得到一个时间的一天的开始时间
SET @day = CAST(
 (
 CAST(DATEPART(YEAR, @dt) as nvarchar(4)) + '-' +
 CAST(DATEPART(MONTH, @dt) as nvarchar(2)) + '-' +
 CAST(DATEPART(DAY, @dt) as nvarchar(2)) + ' 0:00:00'
 )
 AS SMALLDATETIME)

--得到一个时间所在周的Monday的开始时间

DECLARE @firstWeekday SMALLDATETIME
SET @firstWeekday = DATEADD(DAY, -DATEPART(WEEKDAY, @day)+2, @day)

--得到一个时间所在月的开始时间

DECLARE @firstMonthday SMALLDATETIME

SET @firstMonthday = CAST(
 (
 CAST(DATEPART(YEAR, @dt) as nvarchar(4)) + '-' +
 CAST(DATEPART(MONTH, @dt) as nvarchar(2)) + '-' +
 '01 0:00:00'
 )
 AS SMALLDATETIME)

print @day
print @firstWeekday

print @firstMonthday

 --------------------------------------------------------------------------------------------------------

 

计算及格式转换的函数，AVG需要GROUP BY支持

"AVG" = CONVERT (DECIMAL(8, 2), ROUND((AVG(value) / 1000.00), 2)),

--------------------------------------------------------------------------------------------------------

 

SQL 函数： 在一个字符串中查找以字符串1开始字符串2结束中的字符串。
CREATE  FUNCTION [dbo].[StringSearch] (
@string NVARCHAR(1024),
@startString NVARCHAR(256),
@endString NVARCHAR(256)
)
RETURNS NVARCHAR(512)
BEGIN
    declare @returnVal NVARCHAR(512)
    SET @returnVal = SUBSTRING(@string,
    CHARINDEX(@startString, @string) + LEN(@startString),
    CHARINDEX(@endString, @string)-CHARINDEX(@startString, @string) -  LEN(@startString))
    return @returnVal
END

 

使用方法：

dbo.StringSearch(xmlString, '<Root>', '</Root>')

--------------------------------------------------------------------------------------------------------