I used to get a similar method to remove the same information. I cannot find it now, but today I have spent some time to get it out and record it.
I used to get a similar method to remove the same information. I cannot find it now, but today I have spent some time to get it out and record it.
Check the code first.
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 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
The 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
The 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,
(The second answer is to use 'SQL' for connection)
(Use case end)
4. select into and insert into select (thanks)
1.
Statement format:
2.
Statement format:
.
5. By the way, review common SQL statements
The 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:
