MSSQL syntax statement collection

Each object created in the database (constraints, default values, logs, rules, stored procedures, etc.) occupies one row in the table. OnlyTempdbInner,

Each temporary object occupies a row in the table.

Select * From sysobjects where type = 'U' select the user table

 

2. Begin ...... end

3 if aa <> 3 begin ...... end

4. While () Begin ......

5. Declare @ sqlstr varchar (10)

6. Set @ sqlstr = 'delete from [dnt_topictags] Where [TID] In ('+ @ tidlist + ')'

7. If exists (Select name from sysobjects
Where name = 'proc _ calculate_taxes 'and type = 'P ')

8.
Create procedure proc_calculate_taxes (@ P1 smallint = 42, @ P2 char (1 ),
@ P3 varchar (8) = 'car ')
As
Select *
From mytable

9. Execute the Stored Procedure

Declare @ C varchar (30)
Set @ C = 'processed'
Exec @ C

 

Declare @ se varchar (100)

Set @ Se = 'select * From table1'

Exec (@ SE)

'

10 put the values of the same field together declare @ A varchar (100)

Select @ A = @ A + ',' + loginid from account
Select @

11. alter table test add Columna int not null

Alter table test drop Columna

12. Dynamic stored procedures

Create proc test (@ V1 varchar (100 ))
As
Begin
Declare @ 55 varchar (100)

Set @ 55 = 'select * From tt' + @ V1
Exec (@ 55)
End
Go

13 random fetch records

Select top 10 *, newid () as random
From TT
Order by random

 

 

Example of randomly extracting 10 records:
SQL Server: Select top 10 * from Table order by newid ()

Access: Select top 10 * from Table order by RND (ID)
In RND (ID), the ID is an automatic number field, which can be completed using any other value, for example, the name field (username)
Select top 10 * from Table order by RND (LEN (username ))

MySQL: Select * from Table order by rand () limit 10
 

14. Case... When... else... end (http://lavasoft.blog.51cto.com/62575/23373)

Update e_ztbg set jddxh = (Case isnull (jddxh, '') when'' then isnull (jddxh,'') + '2017-0001 'else isnull (jddxh ,'') + ',' + '0001-080001' end)

Where lsh = '2017-0001'

15

Select Replace ('hello', 'E', 'xxx ')