Some small tips for SQL

Skills
These has been picked up from thread within Sqljunkies forums

Problem
The problem is and 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 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,janet,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 the products B where A.productid >= B.productid) between and 16


Problem
Perform case-sensitive comparision within SQL statement without have 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
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