随笔分类 -  SQL Server Technical Issues

sharing the skills about writing SQL Scripts, Store Procedure..
Process multi-row data with cursor or without cursor[利用游标或者非游标处理多行数据]
摘要：Implement functionality using Cusor /* decription: import data from BomIntraplantRoute_Buckets of ofg_response_staging into ds_in_bom_intraplant_route_bucket of OFG_SLV_DEV created by: Winstion He on... 阅读全文

posted @ 2008-05-07 21:14 飞天舞者 阅读(410) 评论(0) 推荐(0) 编辑

A store procedure sample using caes ...when ...then...
摘要：create proc Pr_ToSolver_ds_in_item_loc as if exists(select * from OFG_SLV_DEV3.dbo.ds_in_item_loc) delete from OFG_SLV_DEV3.dbo.ds_in_item_loc insert into OFG_SLV_DEV3.dbo.ds_in_item_loc ( ... 阅读全文

posted @ 2008-05-02 13:05 飞天舞者 阅读(288) 评论(0) 推荐(0) 编辑

difference between Truncate and Delete
摘要：一、delete产生rollback，如果删除大数据量的表速度会很慢，同时会占用很多的rollback segments .truncate 是DDL操作，不产生rollback，速度快一些. Truncate table does not generate rollback information and redo records so it is much faster than d... 阅读全文

posted @ 2008-03-03 14:45 飞天舞者 阅读(277) 评论(0) 推荐(0) 编辑

some tips about sys.objects table
摘要：all database objects are stored in sys.objects table. they can be classified as following type: U ==> normal table V ==> View S ==> System table P ==> Store Procedure ... for example, if we 'd like t... 阅读全文

posted @ 2008-01-25 14:37 飞天舞者 阅读(161) 评论(0) 推荐(0) 编辑

just some skills for SQL
摘要：1 about the name regulation for table,store procedure,view .. normally, each different kind of object should be named with specific flag, such as table named as Tab_*, store procedure named ... 阅读全文

posted @ 2008-01-22 16:55 飞天舞者 阅读(221) 评论(0) 推荐(0) 编辑

execute sql query dynamically
摘要：eg: create proc pr_test @table varchar(128), @database varchar(128) as declare @str1 varchar(128) declare @str2 varchar(128) set @str2=' select * from '+@database+'.dbo.'+@table exec (@str1+@str2) 阅读全文

posted @ 2008-01-17 15:57 飞天舞者 阅读(275) 评论(0) 推荐(0) 编辑

insert/process multi-records without cursor
摘要：if object_id('Pr_ToSolver_ds_in_item_group_loc_period') is not null drop proc Pr_ToSolver_ds_in_item_group_loc_period create proc Pr_ToSolver_ds_in_item_group_loc_period as insert into ds_in_ite... 阅读全文

posted @ 2008-01-17 11:34 飞天舞者 阅读(217) 评论(0) 推荐(0) 编辑

how to judge if the temptable or normal table that you created exists
摘要：just as the following scripts shows: 1.temp table if exists (select * from sysobjects where objectproperty(object_id('TempTableName'),'istable') = 1) 2.normal table if object_id('tablename') i... 阅读全文

posted @ 2008-01-17 09:20 飞天舞者 阅读(184) 评论(0) 推荐(0) 编辑

simple =, join,left out join,right outer join, cross join
摘要：a. = eg. Select A.a B.b from A, B where A.a=B.a and A.c=’herengang’; under this condition, it only shows the data that A.a=B.a and A.c=”herengang”. Although it there is data wh... 阅读全文

posted @ 2008-01-16 11:18 飞天舞者 阅读(278) 评论(0) 推荐(0) 编辑

How to receive another procedure result in one procedure.
摘要：The grammar is following, eg: declare @result int exec @result=sp_userTest select @result //statements: sp_userTest is a procedure,which is following create procedure sp_userTest As Select * from aut... 阅读全文

posted @ 2008-01-16 11:13 飞天舞者 阅读(247) 评论(0) 推荐(0) 编辑

how to call the procedure in c# program
摘要：To call the procedure, it need the followng steps open the connection of database. open the commander , at the same time , we must set the command type be storeprocedure. Of course, opening the comman... 阅读全文

posted @ 2008-01-16 11:11 飞天舞者 阅读(303) 评论(0) 推荐(0) 编辑

get all table's information concerned
摘要：1 get all columnname select [name] from syscolums where id=object_id('tablename) 2 get all tables select [name] from SysObjects where type='U' 3 get all datatype of system select [xtype],[name] from ... 阅读全文

posted @ 2008-01-16 11:08 飞天舞者 阅读(177) 评论(0) 推荐(0) 编辑

create procedure grammar
摘要：Create procedure pre_name @variable list as pro_doby Eg. Create procedure sp_GetUserInformation @userid char(15)=’00001’, @username char(2) output, //which means username is outout @addr... 阅读全文

posted @ 2008-01-16 11:04 飞天舞者 阅读(329) 评论(0) 推荐(0) 编辑

导航

< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
For more information about me, feel free email to me winston.he@hotmail.com
点击右上角即可分享
微信分享提示