Using Cross Apply to Use Like Operator on delimitted string values

Declare @vTable TABLE(id INT, NAME VARCHAR(100))
INSERT INTO @vTable
    SELECT 1,'faheem' UNION ALL
    SELECT 2,'ahmad' UNION ALL
    SELECT 3,'aslam UNION ALL
    SELECT 4,'bilal' UNION ALL
    SELECT 4,'khizar'
DECLARE @vParam VARCHAR(100)
-- To check the values with LIKE operator. These are comma separated.
SET @vParam = 'fah,asl'
-- Used CROSS APPLY to accomplish the task...
SELECT * FROM @vTable
CROSS APPLY (SELECT [item] FROM dbo.fnSplit(@vParam,',')) b
WHERE NAME LIKE '%' + b.[item] + '%'

fnSplit can be created like

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
END

IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

Find who is connected to database in a current instance

in Sqlserver 2005 system stored procedure "sp_who" can be used to get information about current users and processes in an instance of the Microsoft SQL Server Database Engine. The information returned can be filtered to return only those processes that are not idle.

Using Exists in When clause

SELECT SEQ.Name AS SeqNumber,


Seq.Id,

CASE WHEN EXISTS(SELECT * FROM dbo.tblSYSASEQData WHERE IsDefault='true' AND SeqId=SEQ.Id) THEN

(SELECT Data FROM dbo.tblSYSASEQData WHERE IsDefault='true' AND SeqId=SEQ.Id) ELSE (SELECT TOP 1 Data FROM dbo.tblSYSASEQData WHERE SeqId=SEQ.Id ORDER BY SEQid) end AS SEQData

FROM dbo.tblSYSASEQAssociation SEQA

INNER JOIN dbo.tblSYSASEQ SEQ ON SEqA.SeqAssociationId=SEQ.SeqAssociationId

INNER JOIN dbo.tblSYSAUser U ON U.Id=SEQA.UserId

WHERE U.Id=120

Query to Find Missing values in a sequence

Suppose we have a table named sequence

we can write query to find missing(at least first missing) value/values in a sequence of integers like that

Suppose we have a table named sequence
SELECT SEQ1.Id+1 FROM #sequence SEQ1 LEFT OUTER JOIN #sequence SEQ2 ON SEQ1.id+1=SEQ2.id
WHERE SEQ2.id IS NULL