sql server stored procedure tutorial

procedure of the database, is also the first query of the master table records, and then use the cursor loop in the process of querying the child table information, and then in C # --Centralized processing Obviously, the latter reduces the transaction cost of the database, one time to obtain the desired data, personally think it is better than the first, welcome everyone to discuss a better way Note that the type returned by Row_number () is big

★First of all, I would like to thank the Netizens [from the SQL server technology space group] for their simple support... Note the difference between null and ''. Select * from table where fdate> comparison between '2007-6-7' or between time .......... ------------ Table field --------------- Create Table [DBO]. [stuinfo] ( [Fnumber] [int] identity (1, 1) not null, [Fname] [nvarchar] (30) Collate chinese_p

Label:1. Returns a valueDECLARE @count int exec @count = testreturn \ ' 111\ ', \ ' 222\ ' select @count@count is the value returned is the INT type2. Return a data tableStart by creating a new data tableCREATE TABLE Test ( tmp_order_id varchar (20))The returned data is then inserted into the tableGet the contents of a tableSELECT * FROM TestTo delete a generated data tableDROP TABLE test;For more details, refer to: http://blog.csdn.net/liangweiwei130/article/details/6691207How to get the ret

Use Fperp--Specifies the database to generate the data dictionaryGoSELECTTable name =case when a.colorder=1 then D.name else ' end,Table shows =case when a.colorder=1 then IsNull (F.value, ') Else ' end,Field Ordinal =a.colorder,Field name =a.name,Identify =case when ColumnProperty (A.id,a.name, ' isidentity ') =1 then ' √ ' Else ' end,Primary key =case when exists (SELECT 1 from sysobjects where xtype= ' PK ' and name in (SELECT name from sysindexes WHERE indid in (SELECT indid from Sysindexkey

* *************************************************************** * China worry-free business network tens of millions of pages stored procedures * * * ************************ Parameter description: 1.Tables: Table name, view 2.PrimaryKey: Primary keyword 3.Sort: sort statements, without ORDER by example: NewsID Desc , Orderrows Asc 4.CurrentPage: Current page number 5.PageSize: Page size 6.Filter: Filter statement, no where 7.group:group statement,

Create or replace PROCEDURE cmpp_submit_remove_procedure Is Dendtime date; --Each processing is a point in time --Get the data ID to process Cursor C_cmpp_submit (endtime date) is Select ID from Cmpp_submit Where ((ih_process or (ih_process = ' wait_for_response ' and ih_retry and Ih_timestamp IId number; --ID Icount number; --Counter Begin --dendtime: = Trunc ((sysdate-1/288), ' mi '); --Get the processing time

Advantages of using stored procedures: 1.Stored procedures are pre-compiled in the database,Compared with pure SQLHigher execution efficiency 2.The amount of data transmitted by stored procedures over the network is more than that transmitted by pure SQL statements.Few statements. 3.

You must have had such troubles. The same table and different databases cannot be added to select insert.Therefore, you must enter a stored procedure to indicate that the insert statement will generate data for you.Of course, the number of data tables is too large. You 'd better use another method. Copy codeThe Code is as follows:Create proc [dbo]. [spGenInsertSQL] (@ tablename varchar (256 ))AsBeginDeclare

You must have had such trouble, the same table, different database, join you can't perform select InsertThen you definitely need a stored procedure that needs to be passed in and will give you the INSERT statement that generates the data.Of course the number of data sheets is too large, you'd better use another way Copy Code code as follows: Create proc [dbo]. [Spgeninsertsql] (@tablename varch

SQL Server's extended storage process is actually a common WindowsDLL, but some functions are implemented according to certain rules. Recently, when writing an extended storage process, I found that there are still some important points to note when writing this kind of dynamic library. Note that the DLL runs in the address space of SQLServer. SQL Server's extended storage process is actually a common Windo

/* -- Paging program implemented by stored proceduresDisplays page X of the specified table, view, and query result.In the case of primary keys or ID columns in the table, query the data directly from the original table. In other cases, use the temporary table method.This method is not recommended if the view or query result contains a primary key.-- Producer build 2003.09 --*//* -- Call exampleExec p_show 'region information'Exec p_show 'region infor

/* -- Paging program implemented by stored procedures Displays page X of the specified table, view, and query result. In the case of primary keys or ID columns in the table, query the data directly from the original table. In other cases, use the temporary table method. This method is not recommended if the view or query result contains a primary key. -- Producer build 2003.09 --*/ /* -- Call example Exec p_show 'region information' Exec p_show 'regio

/*--a paging program implemented using stored procedures Show page x for specified tables, views, query results For the primary key or identity column in the table, the query is taken directly from the original table, and other cases use the temporary table method This method is not recommended if there are primary keys in the view or query results --Jiangjian 2003.09--*/ /*--Call Example EXEC p_show ' Area information ' EXEC p_show ' area information

Copy Code code as follows: Set ANSI_NULLS on SET QUOTED_IDENTIFIER ON Go Create PROCEDURE [dbo]. [Sp_pagination] /**//* *************************************************************** * * Tens of millions of levels of paging stored procedures * * *************************************************************** Parameter description: 1.Tables: Table name, view 2.PrimaryKey: Primary keyword

Realize 1) have the same data, return directly (return value: 0); 2 has the same primary key, but the data different data, carries on the update processing (returns the value: 2); 3 No data, insert data processing (return value: 1). "Create a stored procedure" Create proc insert_update @Id varchar, @Name varchar, @Telephone varchar, @Address varchar, @Job varchar, @returnValue int ou

Just taking notes, nothing!! Copy Code code as follows: --Create a test table CREATE TABLE [dbo]. [Student] ( [ID] [int] IDENTITY (1,1) not NULL PRIMARY KEY, [Name] [nvarchar] () not NULL DEFAULT ('), [Age] [INT] Not NULL DEFAULT (0), [Sex] [Bit] Not NULL DEFAULT (0), [Address] [nvarchar] () not NULL DEFAULT (') ) -for example, a query stored procedure Create PROC Getstuden

from syscolumns C where order by C. colidOpen syscolumns_cursorSet @ column =''Set @ columndata =''Fetch next from syscolumns_cursor into @ name, @ xtypeWhile @ fetch_status BeginIf @ fetch_status Begin-- If @ xtype not in (189,34, 35,99, 98) -- timestamp does not need to be processed. Image, text, ntext, SQL _variant will not be processed for the moment.BeginSet @ column = @ column + case when Len (@ column) = 0 then 'else', 'end + @ nameSet @ colum

Create procedure [DBO]. [sp_get_tree_relation] (@ Table_name nvarchar (50), @ ID nvarchar (50), @ name nvarchar (50), @ parent_id nvarchar (50), @ startid nvarchar (20 )) As Declare @ v_id int Declare @ v_level int Declare @ SQL nvarchar (500) Begin Create Table # temp (ID nvarchar (20), name nvarchar (50), parent_id nvarchar (20 )) Create Table # T1 (ID nvarchar (20), name nvarchar (50), parent_id nvarchar

One: Stored procedure implementation Paging Example: Query the property list ALTER procedure [dbo]. [Bm_getblocklist] @pageIndexint,-------number of pages @pagesizeint,---------number of bars displayed @cityidint asdeclare @sql nvarchar (max), @sql2 nvarchar (max)Set@sql ='F

, and has done its own query optimization check. 3, high program operation efficiency, optimize the application, in the SP writing process should pay attention to the following points: (a) Usage specification for SQL: I. Avoid big business operation, use HOLDLOCK clause carefully, improve system concurrent ability. Ii. try to avoid repeated access to the same or several tables, especially the large data table, you can consider the conditions to ex