create table probna (
id_rek integer identity (1000,1),
tekst varchar(20),
liczba numeric (8,2),
random float
)
-- zapełnianie losowymi wartosciami
ALTER PROCEDURE generuj
@ile int = 1 ,
@tabela varchar(20)
AS
DECLARE @i integer
DECLARE @los varchar(20)
DECLARE @znak varchar(1)
set @znak = char(39)
set @i =1
while @i<@ile
begin
set @los = replicate(char(rand()*65+33),round(rand()*20,0))
print 'insert into '+@tabela + ' values(' + @znak + @los + @znak+', null, null)'
execute('insert into '+@tabela + ' values(' + @znak + @los + @znak +', null, null)')
set @i=@i+1
end
-- zapełnianie liczbami z krokiem
ALTER procedure wyp_liczby
@od_ilu int,
@co_ile int
AS
BEGIN
declare @pom_liczba numeric
declare all_rec cursor for select liczba from probna for update of liczba
open all_rec
fetch all_rec into @pom_liczba
while @@FETCH_STATUS =0
begin
update probna set liczba = @od_ilu where current of all_rec
set @od_ilu = @od_ilu + @co_ile
print '>>>>>>>>>>>>>>>'
print @od_ilu
fetch all_rec into @pom_liczba
end
close all_rec
deallocate all_rec
print '>>>>>>>>>>>KONIEC<<<<<<<<<<<'
end
ALTER procedure dod_liczby
as
begin
declare @pom_liczba numeric
declare @pom_random numeric
declare @suma numeric
set @suma = 0
declare all_rec cursor for select liczba, random from probna for update of random
open all_rec
fetch all_rec into @pom_liczba, @pom_random
while @@FETCH_STATUS =0
begin
set @suma = @suma + @pom_liczba
update probna set random = @suma where current of all_rec
print '>>>>>>>>>>>>>>>'
print @suma
fetch all_rec into @pom_liczba, @pom_random
end
close all_rec
deallocate all_rec
print '>>>>>>>>>>>KONIEC<<<<<<<<<<<'
end
create procedure wtorna @liczba3 int output
as
set @liczba3 = @liczba3 *2
return
-------------------------
create procedure glowna
@liczba1 int =1, @liczba2 int
as
exec wtorna @liczba2 output
print '>>>>>>>>>>>>>'
print @liczba2
exec wtorna @liczba1 output
print '>>>>>>>>>>>>>'
print @liczba1
-- wywolanie
exec glowna 4,2
--wynik
>>>>>>>>>>>>>
4
>>>>>>>>>>>>>
8
alter procedure dynamika
as
begin
declare @pom_liczba numeric
declare @pom_random numeric
declare @poprzednia numeric
declare all_rec cursor for
select liczba, random from probna for update of random
open all_rec
fetch all_rec into @pom_liczba, @pom_random
while @@FETCH_STATUS =0
begin
set @poprzednia= @pom_liczba
fetch all_rec into @pom_liczba, @pom_random
update probna set random = @pom_liczba /@poprzednia where current of all_rec
end
close all_rec
deallocate all_rec
print '>>>>>>>>>>>KONIEC<<<<<<<<<<<'
end
alter function aa(@x int,@y int) returns int
as
begin
declare @wynik int
if @x > @y
begin
set @wynik = @x+@y
end
else
begin
set @wynik = @x-@y
end
return(@wynik)
end
ALTER procedure wez_banki @sciezka varchar(100)
as
declare @sql varchar(1000)
truncate table lista_bankow
set @sql = 'BULK INSERT mps_mis..lista_bankow FROM '''+@sciezka +'banki.txt '''
set @sql = @sql + ' with (CODEPAGE = '''+'852'+''', fieldterminator = '''+'\n'+''')'
print (@sql)
execute (@sql)
ALTER procedure wez_usr2 @plik varchar(100)
as
declare @sql varchar(1024)
truncate table pomocnicza
set @sql = 'BULK INSERT pomocnicza FROM ''' + @plik +
''' with ( firstrow = 2, fieldterminator = '''+'\t'+''', rowterminator = '''+
+'\r'+''')'
print @sql
execute (@sql)
ALTER function format_datetime
-- ***************************************************************************************
-- FUNKCJA: format_datetime
-- PRZEZNACZENIE: Formatuje znaki zgodnie z podanym wzorcem,
-- ***************************************************************************************
(@data datetime, @format varchar(10))
returns varchar(10)
as
begin
declare @ret_val varchar(10)
declare @day varchar(2)
declare @month varchar(2)
declare @year varchar(4)
set @day = day(@data)
if convert(int,@day)<=9
set @day = '0'+@day
set @month = month(@data)
if convert(int,@month)<=9
set @month = '0'+@month
set @year =year(@data)
set @ret_val = case @format
when 'dd-mm-yyyy' then (select @day+'-'+@month+'-'+@year)
when 'dd/mm/yyyy' then (select @day+'/'+@month+'/'+@year)
when 'yyyy-mm-dd' then (select @year+'-'+@month+'-'+@day)
when 'yyyy/mm/dd' then (select @year+'-'+@month+'-'+@day)
when 'mm-yyyy' then (select @month+'-'+@year)
when 'mm/yyyy' then (select @month+'/'+@year)
when 'yyyy-mm' then (select @year+'-'+@month)
when 'yyyy/mm' then (select @year+'/'+@month)
else ( select @day+'-'+@month+'-'+@year)
end
return @ret_val
end
create table auta1 (
nr_rej char(7) not null primary key,
rocznik smallint)
insert into auta1 values('aa11111',1990)
insert into auta1 values('bb22222',1996)
insert into auta1 values('cc34544',1998)
create trigger aa1
on auta1
for update
as
select * from deleted
select * from inserted
print 'OK'
update auta1 set rocznik = rocznik + 1
where nr_rej='aa11111'
--exec sp_addlogin pola2
--exec sp_helplogins 'pola2'
--exec sp_password @new='pola2', @loginame='pola2'
--deny select on osoba to wpisywacz
--grant select on osoba to wpisywacz
--exec sp_addrole wyszukiwacz
--exec sp_addrole wpisywacz
--grant insert on table1 to wpisywacz
--grant select on table1 to wyszukiwacz
--exec sp_addrolemember wyszukiwacz, guest
--exec sp_addrolemember wpisywacz, pola
--deny select on table1 to wpisywacz
1