摘要:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_int2hex]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_int2hex]GO/*--数字转换成16进制--邹建 2004.04(引用请保留此信息)--*//*--调用示例--调用select dbo.f_int2hex(123)--*/create function f_int2hex(@num int)re 阅读全文
摘要:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_Sql]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_Sql]GOif exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') 阅读全文
摘要:
----------------------------------------------------------------------------------------------- Query Optimization Basics---------------------------------------------------------------------------------------------SELECT productModel.name as productModel, product.name as productNameFROM AdventureWor 阅读全文
摘要:
CREATE DATABASE SecurityChapterGOUse SecurityChapterGO------------------------------------------------------------- Database Security Overview; Impersonation-----------------------------------------------------------CREATE LOGIN system_admin WITH PASSWORD = 'tooHardToEnterAndNoOneKnowsIt'EXE 阅读全文
摘要:
Use tempdbGO----------------------------------------------------------------------- Precalculated Values; Sequence Tables----------------------------------------------------------------------;WITHdigits (i) as(--set up a set of numbers from 0-9 SELECT i FROM (VALUES (0),(1),(2),(3),(4), (5),(6),(7), 阅读全文
摘要:
using System;using System.Data.SqlClient;using System.Data;using System.Text;namespace NET.MST.Ninth.UseCommandBehavior{ partial class UseCommandBehavior { /// <summary> /// 测试方法 /// </summary> /// <param name="args"></param> static void Main(string[] args) { //建立连接 阅读全文
摘要:
CREATE PROC sp_PageView@tbname sysname, --要分页显示的表名@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段@PageCurrent int=1, -->0表示要显示的页码,如果为0表示仅清理缓存数据的临时表,不返回数据,其他值代表重建缓存数据的临时表@PageSize int=10, --每页的大小(记录数)@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段@Where nvarchar(1000 阅读全文
摘要:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_search]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_search]GOif exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable 阅读全文
摘要:
Use tempdbgo---------------------------------------------------------------------------------- Integer Values - Decimal Values - decimal (or numeric)--------------------------------------------------------------------------------DECLARE @testvar decimal(3,1)SELECT @testvar = -10.155555555SELECT @tes 阅读全文
摘要:
--取得随机数的视图CREATE VIEW v_RANDASSELECT re=STUFF(RAND(),1,2,'')GO--生成随机编号的函数CREATE FUNCTION f_RANDBH(@BHLen int)RETURNS varchar(50)ASBEGINDECLARE @r varchar(50)IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)SET @BHLen=10SELECT @r=CHAR(65+(SUBSTRING(re,1,1)+SUBSTRING(re,2,1)+SUBSTRING(re,3,1))%26)+CHA 阅读全文
摘要:
--测试数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',2UNION ALL SELECT 'a',3UNION ALL SELECT 'a',6UNION ALL SELECT 'a',7UNION ALL SELECT 'a',8UNION ALL SELECT 'b',1UNION ALL SELECT 'b',5UNION ALL SELECT 'b',6UNION ALL SE 阅读全文
摘要:
--测试数据CREATE TABLE tb(col1 varchar(10),col2 int)INSERT tb SELECT 'a',2UNION ALL SELECT 'a',3UNION ALL SELECT 'a',6UNION ALL SELECT 'a',7UNION ALL SELECT 'a',8UNION ALL SELECT 'b',3UNION ALL SELECT 'b',5UNION ALL SELECT 'b',6UNION ALL SE 阅读全文
摘要:
--计算两个日期之间相差的工作天数CREATE FUNCTION f_WorkDateDiff(@dt_begin datetime,@dt_end datetime)RETURNS intASBEGINDECLARE @workday int,@i int,@bz bit,@dt datetimeIF @dt_begin>@dt_endSELECT @bz=1,@dt=@dt_bsegin,@dt_begin=@dt_end,@dt_end=@dtELSESET @bz=0SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,@workday=@i/7 阅读全文
摘要:
--取得随机数的视图CREATE VIEW v_RANDASSELECT re=STUFF(RAND(),1,2,'')GO--生成随机编号的函数CREATE FUNCTION f_RANDBH(@BHLen int)RETURNS varchar(50)ASBEGINDECLARE @r varchar(50)IF NOT(ISNULL(@BHLen,0) BETWEEN 1 AND 50)SET @BHLen=10lb_bh:--生成随机编号的处理SELECT @r=re FROM v_RANDWHILE LEN(@r)<@BHLenSELECT @r=@r+re F 阅读全文
摘要:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_replace]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_replace]GO/*--非法字符替换函数去掉字符串中,小于32的字符保留 TAB char(9) 换行 char(10) 回车 char(13)如果上述字符也不需要,则将函数中的注释删除--邹建 2004.09(引用请保留此信息)--*//*--调用 阅读全文
摘要:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_FormatSTR]') and xtype in (N'FN', N'IF', N'TF'))drop function [dbo].[f_FormatSTR]GO/*--字符串格式化函数分拆指定分隔符的的字符串,并把它重新组成生成固定长度的字符串--邹建 2004.04(引用请保留此信息)--*//*--调用示例SELECT dbo.f_FormatSTR('1.1.10 阅读全文