· 8 years ago · May 28, 2018, 12:02 PM
1declare @empname as nvarchar(61)
2set @empname = ( select firstname + ' ' + lastname
3from Employees
4where EmployeeID = 3);
5print @empname
6select @empname as empname;
7go
8
9declare @i as int
10set @i = 10
11select @i
12
13declare @firstname as nvarchar(20), @lastname as nvarchar(40);
14set @firstname = (select firstname from Employees where EmployeeID = 3);
15set @lastname = (select lastname from Employees where EmployeeID = 3);
16select @firstname as fristname, @lastname as lastname;
17
18declare @firstname as nvarchar(20), @lastname as nvarchar(40);
19select
20@firstname = firstname,
21@lastname = lastname
22from Employees
23where EmployeeID = 3
24select @firstname as fristname, @lastname as lastname;
25
26declare @empname as nvarchar(61);
27select @empname = firstname + N' '+lastname
28from Employees
29where ReportsTo = 2;
30select @empname as empname;
31
32--bendzie blond
33declare @empname as nvarchar(61)
34set @empname = (select firstname + N' ' + lastname
35from Employees
36where ReportsTo = 2)
37select @empname as empname;
38
39--blond syntax
40print '1'
41use Northwind
42go
43print'2'
44select CustomerID from Customers
45select OrderID fom Orders
46go
47print'3'
48select EmployeeID from Employees
49go
50
51--blond
52declare @i as int = 10
53print @i
54go
55print @i
56go
57
58--instrukcje ktrocy nie mozna lonczyc
59drop view if exists MyView;
60go
61create view MyView(a1,a2)
62as
63select year(Orderdate), count(*)
64from orders
65group by year(orderdate)
66--nie mozna oreder by, chyba ze damy procenty w selecit
67go
68select * from MyView where a1 = 1996
69
70drop table if exists dbo.T1;
71create table dbo.T1(col1 INT)
72go
73alter table dbo.t1 add col2 int
74go
75select col1, col2 from dbo.t1
76go
77
78--tworzymy tabelete t1 z kolum,na identity
79drop table if exists dbo.t1
80create table dbo.t1(col1 int identity constraint pk_t1 primary key)
81go
82--pomin wyswietlanie komuniaktow
83set nocount on
84go
85--wykonaj plik wsadowy 100razy
86insert into dbo.t1 default values
87go 100
88select count(*) from t1
89
90if year(sysdatetime()) <> year(dateadd(day,1,sysdatetime()))
91print 'asd'
92else
93print 'dsa'
94go
95
96if year(sysdatetime()) <> year(dateadd(day,1,sysdatetime()))
97print 'asd'
98else if
99month(sysdatetime()) <> month(dateadd(m,1,sysdatetime()))
100print 'qwe'
101else
102print 'zxc'
103go
104
105if day(sysdatetime()) = 1
106begin
107print 'qwe'
108print 'asd'
109print 'zxc'
110end
111else
112begin
113print '123'
114print '434'
115print '654'
116end
117go
118
119declare @i as int = 1;
120while @i <= 10
121begin
122print @i
123set @i = @i+1
124end
125go
126
127declare @i as int = 1
128while @i <= 10
129begin if @i = 6 break
130print @i
131set @i = @i + 1
132end
133go
134
135declare @i as int = 0
136while @i<10
137begin
138set @i = @i + 1
139if @i = 6 continue
140print @i
141end
142go
143
144set nocount on
145drop table if exists dbo.Numbers;
146create table dbo.Numbers( n int not null primary key)
147declare @i as int = 1
148while @i <= 100
149begin
150insert into dbo.Numbers VALUES(@i)
151set @i = @i + 1
152end
153go
154select * from dbo.Numbers
155
156drop table if exists #MyTable
157go
158create table #MyTable(
159id int not null primary key,
160num int not null
161)
162insert into #MyTable(id,num) values (1,1),(2,2),(3,3)
163select * from #MyTable
164go
165
166create table ##MyTable(
167id int not null primary key,
168num int not null
169)
170
171insert into ##MyTable(id,num) values(1,1),(2,2),(3,3)
172select * from ##MyTable
173go
174
175declare @MyTable table(
176id int not null primary key,
177num int not null
178)
179
180insert into @MyTable(id,num) values(1,1),(2,2),(3,3)
181select * from @MyTable
182go
183
184drop table if exists dbo.MyType;
185create type dbo.MyType as table(
186id int not null primary key,
187num int not null
188)
189go
190declare @a1 as dbo.MyType
191insert into @a1(id,num) values(1,1),(2,2),(3,3)
192select * from @a1
193go
194
195declare @sql as varchar(100)
196set @sql = 'print ''komunikat'';';
197exec(@sql)
198go
199
200--nie dziala nie wiem dalczego
201declare @sql as nvarchar(100)
202set @sql = 'select orderid, orderdate
203from orders
204where orderid = @orderid;';
205exec sys.sp_executesql
206@stmt = @sql,
207@params = '@orderid as int',
208@orderid = 10248
209go
210
211begin try
212print 10/2
213print 'no error'
214end try
215begin catch
216print 'error'
217end catch
218go
219
220begin try
221print 10/0
222print 'no error'
223end try
224begin catch
225print 'error'
226end catch
227go
228
229DROP TABLE IF EXISTS dbo.Employees1;
230CREATE TABLE dbo.Employees1
231(
232 empid INT NOT NULL,
233 empname VARCHAR(25) NOT NULL,
234 mgrid INT NULL,
235 CONSTRAINT PK_Employees1 PRIMARY KEY(empid),
236 CONSTRAINT CHK_Employees_empid1 CHECK(empid > 0),
237 CONSTRAINT FK_Employees_Employees1
238 FOREIGN KEY(mgrid) REFERENCES dbo.Employees1(empid)
239);
240GO
241
242begin try
243insert into dbo.Employees1(empid,empname,mgrid)
244values(0,'emp1',null)
245end try
246begin catch
247if error_number() = 2627
248begin print 'handle pk vio' end
249else if error_number() = 547
250begin print 'handle check/fk constraint vio' end
251else if error_number() = 515
252begin print 'handle null vio' end
253else if error_number() = 245
254begin print 'handle conversion er' end
255else
256begin
257 print 're-throwing error';
258 throw;
259end
260print 'error number : ' + cast(error_number() as varchar(10))
261print 'error message : ' + error_message();
262print 'error severity : ' + cast(error_severity() as varchar(10))
263print 'error state : ' + cast(error_state() as varchar(10))
264print 'error line : ' + cast(error_line() as varchar(10))
265end catch
266go
267
268SET NOCOUNT ON;
269DECLARE @Result AS TABLE (a1 varchar(200));
270DECLARE
271 @A1 AS NVARCHAR(40),
272 @A2 AS MONEY,
273 @A3 AS SMALLINT;
274drop table if exists Test;
275create table Test (a1 varchar(200));
276
277DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR
278 SELECT productname, unitprice, UnitsInStock
279 FROM Products where ProductID<10
280 ORDER BY UnitPrice, UnitsInStock;
281OPEN C;
282FETCH NEXT FROM C INTO @A1, @A2, @A3;
283
284WHILE @@FETCH_STATUS = 0
285BEGIN
286 print CONCAT(@A1,' ',@A2,' ',@A3)
287 insert into test values (CONCAT(@A1,' ',@A2,' ',@A3));
288 insert into @Result values (CONCAT(@A1,' ',@A2,' ',@A3));
289 FETCH NEXT FROM C INTO @A1, @A2, @A3;
290END;
291CLOSE C;
292DEALLOCATE C;
293
294SELECT * FROM @Result
295SELECT * from Test
296GO
297
298--jakie w danej bazie som widoki i skasuje je ( kursos kasuje dane)