ALTER procedure [dbo].[PROC_ITEMMASTER_GETUNIQUE] @PAGEINDEX INT,@uid int,@itemnumber varchar(50)
AS
begin tran --開始事務(wù)
drop table [ItemMaster].[dbo].[testim] --刪除表
--把不重復(fù)記錄轉(zhuǎn)存到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+'%'

--判斷是否出錯(cuò)
if @@error<>0
begin
rollback tran --出錯(cuò)則回滾
end
else
begin --否則提前事務(wù)
commit tran
end

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);

--·@new_orderby 是傳入?yún)?shù)，不能這樣寫
select top (10*(2-1)) item_uid from testim order by @new_orderby


--執(zhí)行這個(gè)的時(shí)候，SQL會(huì)出現(xiàn) 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.

declare @name varchar(200) --聲明變量
set @name='abcd;def' --賦值
print 'exec len :'+Convert(varchar(10),Len(@name)) --convert(type,value)轉(zhuǎn)換，Len(value)獲取大小
print 'exec charindex:'+Convert(varchar(10),CharIndex('e',@name))--CharIndex(find,value) 在value中查找find的位置
print 'not replace:'+@name
print 'exec replace:'+Replace(@name,';','') --用replace替換
print 'exec substring:'+Substring(@name,0,3)--用substring截取
print @@RowCount --返回上一行代碼受影響的行數(shù)