Gold award from Technet Guru Comptetion April 2016

Thanks Microsoft for Gold award from

Technet Guru Comptetition April 2016
april 2016 guruhttps://blogs.technet.microsoft.com/wikininjas/2016/05/20/technet-guru-competition-april-2016-results/

Реклама
Gold award from Technet Guru Comptetion April 2016

Sql Server: Using Parameterized Functions

One Of Transact Sql Forum Topic MSDN i was see
Problems using functions in sql server

(link is that
How to execute dynamic sql from function Jump (MSDN Transact Sql Forum)
)

and i was think about this solutions

actually in sql server have limitations in functions

one of the limitations of custom functions
with parameter can not be transferred name of the table function

for example we have 20 tables that we need to work 20 functions
for this problems i was think about » xp_exec»

for example first i will created table and dynamically will select columns from this table

use tempdb
go
create table test
(t1 int)
go
insert test values(1)
insert test values(2)
insert test values(2)
insert test values(3)
insert test values(3)
insert test values(4)
insert test values(5)
insert test values(8)

go

and then we will create a function
for given results dynamically

drop function showfunction
go
create function dbo.showfunction(@tablename sysname,@colname sysname)
returns @showtable table (showresult sql_variant)
as
begin
declare @stringsql varchar(8000)
set @stringsql=’
Create Function dbo.showprim()
Returns @showtab Table(showres sql_variant)
as
begin
insert @showtab
Select showres=AVG(t1) from (
select min(t1) as t1 from (
select top 50 percent ‘+@colname+’ as t1 from ‘+@tablename+’ order by t1 desc
)t
union all
select max(t1) from (
select top 50 percent ‘+@colname+’ as t1 from ‘+@tablename+’ order by t1
)t
)M
Return
end

exec master..xp_exec ‘Drop Function Dbo.showprim’,’N’,’tempdb’
exec master..xp_exec @stringsql,’N’,’tempdb’
Insert @showtable Select*from showprim()
return
end
go

select* from showfunction(‘test’,’t1′)
go
drop table test

showresult
—————-
3

and results was success

You can also see this article in msdn too:
https://social.technet.microsoft.com/wiki/contents/articles/34172.sql-server-using-parameterized-functions.aspx

Sql Server: Using Parameterized Functions

SQL SERVER:Using Datalength() function

Sometimes i see in msdn forums
and in real process problems about string columns

in this theme i will use Datalength() function

Datalength() function returns the length of the data
in the column in contrast to the length of the column

This function we can use in string and another (BLOB) type columns too

in this example I will divide the a long string with Datalength() function

first we will created table for using

—first will created table

create table #array (k1 int identity,arraycol varchar(8000))

—now will inserted long value

insert #array(arraycol) values(‘LES PAUL ‘+
‘BUDDY GUY ‘+
‘JEFF BECK ‘+
‘JOE SATRIANI ‘)
insert #array(arraycol) values(‘STEVE NILLER ‘+
‘EDDIE VAN HALEN ‘+
‘TOM SCHOLZ ‘+
‘JOE SATRIANI ‘)
insert #array(arraycol) values(‘STEVE VAI ‘+
‘ERIC CLAPTON ‘+
‘SLASH ‘+
‘JIMI HENDRIX ‘+
‘JASON BECKER ‘+
‘MICHAEL HARTMAN’)

—and select our example table

select*from #array

k1 arraycol
1 LES PAUL BUDDY GUY JEFF BECK JOE SATRIANI
2 STEVE NILLER EDDIE VAN HALEN TOM SCHOLZ JOE SATRIANI
3 STEVE VAI ERIC CLAPTON SLASH JIMI HENDRIX JASON BECKER MICHAEL HARTMAN

and now i will use Datalength() function for divide long string column

