SQL 一些小技巧

These has been picked up from thread within sqljunkies Forums http://www.sqljunkies.com

Problem
The problem is that I need to round differently (by halves)
Example: 4.24 rounds to 4.00, but 4.26 rounds to 4.50.
4.74 rounds to 4.50 and 4.76 rounds to 5.00

Solution
declare @t float
set @t = 100.74
select round(@t * 2.0, 0) / 2

Problem
I@#m writing a function that needs to take in a comma seperated list and us it in a where clause. The select would look something like this:

select * from people where firstname in (@#larry@#,@#curly@#,@#moe@#)

Solution
use northwind
go

declare @xVar varchar(50)
set @xVar = @#anne,j.net,nancy,andrew, robert@#

select * from employees where @xVar like @#%@# + firstname + @#%@#


Problem
Need a simple paging sql command

Solution
use northwind
go

select * from products a
where (select count(*) from products b where a.productid >= b.productid) between 15 and 16


Problem
Perform case-sensitive comparision within sql statement without having to use the SET command

Solution

use norhtwind
go

SELECT * FROM products AS t1
WHERE t1.productname COLLATE SQL_EBCDIC280_CP1_CS_AS = @#Chai@#

--execute this command to get different collate naming
--select * from ::fn_helpcollations()

 

Problem
How to call a stored procedure located in a different server

Solution

SET NOCOUNT ON
use master
go

EXEC sp_addlinkedserver @#172.16.0.22@#,N@#Sql Server@#
go

Exec sp_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

exec sp_dropserver @#172.16.0.22@#, @#droplogins@#
GO

原文转自：http://www.ltesting.net