SQL Server temporary table cursor stored procedures spell SQL date functions, etc.

1,

Create temporary table
Method 1:
Create Table # temporary table name (Field 1 constraints,
Field 2 constraints,
.....)
Create Table # temporary table name (Field 1 constraints,
Field 2 constraints,
.....)
Method 2:
Select * into # temporary table name from your table;
Select * into # temporary table name from your table;
Note: The above # indicates a local temporary table, and # indicates a global temporary table.

Example: Create Table # tblrefundpaperpassengernames
(
Refundapplicationid int,
Passengernames varchar (500)
)

Query temporary tables
Select * from # temporary table name;
Select * from # temporary table name;

Delete temporary table
Drop table # temporary table name;
Drop table # temporary table name;

 

2. Definition and use of cursors

Declare @ curname cursor;

Declare @ name int; // define a cursor to define a container that holds the cursor content. The data types are the same.

Set @ curname = cursor for select X from # Table

Open @ curname

Fetch next from @ curname into @ name

While @ fetch_status = 0

Begin

************;

Fetch next from @ curname into @ name

End

Close @ curname;

Deallocate @ curname;

 

 

3,

When determining the relationship between a parameter and a field in a stored procedure, the SELECT statement must have parentheses, for example

If @ price <= (select Min (markupprice) from iorderpricelist)

4. In the stored procedure, in addition to returning sub-result sets, the dynamic SQL statement also requires some sum, cout, and other
Then, assign the out parameter to indicate the value.

Declare @ SQL nvarchar (4000 );
Declare @ sumoperate1_money;
Set @ SQL = 'select * from orders where reservationtime> ''' 2009-7-22 ''' -- simple SQL, which is much more complicated
Set @ SQL = 'select @ sumoperategion = sum (A. pricedue) from ('+ @ SQL +') as'

Execute sp_executesql
@ SQL,
N'@ sumoperate=money output ',
@ Sumoperateappsoutput;

In this way, the exec execution result is assigned to the variable, and the out type of the variable can be set at the C # End,
But I flipped through a serious error, a very low-level error,
When executing the SQL statement, the system prompts "the process requires parameter '@ statement' to be of the 'ntext/nchar/nvarchar 'type", which is confusing for a long time,
After Google, I learned that it refers to the @ SQL type, and I actually defined varchar. Sorry, it took a lot of time.

 

5. Write the stored procedure in SQL

Exec iorderaddpricerule_getaddprice 'cgq', 'sfo', 0, 'fm ', '2017-7-15' spname is followed by a parameter directly. The parameter name = parameter value is not required.

 

6. Declare @ MSG nvarchar (100 );

Set @ MSG = '''gda''' + ',' + ''' AB ''';

Select * From T_A where a. col1 in (@ MSG );

I used @ MSG to concatenate the parameters in, for example, in ('gda', 'AB'). However, if I use a variable instead, there is always an error. Why?

 

7. Attach the date function of sqlserver to the standby

Getdate () dateadd (day, 2, '2017-10-15 ') datediff (day, '2017-09-01', '2017-09-18 ') Day (), month (), year ()

Datepart (month, '2017-10-15 ')

. Datepart ('w', '2017-7-25 22:56:32 ') returns 2 Monday (1 on Sunday and 7 on Saturday)

Datename returns the string representing the specified date part of the specified date

Datename (weekday, '2017-10-15 ') -- Return: Friday

Select the week of the current year = datename (Week, '2017-10-15 ')
, Today is the day of the week = datename (weekday, '2017-10-15 ')

Convert (varchar (10), getdate (), 120) returns:

Reminder: in useDate FunctionsThe date value should be between January 1, 1753 and January 1, 9999. This is the date range that the SQL server system can recognize.

 

8. Get the percentage after division of two integers
Cast (convert (decimal (1.0), (100 * count (orderid)/@ ordernum) *) as varchar (20) + '%' As cancelpercent ---- decimal) rounded to retain two decimal places

 

9. Pay attention to the specification and ease of use when writing stored procedures
/*
Function: Add and modify order partners. If there is an order partner, modify it. Otherwise, add a new record. When there is an order partner, update the order form's ticketing partner.
Parameter description:
@ Orderid: Order ID
@ Ticketissuedcopartnerid: ID of the ticketing partner
@ Delivercopartnerid: ID of the delivery partner
@ Receiptmoneycopartnerid collection partner ID
@ Ticketissuedcopartnername name of the ticketing partner
@ Delivercopartnername: name of the distributor
@ Receiptmoneycopartnername: name of the receiving partner
Created on: 2008-09-12 10: 32 created by: Xiaoqian
*/
Create procedure [DBO]. [orders_copartner_update] ......

10. Register SQL cache Dependencies

Aspnet_regsql-s 192.168.5.211-U sa-P 123456-D airticket-ed
Aspnet_regsql-s 192.168.5.211-U sa-P 123456-D airticket-T airdistance-et

Run the command in the Visual Studio 2005 Command Prompt window.
(Table aspnet_sqlcachetablesforchangenotification automatic table creation)

 

11

Today, I opened the Enterprise Manager --> table --> and opened the table. I couldn't open the data, and the system prompts "Unknown error: 7008007e". I thought it was a problem with sqlserver. I unloaded the installation and loaded the installation,

Fuck, it's still that virtue. It's not working. Google it. It turned out to be out of patches. The patch was coming down, and her mom wouldn't do it again. I was so stupid enough to pick it up from the Internet, enhance memory.

Query Analyzer: Select @ version the result is: Microsoft SQL Server 2000-8.00.194 (Intel x86) Aug 6 2000 00:57:48 copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (build 3790: Service Pack 2)