First the code, you can see the SQL statement to remove duplicate records, get duplicate records
Copy Code code as follows:
ALTER procedure [dbo]. [Proc_itemmaster_getunique] @PAGEINDEX int, @uid int, @itemnumber varchar (50)
As
Begin Tran--Start a transaction
drop table [Itemmaster]. [dbo]. [Testim]--delete table
--Transfer the record 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 * 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 @ @error <>0
Begin
Rollback TRAN--rollback if error
End
Else
Begin--Otherwise advance transaction
Commit Tran
End
My data is this: Because Item_uid is an identity column, Item_Number has duplicates,
I want to filter it into this:
Incidentally, some of the little problems that you encounter when you're programming.
1. The program appears could not found stored procedure cannot find this stored procedure
Because my program database has four, and the default connection is a, but actually to execute the stored procedure in B library, resulting in an error,
Solution 1: You can build the same stored procedure in a 2: When the connection is performed, replace the database.
2. asp.net/c# populates the dataset returned in the stored procedure to dataset/datatable
Copy Code code 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);
Thank http://www.cnblogs.com/liujuncm5/archive/2009/08/31/1557569.html in this
3. In the stored procedure, write SQL statements can not dynamic without order by function
Like what
Copy Code code as follows:
--@new_orderby is an incoming parameter and cannot be written like this
Select Top (10* (2-1)) Item_uid the From Testim order by @new_orderby
--At the time of execution, SQL will appear the SELECT item identified by number 1 contains a variable as part
Of the expression identifying a column position. Variables are only allowed when
Ordering by a expression referencing a column name.
But I found a solution, but it was a hassle.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9328 (second answer is connected with ' SQL ')
Http://databases.aspfaq.com/database/how-do-i-use-a-variable-in-an-order-by-clause.html (with case end also line)
4. Select INTO and insert into select two kinds of copy (thank http://www.cnblogs.com/freshman0216/archive/2008/08/15/1268316.html here)
1.INSERT into SELECT statement
Statement form:Insert into Table2 (field1,field2,...) Select Value1,value2,... from Table1
the target table Table2 must exist, and because the target table Table2 already exists, we can insert a constant in addition to the fields Table1 the source table.
2.SELECT into from statement
Statement form:SELECT vale1, value2 into Table2 from Table1
The target table Table2 does not exist because the table Table2 is created automatically at insert time and the specified field data in Table1 is copied to Table2.
5. Review the commonly used SQL method statements by the way
Copy Code code as follows:
DECLARE @name varchar (200)--Declaring variables
Set @name = ' abcd;def '--assignment
print ' EXEC len: ' +convert (varchar), Len (@name))--convert (type,value) conversion, Len (value) Get size
print ' EXEC charindex: ' +convert (varchar), charindex (' e ', @name))--charindex (find,value) find location in value
print ' not replace: ' + @name
print ' EXEC replace: ' +replace (@name, '; ', ')--replace with replace
print ' EXEC substring: ' +substring (@name, 0, 3)--intercept with substring
Print @ @RowCount--Returns the number of rows affected by the previous line of code
Author: Chenhuzi
