Luben Park

表名作為參數(shù)傳遞的存儲過程寫法

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

?

--=================================================================
-- 描述：～～～～～～～～～～～～～～～～～～～～～～～～～～～
-- 作者：魯湘
-- @tableName?? 該模板對應(yīng)的數(shù)據(jù)庫表
-- @RecordID??? 業(yè)務(wù)處理需要尋找表中記錄的ID號碼
--=================================================================

ALTER??? PROCEDURE WF_QingJia
? ?(@tableName varchar(50),@recordID varchar(50))
AS
-- 獲取表中的業(yè)務(wù)數(shù)據(jù)值
DECLARE @money varchar(100),? -- 業(yè)務(wù)邏輯需要的值
?@sqls nvarchar(4000)? -- 保存組合SQL語句

SET @sqls='SELECT @a=Money FROM '+@tableName +' WHERE ID ='+@recordID

EXECUTE sp_executesql @sqls,N'@a varchar(50) output',@money output

-- 根據(jù)值進行相應(yīng)的業(yè)務(wù)處理
print @money
UPDATE WF_FormBill SET [Money]='真的被處理了' WHERE [ID]=@recordID

?

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

posted @ 2006-11-16 16:49 Ben 閱讀(955) | 評論 (0) | 編輯 收藏

[轉(zhuǎn)]經(jīng)典SQL語句--收藏 http://blog.ourtw.com/article.php?tid_600.html

經(jīng)典SQL語句--收藏
[個人收藏]經(jīng)典SQL語句.值得收藏
精典的SQL語句，推薦收藏
在網(wǎng)上經(jīng)常轉(zhuǎn),常常看到有些人為了求得某些SQL語句而焦頭爛額,現(xiàn)在我特別把自己收藏的一些比較精典的SQL拿出來和大家分享一下

1. 行列轉(zhuǎn)換--普通

假設(shè)有張學(xué)生成績表(CJ)如下
Name ? Subject ? Result
張三 ? 語文 ? ? 80
張三 ? 數(shù)學(xué) ? ? 90
張三 ? 物理 ? ? 85
李四 ? 語文 ? ? 85
李四 ? 數(shù)學(xué) ? ? 92
李四 ? 物理 ? ? 82

想變成 ?
姓名 ? 語文 ? 數(shù)學(xué) ? 物理
張三 ? 80 ? 90 ? 85
李四 ? 85 ? 92 ? 82

declare @sql varchar(4000)
set @sql = ''select Name''
select @sql = @sql + '',sum(case Subject when ''''''+Subject+'''''' then Result end) [''+Subject+'']''
from (select distinct Subject from CJ) as a
select @sql = @sql+'' from test group by name''
exec(@sql)

2. 行列轉(zhuǎn)換--合并

有表A,
id pid
1 ? 1
1 ? 2
1 ? 3
2 ? 1
2 ? 2
3 ? 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1

