SQL Server uses stored procedures to remove duplicate rows of SQL statements

Check the code first. You can first read the SQL statement to remove duplicate records and obtain duplicate records.
Copy codeThe 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 a table
-- Transfers non-Repeated 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 whether an error occurs
If @ error <> 0
Begin
Rollback tran -- rollback if an error occurs
End
Else
Begin -- otherwise advance transactions
Commit tran
End

My data is like this: Because item_uid is the ID column, item_number has repeated,

I want to filter it as follows:

By the way, there are a few small problems encountered during programming.

1. The stored procedure cannot be found in the Code "cocould not find stored procedure ".

Because there are four program databases, and the default connection is A, but the actual execution of the stored procedure in the B library causes an error,

Solution 1: create the same stored procedure in A. 2. Replace the database when executing the connection.

2. asp.net/C# fill in the dataset returned in the stored procedure to dataset/datatable


Copy codeThe Code is 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 );


Thanks to http://www.cnblogs.com/liujuncm5/archive/2009/08/31/1557569.html here

3. In the stored procedure, SQL statements cannot be dynamically written without the order by function.

For example

Copy codeThe Code is as follows:
-- @ New_orderby is an input parameter and cannot be written like this
Select top (10 * (2-1) item_uid from testim order by @ new_orderby


-- When this is executed, The SQL statement displays the SELECT item identified by The ORDER BY number 1 contains a variable as part
Of the expression identifying a column position. Variables are only allowed when
Ordering by an expression referencing a column name.

However, it is very troublesome for me to find a solution,

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

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

4. select into and insert into select (here thanks to the http://www.cnblogs.com/freshman0216/archive/2008/08/15/1268316.html)

1.Insert into select statement

Statement format:Insert into Table2 (field1, field2,...) select value1, value2,... from Table1

 The target table Table2 must exist. Because the target table Table2 already exists, We can insert constants in addition to the fields in the source table Table1.

2.Select into from statement

Statement format:SELECT vale1, value2 into Table2 from Table1

 The target table Table2 does not exist,Because table 2 is automatically created during insertion, and the specified field data in table 1 is copied to table 2..

5. By the way, review common SQL statements
Copy codeThe Code is as follows:
Declare @ name varchar (200) -- declare a variable
Set @ name = 'abcd; def '-- Value assignment
Print 'exec len: '+ Convert (varchar (10), Len (@ name) -- convert (type, value) conversion, Len (value) Get the size
Print 'exec charindex: '+ Convert (varchar (10), CharIndex ('E', @ name) -- CharIndex (find, value) find the find location in the value
Print 'not replace: '+ @ name
Print 'exec replace: '+ Replace (@ name,'; ', '') -- replace
Print 'exec substring: '+ Substring (@ name,) -- use substring to intercept
Print @ RowCount -- returns the number of lines affected by the previous line of code

Author: chenhuzi