update #array
set arraycol =
LEFT(arraycol,(3*15))+’MUDDY WATERS ‘+
RIGHT(arraycol,case when (datalength(arraycol)-(4*15))<0 then 0 else
datalength(arraycol)-(4*15) end )
where k1=2
select
ELEMENT1=SUBSTRING(arraycol,(0*15)+1,15),
ELEMENT2=SUBSTRING(arraycol,(1*15)+1,15),
ELEMENT3=SUBSTRING(arraycol,(2*15)+1,15),
ELEMENT4=SUBSTRING(arraycol,(3*15)+1,15),
ELEMENT5=SUBSTRING(arraycol,(4*15)+1,15),
ELEMENT6=SUBSTRING(arraycol,(5*15)+1,15)
from #array

ELEMENT1 ELEMENT2 ELEMENT3 ELEMENT4 ELEMENT5 ELEMENT6
LES PAUL BUDDY GUY JEFF BECK J OE SATRIANI
STEVE NILLER ED DIE VAN HALEN T OM SCHOLZ JOE S MUDDY WATERS
STEVE VAI ERIC CLAPTON SLASH J IMI HENDRIX JAS ON BECKER MICHA EL HARTMAN

and result was success

you can also see this article in msdn too:SQL SERVER:Using Datalength() function

SQL SERVER:Using Datalength() function

Creating Pivot table with cursor dynamically

One of the interesting topics in SQL Server are Pivot tables, but in practice time for many programmers this topic sometimes is difficult or
some programmers cannot write grammar code for this problems. In this article I will show techniques for creating pivot tables with a cursor dynamically. First, we need table, but I will create table with default inserting rand() price from system.

create table #pivot
(col1 int,
col2 int,
value1 decimal(6,2) default(
(case (cast(rand()+.5 as int)*-1) when 0 then 1
else -1 end)*(convert(int,rand()*100000)%10000)*rand()
)
)

and then inserting data to #pivot table

insert #pivot (col1,col2) values(1,1)
insert #pivot (col1,col2) values(1,2)
insert #pivot (col1,col2) values(1,3)
insert #pivot (col1,col2) values(1,4)
insert #pivot (col1,col2) values(1,5)
insert #pivot (col1,col2) values(1,6)
insert #pivot (col1,col2) values(2,1)
insert #pivot (col1,col2) values(2,2)
insert #pivot (col1,col2) values(2,3)
insert #pivot (col1,col2) values(2,4)
insert #pivot (col1,col2) values(2,5)
insert #pivot (col1,col2) values(2,6)
insert #pivot (col1,col2) values(2,7)
insert #pivot (col1,col2) values(3,1)
insert #pivot (col1,col2) values(3,2)
insert #pivot (col1,col2) values(3,3)

with this code we was inserting to col1,col2 column #pivot table

column value1 was inserted from system with rand() price

select * from #pivot

col1 col2 value1
———— ———— —————————————
1 1 1014.31
1 2 33.27
1 3 6342.69
1 4 85.21
1 5 2607.97
1 6 -3432.93
2 1 -1721.79
2 2 -4844.58
2 3 -582.77
2 4 -259.22
2 5 1735.89
2 6 -1549.44
2 7 2482.01
3 1 -719.52
3 2 -5258.93
3 3 -1335.91

(16 row(s) affected)

and int next step for this data we will creating pivot table with cursor dynamically

declare s cursor
for
select distinct col2 from #pivot order by col2
declare @col2 int,@col2str varchar(10),@sql varchar(8000)
open s
fetch s into @col2
set @sql=»
while (@@fetch_status=0)begin
set @col2str=cast(@col2 as varchar)
set @sql=@sql+’,sum(case when col2=’ + @col2str+’then value1
else null end) [‘+@col2str+’]’
fetch s into @col2
end

set @sql=’select col1’+@sql+’ from #pivot group by col1′
exec(@sql)

close s
deallocate s
drop table #pivot

col1 1 2 3 4 5 6 7
——— ——— ————————————————- ———————
1 3305.19 -302.17 1625.63 -1042.23 -4998.37 -1936.90 NULL
2 -4784.91 5696.81 1792.75 2492.30 -1279.02 -6012.10 1551.19
3 -793.50 2432.62 -1121.00 NULL NULL NULL NULL

and result was success.

you can also can see this aritcle in msdn too:

https://social.technet.microsoft.com/wiki/contents/articles/37850.creating-pivot-table-with-cursor-dynamically.aspx

Creating Pivot table with cursor dynamically