創(chuàng)建一個合并的函數(shù)
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''''
select @str=@str+'',''+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go

--調(diào)用自定義函數(shù)得到結(jié)果
select distinct id,dbo.fmerg(id) from 表A

3. 如何取得一個數(shù)據(jù)表的所有列名

方法如下：先從SYSTEMOBJECT系統(tǒng)表中取得數(shù)據(jù)表的SYSTEMID,然后再SYSCOLUMN表中取得該數(shù)據(jù)表的所有列名。
SQL語句如下：
declare @objid int,@objname char(40)
set @objname = ''tablename''
select @objid = id from sysobjects where id = object_id(@objname)
select ''Column_name'' = name from syscolumns where id = @objid order by colid

是不是太簡單了？ 呵呵 不過經(jīng)常用阿.

4. 通過SQL語句來更改用戶的密碼

修改別人的,需要sysadmin role ?
EXEC sp_password NULL, ''newpassword'', ''User''

如果帳號為SA執(zhí)行EXEC sp_password NULL, ''newpassword'', sa

5. 怎么判斷出一個表的哪些字段不允許為空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=''NO'' and TABLE_NAME=tablename

6. 如何在數(shù)據(jù)庫里找到含有相同字段的表？
a. 查已知列名的情況
SELECT b.name as TableName,a.name as columnname
From syscolumns ? a INNER JOIN ? sysobjects b ?
ON a.id=b.id ?
AND b.type=''U'' ?
AND a.name=''你的字段名字''

b. 未知列名查所有在不同表出現(xiàn)過的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
? And o.type = ''U''
? And Exists (
? ? Select 1 From syscolumns s2 ?
? ? Where s1.name = s2.name ?
? ? And s1.id <> s2.id
? ? )

7. 查詢第xxx行數(shù)據(jù)

假設(shè)id是主鍵：
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

如果使用游標(biāo)也是可以的
fetch absolute [number] from [cursor_name]
行數(shù)為絕對行數(shù)

8. SQL Server日期計算
a. 一個月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上個月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一個星期一
select DATEADD(wk, DATEDIFF(wk,0, ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ? ? ? dateadd(dd,6-datepart(day,getdate()),getdate()) ? ?
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ), 0) ? ?
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
-----------------------------------------------------------------------
1.按姓氏筆畫排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

2.數(shù)據(jù)庫加密:
select encrypt(''原始密碼'')
select pwdencrypt(''原始密碼'')
select pwdcompare(''原始密碼'',''加密后密碼'') = 1--相同；否則不相同 encrypt(''原始密碼'')
select pwdencrypt(''原始密碼'')
select pwdcompare(''原始密碼'',''加密后密碼'') = 1--相同；否則不相同

3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+'',''+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=''表A''
set @sql=''select ''+right(@list,len(@list)-1)+'' from 表A''
exec (@sql)

4.查看硬盤分區(qū):
EXEC master..xp_fixeddrives

5.比較A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
? =
? (select checksum_agg(binary_checksum(*)) from B)
print ''相等''
else
print ''不相等''

6.殺掉所有的事件探察器進程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ''kill ''+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN(''SQL profiler'',N''SQL 事件探查器'')
EXEC sp_msforeach_worker ''?''

7.記錄搜索:
開頭到N條記錄
Select Top N * From 表
-------------------------------
N到M條記錄(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到結(jié)尾記錄
Select Top N * From 表 Order by ID Desc

8.如何修改數(shù)據(jù)庫的名稱:
sp_renamedb ''old_name'', ''new_name''

9：獲取當(dāng)前數(shù)據(jù)庫中的所有用戶表
select Name from sysobjects where xtype=''u'' and status>=0

10：獲取某一個表的所有字段
select name from syscolumns where id=object_id(''表名'')

11：查看與某一個表相關(guān)的視圖、存儲過程、函數(shù)
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like ''%表名%''

12：查看當(dāng)前數(shù)據(jù)庫中所有存儲過程
select name as 存儲過程名稱 from sysobjects where xtype=''P''

13：查詢用戶創(chuàng)建的所有數(shù)據(jù)庫
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name=''sa'')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

14：查詢某一個表的字段和數(shù)據(jù)類型
select column_name,data_type from information_schema.columns
where table_name = ''表名''

[n].[標(biāo)題]:
Select * From TableName Order By CustomerName

[n].[標(biāo)題]:
Select * From TableName Order By CustomerName

posted @ 2006-04-06 15:01 Ben 閱讀(878) | 評論 (0) | 編輯 收藏

vs2003 和vs2005下的發(fā)送SMTP郵件 （downmoon原創(chuàng)） 選擇自 downmoon 的 Blog

vs2003 和vs2005下的發(fā)送SMTP郵件 （downmoon原創(chuàng)）
一、vs2003
引用 System.Web.Mail命名空間

?private void SenMail2003()
??? {
??????? MailMessage mailObj = new MailMessage();
??????? mailObj.To = this.txtTo.Text;
??????? mailObj.From = this.txtFrom.Text;
???????
??????? mailObj.Subject = "精采笑話";
??????? mailObj.Body = "豬！你已中毒! 哈哈 ";
???????
??????? mailObj.BodyFormat = MailFormat.Html;
??????? mailObj.BodyEncoding = MailFormat.Base64;
??????? mailObj.Priority = MailPriority.High;
??????? mailObj.Attachments.Add(new MailAttachment("c:\\swf\\000.bmp"));
??????? SmtpMail.Send(mailObj);
??????? Response.Write("發(fā)送郵件成功！");
??? }

二、vs2005
引用 System.Net.Mail命名空間,安全性得到了增強

?public static void SendWebMailAndAttach(string server)
??? {
???????? string file = "e:\\inetpub\\wwwroot\\Test2005All\\TestXML\\testXML.xml";
???????? System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage("Test@126.com", "Test@126.com", "text message for you.", "Test Title");
???????? System.Net.Mail.Attachment data = new System.Net.Mail.Attachment(file, System.Net.Mime.MediaTypeNames.Application.Octet);
???????? System.Net.Mime.ContentDisposition disposition = data.ContentDisposition;
???????? disposition.CreationDate = System.IO.File.GetCreationTime(file);
???????? disposition.ModificationDate = System.IO.File.GetLastWriteTime(file);
???????? disposition.ReadDate = System.IO.File.GetLastAccessTime(file);
???????? message.Attachments.Add(data);
??????? System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient(server);
??????? client.Credentials = new NetworkCredential("用戶名", "密碼");
??????? client.Send(message);
??????? data.Dispose();
??? }
引用示例
SendWebMailAndAttach("smtp.126.com");

posted @ 2006-04-06 14:18 Ben 閱讀(465) | 評論 (0) | 編輯 收藏

SQL中CONVERT轉(zhuǎn)化函數(shù), Concat , COALESCE的用法

SQL中CONVERT轉(zhuǎn)化函數(shù)的用法

CONVERT的使用方法:

////////////////////////////////////////////////////////////////////////////////////////

格式:
CONVERT(data_type,expression[,style])

說明:
此樣式一般在時間類型(datetime,smalldatetime)與字符串類型(nchar,nvarchar,char,varchar)
相互轉(zhuǎn)換的時候才用到.

例子:
SELECT CONVERT(varchar(30),getdate(),101) now
結(jié)果為
now
---------------------------------------
09/15/2001

/////////////////////////////////////////////////////////////////////////////////////

style數(shù)字在轉(zhuǎn)換時間時的含義如下

-------------------------------------------------------------------------------------------------
Style(2位表示年份) | Style(4位表示年份) | 輸入輸出格式
-------------------------------------------------------------------------------------------------
- | 0 or 100 | mon dd yyyy hh:miAM(或PM)
-------------------------------------------------------------------------------------------------
1 | 101 | mm/dd/yy
-------------------------------------------------------------------------------------------------
2 | 102 | yy-mm-dd
-------------------------------------------------------------------------------------------------
3 | 103 | dd/mm/yy
-------------------------------------------------------------------------------------------------
4 | 104 | dd-mm-yy
-------------------------------------------------------------------------------------------------
5 | 105 | dd-mm-yy
-------------------------------------------------------------------------------------------------
6 | 106 | dd mon yy
-------------------------------------------------------------------------------------------------
7 | 107 | mon dd,yy
-------------------------------------------------------------------------------------------------
8 | 108 | hh:mm:ss
-------------------------------------------------------------------------------------------------
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)
-------------------------------------------------------------------------------------------------
10 | 110 | mm-dd-yy
-------------------------------------------------------------------------------------------------
11 | 111 | yy/mm/dd
-------------------------------------------------------------------------------------------------
12 | 112 | yymmdd
-------------------------------------------------------------------------------------------------
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24小時制)
-------------------------------------------------------------------------------------------------
14 | 114 | hh:mi:ss:mmm(24小時制)
-------------------------------------------------------------------------------------------------
- | 20 or 120 | yyyy-mm-dd hh:mi:ss(24小時制)
-------------------------------------------------------------------------------------------------
- | 21 or 121 | yyyy-mm-dd hh:mi:ss:mmm(24小時制)
-------------------------------------------------------------------------------------------------

concat方法的結(jié)果等于:result = string1 + string2 + string3 + … + stringN

COALESCE 返回其參數(shù)中第一個非空表達式

posted @ 2006-04-06 14:15 Ben 閱讀(1970) | 評論 (0) | 編輯 收藏

[轉(zhuǎn)].NET(C#)連接各類數(shù)據(jù)庫-集錦

1.C#連接連接Access
程序代碼:
-------------------------------------------------------------------------------

using System.Data;
using System.Data.OleDb;

......

string strConnection="Provider=Microsoft.Jet.OleDb.4.0;";
strConnection+=@"Data Source=C:\BegASPNET\Northwind.mdb";

OleDbConnection objConnection=new OleDbConnection(strConnection);

......

objConnection.Open();
objConnection.Close();

......

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

解釋:

　連接Access數(shù)據(jù)庫需要導(dǎo)入額外的命名空間,所以有了最前面的兩條using命令,這是必不可少的!

　strConnection這個變量里存放的是連接數(shù)據(jù)庫所需要的連接字符串,他指定了要使用的數(shù)據(jù)提供者和要使用的數(shù)據(jù)源.

　"Provider=Microsoft.Jet.OleDb.4.0;"是指數(shù)據(jù)提供者,這里使用的是Microsoft Jet引擎,也就是Access中的數(shù)據(jù)引擎,asp.net就是靠這個和Access的數(shù)據(jù)庫連接的.

　"Data Source=C:\BegASPNET\Northwind.mdb"是指明數(shù)據(jù)源的位置,他的標(biāo)準(zhǔn)形式是"Data Source=MyDrive:MyPath\MyFile.MDB".

PS:
　1."+="后面的"@"符號是防止將后面字符串中的"\"解析為轉(zhuǎn)義字符.
　2.如果要連接的數(shù)據(jù)庫文件和當(dāng)前文件在同一個目錄下,還可以使用如下的方法連接:
　　strConnection+="Data Source=";
　　strConnection+=MapPath("Northwind.mdb");
　　這樣就可以省得你寫一大堆東西了!
　3.要注意連接字符串中的參數(shù)之間要用分號來分隔.

　"OleDbConnection objConnection=new OleDbConnection(strConnection);"這一句是利用定義好的連接字符串來建立了一個鏈接對象,以后對數(shù)據(jù)庫的操作我們都要和這個對象打交道.

　"objConnection.Open();"這用來打開連接.至此,與Access數(shù)據(jù)庫的連接完成.
--------------------------------------------------------------------------------

2.C#連接SQL Server
程序代碼:
--------------------------------------------------------------------------------

using System.Data;
using System.Data.SqlClient;

...

string strConnection="user id=sa;password=;";
strConnection+="initial catalog=Northwind;Server=YourSQLServer;";
strConnection+="Connect Timeout=30";

SqlConnection objConnection=new SqlConnection(strConnection);

...

objConnection.Open();
objConnection.Close();

...

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

解釋:

連接SQL Server數(shù)據(jù)庫的機制與連接Access的機制沒有什么太大的區(qū)別,只是改變了Connection對象和連接字符串中的不同參數(shù).

首先,連接SQL Server使用的命名空間不是"System.Data.OleDb",而是"System.Data.SqlClient".

其次就是他的連接字符串了,我們一個一個參數(shù)來介紹(注意:參數(shù)間用分號分隔):
　"user id=sa":連接數(shù)據(jù)庫的驗證用戶名為sa.他還有一個別名"uid",所以這句我們還可以寫成"uid=sa".
　"password=":連接數(shù)據(jù)庫的驗證密碼為空.他的別名為"pwd",所以我們可以寫為"pwd=".
　這里注意,你的SQL Server必須已經(jīng)設(shè)置了需要用戶名和密碼來登錄,否則不能用這樣的方式來登錄.如果你的SQL Server設(shè)置為Windows登錄,那么在這里就不需要使用"user id"和"password"這樣的方式來登錄,而需要使用"Trusted_Connection=SSPI"來進行登錄.
　"initial catalog=Northwind":使用的數(shù)據(jù)源為"Northwind"這個數(shù)據(jù)庫.他的別名為"Database",本句可以寫成"Database=Northwind".
　"Server=YourSQLServer":使用名為"YourSQLServer"的服務(wù)器.他的別名為"Data Source","Address","Addr".如果使用的是本地數(shù)據(jù)庫且定義了實例名,則可以寫為"Server=(local)\實例名";如果是遠(yuǎn)程服務(wù)器,則將"(local)"替換為遠(yuǎn)程服務(wù)器的名稱或IP地址.
　"Connect Timeout=30":連接超時時間為30秒.

　在這里,建立連接對象用的構(gòu)造函數(shù)為:SqlConnection.
--------------------------------------------------------------------------------

3.C#連接Oracle
程序代碼:
--------------------------------------------------------------------------------

using System.Data.OracleClient;
using System.Data;

//在窗體上添加一個按鈕，叫Button1，雙擊Button1，輸入以下代碼
private void Button1_Click(object sender, System.EventArgs e)
{
string ConnectionString="Data Source=sky;user=system;password=manager;";//寫連接串
OracleConnection conn=new OracleConnection(ConnectionString);//創(chuàng)建一個新連接
try
{
conn.Open();
OracleCommand cmd=conn.CreateCommand();

cmd.CommandText="select * from MyTable";//在這兒寫sql語句
OracleDataReader odr=cmd.ExecuteReader();//創(chuàng)建一個OracleDateReader對象
while(odr.Read())//讀取數(shù)據(jù)，如果odr.Read()返回為false的話，就說明到記錄集的尾部了???????????????
{
Response.Write(odr.GetOracleString(1).ToString());//輸出字段1，這個數(shù)是字段索引，具體怎么使用字段名還有待研究
}
odr.Close();
}
catch(Exception ee)
{
Response.Write(ee.Message); //如果有錯誤，輸出錯誤信息
}
finally
{
conn.Close(); //關(guān)閉連接
}
}

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

4.C#連接MySQL
程序代碼:
--------------------------------------------------------------------------------

using MySQLDriverCS;

// 建立數(shù)據(jù)庫連接
MySQLConnection DBConn;
DBConn = new MySQLConnection(new MySQLConnectionString("localhost","mysql","root","",3306).AsString);
DBConn.Open();

// 執(zhí)行查詢語句
MySQLCommand DBComm;
DBComm = new MySQLCommand("select Host,User from user",DBConn);

// 讀取數(shù)據(jù)
MySQLDataReader DBReader = DBComm.ExecuteReaderEx();

// 顯示數(shù)據(jù)
try
{
while (DBReader.Read())
{
Console.WriteLine("Host = {0} and User = {1}", DBReader.GetString(0),DBReader.GetString(1));
}
}
finally
{
DBReader.Close();
DBConn.Close();
}

//關(guān)閉數(shù)據(jù)庫連接
DBConn.Close();

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

5.C#連接IBM DB2
程序代碼:
--------------------------------------------------------------------------------

OleDbConnection1.Open();
//打開數(shù)據(jù)庫連接
OleDbDataAdapter1.Fill(dataSet1,"Address");
//將得來的數(shù)據(jù)填入dataSet
DataGrid1.DataBind();
//綁定數(shù)據(jù)
OleDbConnection1.Close();
//關(guān)閉連接

//增加數(shù)據(jù)庫數(shù)據(jù)
在Web Form上新增對應(yīng)字段數(shù)量個數(shù)的TextBox，及一個button，為該按鍵增加Click響應(yīng)事件代碼如下：

this.OleDbInsertCommand1.CommandText = "INSERTsintosADDRESS(NAME,
EMAIL, AGE, ADDRESS) VALUES
('"+TextBox1.Text+"','"+TextBox2.Text+"','"+TextBox3.Text+"','"+TextBox4.Text+"')";
OleDbInsertCommand1.Connection.Open();
//打開連接
OleDbInsertCommand1.ExecuteNonQuery();
//執(zhí)行該SQL語句
OleDbInsertCommand1.Connection.Close();
//關(guān)閉連接

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

6.C#連接SyBase
程序代碼: (OleDb)
--------------------------------------------------------------------------------

Provider=Sybase.ASEOLEDBProvider.2;Initial Catalog=數(shù)據(jù)庫名;User ID=用戶名;Data Source=數(shù)據(jù)源;Extended Properties="";Server Name=ip地址;Network Protocol=Winsock;Server Port Address=5000;

posted @ 2006-04-05 14:07 Ben 閱讀(309) | 評論 (0) | 編輯 收藏

[轉(zhuǎn)帖]SQL Server - [分布式查詢/事務(wù)]

分布式查詢

posted @ 2006-03-29 16:35 Ben 閱讀(1205) | 評論 (0) | 編輯 收藏