Thursday, February 10, 2011

Style of using @variable and IN operator




Style of Using @variable and IN Operator


Using Of @variable and IN Operator in single statment



create table test (companyid int, title varchar(100), description varchar(100), keywords varchar(100))
insert into test values(1,'company a','company a','aa')
insert into test values(1,'company b','company b','bb')
insert into test values(1,'company c','company c','cc')
insert into test values(1,'company d','company d','dd')

declare @keywords varchar(100)
set @keywords = 'aa,bb,cc,dd';

WITH CTE AS (
SELECT
CAST('' + REPLACE(@keywords, ',', '') + '' AS XML) AS Keywords
)

select * from test where keywords in (
SELECT RTRIM(LTRIM(ExtractedCompanyCode.X.value('.', 'VARCHAR(256)'))) AS Keywords
FROM CTE
CROSS APPLY Keywords.nodes('//I') ExtractedCompanyCode(X)
)



Wednesday, February 2, 2011

Common Table Expressions (CTE) :-




Common Tabel Expressions (CTE)


Common Table Expressions (CTEs) were one of the most exciting features to be introduced with SQL

Server 2005


A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It

allows access to functionality within that single SQL statement that was previously only available

through use of functions, temp tables, cursors, and so on...


CTE basics:-


The concept behind a CTE is simplicity itself. Consider the following statement:


with MyCTE(x)

as

(select x='hello')

select x from MyCTE



This defines a CTE called MyCTE. In brackets after the as keyword is the query that defines the CTE.

The subsequent query references our CTE, in this case simply returning the string "hello".



Like a derived table, a CTE lasts only for the duration of a query but, in contrast to a derived

table, a CTE can be referenced multiple times in the same query. So, we now we have a way of

calculating percentages and performing arithmetic using aggregates without repeating queries or using

a temp table:


with MyCTE(x)

as

(select top 10 x = id from sysobjects)

select x, maxx = (select max(x) from MyCTE), pct =100.0 * x / (select sum(x) from MyCTE)

from MyCTE


This returns (on my system):

	x           maxx         pct 

4 2137058649 2.515723270440
5 2137058649 3.144654088050
7 2137058649 4.402515723270
8 2137058649 5.031446540880
13 2137058649 8.176100628930
15 2137058649 9.433962264150
25 2137058649 15.723270440251
26 2137058649 16.352201257861
27 2137058649 16.981132075471
29 2137058649 18.238993710691


CTE and recursion:-


More interesting, in my opinion, is the use of recursion with CTEs. The table defined in the CTE

can be referenced in the CTE itself to give a recursive expression, using union all:




with MyCTE(x)

as
(

select x = convert(varchar(8000),'hello')

union all

select x + 'a' from MyCTE where len(x) < 100

)

select x from MyCTE

order by x


The query:



select x = convert(varchar(8000),'hello')



is called the anchor member. This is executed in the first pass and will populate the CTE with the

result, in this case hello. This initial CTE is repeatedly executed until the complete result set is

returned. The next entry:



select x + 'a' from MyCTE where len(x) < 100



is a recursive member as it references the CTE, MyCTE. The recursive member is executed with the

anchor member output to give helloa. The next pass takes helloa as input and returns helloaa, and so

on so that we arrive at a CTE populated with rows as follows:


hello

helloa
helloaa
helloaaa
helloaaaa
….........