stored procedure performance tuning in sql server

violated. In fact, I have been using the second option. In order to find convenience, I sometimes add CategoryID and Categorypath to the news sheet. And the algorithm I am going to say today is actually the improved version of the second scheme, and the general classification is to use a table to save the classified information. And here I am, to create two new tables, one table is to save the category information table, a Save the taxonomy table. The table structure is as follows: Table 1:t

Create proc Get_data(@Del_ID varchar (36))AsSELECT * from depts where [email protected]_idSELECT * FROM DeptsCREATE PROCEDURE Proc_insert_data@DealerID varchar (36)AsBeginDECLARE @count intSelect @count = (select count (*) from depts where [email protected])if (@count >0)BeginDelete from depts where [email protected]Insert into depts (deptname) VALUES (@DealerID)EndElseBeginInsert into depts (deptname) VALUES (@DealerID)EndEndSQL

Label:Transfer from http://www.shangxueba.com/jingyan/1940447.html 1. Cursor mode Copy the Code code as follows: DECLARE @Data NVARCHAR (max) SET @Data = ' 1,tanw,2,keenboy '--id,name DECLARE @dataItem NVARCHAR (100) DECLARE data_cursor Cursor for (SELECT * from Split (@Data, '; ')) OPEN Data_cursor FETCH NEXT from Data_cursor to @dataItem While @ @FETCH_STATUS =0 BEGIN DECLARE @Id INT DECLARE @Name NVARCHAR (50) DECLARE dataitem_cursor Cursor for (SELECT * from Split (@dataItem, ', ')) OPEN Dat

Tags: Define variable--simple assignment declare @a int set @a=5 print @a--Assign value using SELECT statement declare @user1 nvarchar select @user1 = ' Zhang San ' print @user1 declare @user2 nvarchar Select @user2 = Name from St_user where id=1 print @user2--Use the UPDATE statement to assign a value DECLA Re @user3 nvarchar update st_user set @user3 = Name where id=1 print @user3 Two, table, temp table, table variable--Create temporary table 1 creation table # Du_user1 ([ID] [int] NOT NULL,

In SQL Server, there are not many opportunities to extend the storage process for direct use.I have summarized several useful extended stored procedures as follows: -- Obtain the version number of MS SQLExecute master .. sp_msgetversionGo-- The effect is as follows:Character_value------------------------------------------8.00.760 1 3 (The number of affected r

Small, with parametersif (exists (SELECT * from sys.objects where name = ' proc_get_purchasing2 '))drop proc Proc_get_studentGoALTER proc PROC_GET_PURCHASING2 (@today int)As--declare @today intDeclaration variablesdeclare @weeks nvarchar (3)Set Variable value set @weeksSet @weeks =case when @today = 1 Then ' Monday ' When @today = 2 Then ' Tuesday 'Else ' ERROR 'EndPrint @weeksEXEC proc_get_purchasing2 @today =2With loopsif (exists (SELECT * from sys.objects where name = ' Proc_get_purchasing3 '

SQL Server stored procedure syntax:Create PROC [edure] procedure_name [; number] [{@parameter data_type} [VARYING] [= default] [OUTPUT]] [,... n] [with {RECOMPILE |Encryption | RECOMPILE, encryption}] [for REPLICATION] as sql_statement [... n]I. Introduction of Parameters 　 1, procedure_nameThe name of the new

1. Define Stored procedures-- =============================================--Defining stored procedures-- =============================================CREATE PROCEDURE [dbo]. [Updatet]-Define XML parameters@xml XMLAsBEGINSET NOCOUNT on;DECLARE @xmlHandle int--Output parametersEXEC sp_xml_preparedocument @xmlHandle OUTPUT, @xmlSELECT Mid,mname,msex into #tmpFrom O

Creates a temporary table that reports errors when inserting data into a temporary table. A start prompt does not open the primary key, and then the primary key is turned on to prompt the above error exception. Find information from the Internet did not find, and then to the group to ask you Daniel, a Daniel told me is not set the primary key. I took a closer look at the hint and realized that I had set the primary key temporarily. Code before the primary key: CREATE TABLE #TT ( cours

SQL Server General paging stored procedures, with stored procedures can improve efficiency and save time. SQL Server General paging stored procedures, with

Note: Stored Procedures can prevent the threat of SQL Injection Vulnerabilities and are very secure. The Stored Procedure contains input and output parameters. The input parameters include the user ID and password. The returned values indicate different results. Alter procedure