SQL一些小技巧

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

ThesehasbeenpickedupfromthreadwithinSQLjunkiesForumshttp://www.SQLjunkies.com

Problem
TheproblemisthatIneedtorounddifferently(byhalves)
Example:4.24roundsto4.00,but4.26roundsto4.50.
4.74roundsto4.50and4.76roundsto5.00

Solution
declare@tfloat
set@t=100.74
selectround(@t*2.0,0)/2

Problem
I'mwritingafunctionthatneedstotakeinacommaseperatedlistandusitinawhereclause.Theselectwouldlooksomethinglikethis:

select*frompeoplewherefirstnamein('larry','curly','moe')

Solution
usenorthwind
go

declare@xVarvarchar(50)
set@xVar='anne,janet,nancy,andrew,robert'

select*fromemployeeswhere@xVarlike'%'+firstname+'%'


Problem
NeedasimplepagingSQLcommand

Solution
usenorthwind
go

select*fromproductsa
where(selectcount(*)fromproductsbwherea.productid>=b.productid)between15and16


Problem
Performcase-sensitivecomparisionwithinSQLstatementwithouthavingtousetheSETcommand

Solution

usenorhtwind
go

SELECT*FROMproductsASt1
WHEREt1.productnameCOLLATESQL_EBCDIC280_CP1_CS_AS='Chai'

--executethiscommandtogetdifferentcollatenaming
--select*from::fn_helpcollations()

 

Problem
Howtocallastoredprocedurelocatedinadifferentserver

Solution

SETNOCOUNTON
usemaster
go

EXECsp_addlinkedserver'172.16.0.22',N'SQLServer'
go

Execsp_link_publication@publisher='172.16.0.22',
@publisher_db='Northwind',
@publication='NorthWind',@security_mode=2,
@login='sa',@password='sa'
go

EXEC[172.16.0.22].northwind.dbo.CustOrderHist'ALFKI'
go

execsp_dropserver'172.16.0.22','droplogins'
GO

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>