Shulou(Shulou.com)05/31 Report--

This article introduces how to use stored procedures to remove duplicate lines in sqlserver. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

The code is as follows: ALTER procedure [dbo]. [PROC_ITEMMASTER_GETUNIQUE] @ PAGEINDEX INT,@uid int @ itemnumber varchar (50) AS begin tran-- start transaction drop table [ItemMaster]. [dbo]. [testim]-- Delete table-- dump non-repeating records to testim select * into [ItemMaster]. [dbo]. [testim] from [ItemMaster] .[ dbo]. [dat _ item_master] where item_uid in (select min (item_uid) as item_uid from [ItemMaster]. [dbo]. [dat _ item_master] group by item_number) and status=0 select Top 10 * from [ItemMaster]. [dbo]. [testim] where item_uid not in (select top (10 * (@ PAGEINDEX-1)) item_uid from [ItemMaster]. [dbo]. [testim]) and owneruid=@uid and item_number like @ itemnumber+'%'-- determine if there is an error if @ @ error0 begin rollback tran-- roll back end else begin if there is an error-- otherwise advance transaction commit tran end

My data is like this: because item_uid is an identity column and item_number is duplicated, I want to filter it like this: by the way, a few minor problems encountered in programming 1. Program appears Could not find stored procedure can not find this stored procedure because my program database has four, and the default connection is A, but actually want to execute the stored procedure in B library, which leads to an error. Solution 1: create the same stored procedure in A 2: when performing the connection, replace the database. 2. Asp.net/C# will store the dataset returned in the stored procedure Populate the copy code to dataset/datatable as follows: SqlConnection conn = new SqlConnection (ConfigurationManager.ConnectionStrings ["SolutionSQLServer"] .ToString ()) SqlCommand cmd = new SqlCommand ("Test", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add ("@ MaxId", SqlDbType.Int). Value = 12000; SqlDataAdapter sda = new SqlDataAdapter (cmd); DataTable dt = new DataTable (); sda.Fill (dt)

I would like to thank http://www.cnblogs.com/liujuncm5/archive/2009/08/31/1557569.html 3. In a stored procedure, you cannot write a SQL statement without orderby dynamically. For example, the copy code is as follows:-- @ new_orderby is an incoming parameter, and you cannot write select top (10* (2-1)) item_uid from testim orderby @ new_orderby. When this is executed, The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position will appear in SQL. Variables are only allowed when ordering by an expression referencing a column name.

But I found a solution, but it was troublesome.

Http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9328 (the second answer is connected with 'sql')

Http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html (case end is also fine)

4. Two copied sentences, select into and insert into select (thanks to http://www.cnblogs.com/freshman0216/archive/2008/08/15/1268316.html)

1.INSERT INTO SELECT statement

The statement form is: Insert into Table2 (field1,field2,...) Select value1,value2,... From Table1

The target table Table2 must exist, and since the target table Table2 already exists, we can insert constants in addition to the fields of the source table Table1.

2.SELECT INTO FROM statement

The statement form is: SELECT vale1, value2 into Table2 from Table1

It is required that the target table Table2 does not exist because the table Table2 is automatically created upon insertion and the field data specified in the Table1 is copied to the Table2.

5. By the way, review the commonly used SQL method statement copy code as follows: declare @ name varchar (200)-declare the variable set @ name='abcd Def'-- assign print 'exec len:' + Convert (varchar (10), Len (@ name))-- convert (type,value) transform, Len (value) get the size of print 'exec charindex:'+Convert (varchar (10), CharIndex (' eCooper dwelling name))-- CharIndex (find,value) finds the location of find in value print 'not replace:'+@name print' exec replace:'+Replace (@ name,') ',')-- replace print 'exec substring:'+Substring (@ name,0,3) with replace-- intercept print @ @ RowCount with substring-- returns the number of lines affected by the previous line of code

So much for sharing about how to use stored procedures to remove duplicate lines in sqlserver. I hope the above content can be of some help and can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.