澳门金沙vip做购物车系统时接受到得多少个sqlserver 存储进度

即以游客身份登录网站时以cookie的方式存储购物车,而以登录用户的身份进入时将购物车信息存储到数据库中去,若是先以游客身份完成购物再登录继续购物,则将cookies购物车存入数据库;
其中涉及到的存储过程主要如下: 一:已登录会员添加商品到购物车功能:
复制代码 代码如下: /*
@store_sum表示要添加的商品数量,添加同时确认购物车中自己已有的数量与将要加入的数量之和是否超过库存
*/ CREATE proc ncp_Cart_Add ( @store_id int, @store_sum int=1,
@member_id int ) as DECLARE @Amount int DECLARE @NowAmount int Begin
select @Amount=(select amount from ncp_store where id=@store_id) IF
EXISTS(SELECT 1 FROM [ncp_cart] WHERE store_id=@store_id and
member_id=@member_id) Begin select @NowAmount=(select
store_sum+@store_sum from ncp_cart WHERE store_id=@store_id and
member_id=@member_id) if @NowAmount@Amount return 0 else UPDATE
[ncp_cart] SET store_sum=store_sum+@store_sum,addtime=getDate()
where store_id=@store_id and member_id=@member_id return 1 End ELSE
Begin select @NowAmount=(select store_sum from ncp_cart WHERE
store_id=@store_id and member_id=@member_id) if @NowAmount@Amount
return 0 else INSERT INTO [ncp_cart](store_id,store_sum,member_id)
values(@store_id,@store_sum,@member_id) return 1 END End GO
二:购物车的删除功能 复制代码 代码如下:
/* type 为1是全部删 0时只删一个 */ CREATE PROCEDURE ncp_Cart_Del
@type int=0, @store_id int , @member_id int AS begin if @type=0 delete
from [ncp_cart] where store_id=@store_id and member_id=@member_id
else delete from [ncp_cart] where member_id=@member_id End GO

一、创建存储过程


加密存储过程 ★:

if Exists(select name from sysobjects where NAME = ‘sp1LoginUser’ and
type=’P’)
drop procedure sp1LoginUser
GO
CREATE PROCEDURE [dbo].[sp1LoginUser]
— Add the parameters for the stored procedure here
@username NVARCHAR(50)
AS
BEGIN
DECLARE @identityCount INT
SELECT * FROM dbo.LoginUser where IsDelete=0
AND UserName=@username
SELECT TOP 1 @identityCount= ID FROM dbo.LoginUser ORDER BY ID DESC
select @identityCount
END
GO

IF EXISTS (SELECT name FROM
sysobjects WHERE name = ‘encrypt_this’ AND type = ‘P’)   
DROP PROCEDURE
encrypt_this 
GO 
USE pubs 
GO 
CREATE PROCEDURE
encrypt_this 
WITH ENCRYPTION  —添加WITH
ENCRYPTION即可 AS 
SELECT * FROM authors
GO  
–查看存储过程文本: 
EXEC sp_helptext
encrypt_this 
–下面是结果集: 
–***The object’s comments have
been encrypted.  
——对象已经被加密 

二、执行存储过程

 

EXEC sp1LoginUser ‘admin’


解密存储过程 ★: 
 
***CREATE  PROCEDURE
sp_decrypt(@objectName varchar(50))
AS
begin   
set nocount on 
begin tran   
declare @objectname1
varchar(100),@orgvarbin varbinary(8000) 
declare @sql1 nvarchar(4000),@sql2
varchar(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)   
DECLARE  @OrigSpText1
nvarchar(4000),@OrigSpText2 nvarchar(4000) , @OrigSpText3
nvarchar(4000), @resultsp nvarchar(4000)   
declare  @i int,@status int,@type
varchar(10),@parentid int   
declare @colid int,@n int,@q int,@j
int,@k int,@encrypted int,@number int  
select
@type=xtype,@parentid=parent_obj from sysobjects where
id=object_id(@ObjectName)      
create table  #temp(number int,colid
int,ctext varbinary(8000),encrypted int,status int)   
insert #temp SELECT
number,colid,ctext,encrypted,status FROM syscomments  WHERE id =
object_id(@objectName)   
select @number=max(number) from
#temp   set @k=0      
while @k<=@number    begin   
if exists(select 1 from syscomments
where id=object_id(@objectname) and number=@k)   
begin   
if @type=’P’   
set @sql1=(case when @number>1
then ‘ALTER PROCEDURE ‘+ @objectName +’;’+rtrim(@k)+’ WITH
ENCRYPTION 
AS’else’ALTER PROCEDURE ‘+
@objectName+’ 
WITH ENCRYPTION AS ‘
end)  
  
   
if @type=’TR’  
begin   
declare @parent_obj
varchar(255),@tr_parent_xtype varchar(10)   
select @parent_obj=parent_obj from
sysobjects where id=object_id(@objectName)   
select @tr_parent_xtype=xtype from
sysobjects where id=@parent_obj   
if @tr_parent_xtype=’V’   
begin   
set @sql1=’ALTER TRIGGER ‘+@objectname+’ ON ‘+OBJECT_NAME(@parentid)+’ WITH
ENCRYPTION INSTERD OF INSERT AS PRINT 1 ‘  
end   
else   
begin   
set @sql1=’ALTER TRIGGER ‘+@objectname+’ ON ‘+OBJECT_NAME(@parentid)+’ WITH
ENCRYPTION FOR INSERT AS PRINT 1 ‘   
end 
     
end

 

if @type=’FN’ or @type=’TF’ or
@type=’IF’   
set @sql1=(case @type when ‘TF’
then    
‘ALTER FUNCTION ‘+ @objectName+'(@a
char(1)) returns @b table(a varchar(10)) with encryption as begin insert
@b select @a return end ‘   
when ‘FN’ then   
‘ALTER FUNCTION ‘+ @objectName+'(@a
char(1)) returns char(1) with encryption as begin return @a
end’   
when ‘IF’ then   ‘ALTER FUNCTION ‘+
@objectName+'(@a char(1)) returns table with encryption as return select
@a as a’   
end)  
    
if @type=’V’   
set @sql1=’ALTER VIEW ‘+@objectname+’ WITH ENCRYPTION AS SELECT 1 as
f’      
set @q=len(@sql1)   
set
@sql1=@sql1+REPLICATE(‘-‘,4000-@q)   
select
@sql2=REPLICATE(‘-‘,8000)   
set @sql3=’exec(@sql1′   
select @colid=max(colid) from #temp
where number=@k    
set @n=1   
while
@n<=CEILING(1.0*(@colid-1)/2) and len(@sQL3)<=3996   
begin    
set @sql3=@sql3+’+@’   
set @n=@n+1   
end   
set @sql3=@sql3+’)’   
exec sp_executesql @sql3,N’@Sql1
nvarchar(4000),@ varchar(8000)’,@sql1=@sql1,@=@sql2     

三、创建函数

end   
set @k=@k+1   
end 
     
set @k=0   
while @k<=@number    
begin
      
if exists(select 1 from syscomments
where id=object_id(@objectname) and number=@k)   
begin   
select @colid=max(colid) from #temp
where number=@k    
set @n=1 
     
while @n<=@colid  
begin   
select
@OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp 
WHERE colid=@nand number=@k 
SET @OrigSpText3=(SELECT ctext FROM
syscomments WHERE id=object_id(@objectName) andcolid=@n and number=@k)   
if @n=1   
begin   
if @type=’P’   
SET @OrigSpText2=(case when
@number>1 then ‘CREATE PROCEDURE ‘+ @objectName +’;’+rtrim(@k)+’ WITH
ENCRYPTION AS ‘   
                       else ‘CREATE
PROCEDURE ‘+ @objectName +’ WITH ENCRYPTION AS ‘   
                       end)

1、
ALTER FUNCTION [dbo].[fn1GetRoleNane](
@userid INT
)
returns varchar(500)
AS
begin
DECLARE @tmp VARCHAR(500)
SELECT @tmp=isnull(@tmp+’,’,”)+ltrim(r.roleName) FROM UserAndRole ar,
Role r WHERE r.RoleID=ar.roleID AND ar.userid=@userid
RETURN ISNULL(@tmp,”)
END

if @type=’FN’ or @type=’TF’ or
@type=’IF’   
SET @OrigSpText2=(case @type when
‘TF’ then    
‘CREATE FUNCTION ‘+ @objectName+'(@a
char(1)) returns @b table(a varchar(10)) with encryption as begin insert
@b select @a return end ‘   
when ‘FN’ then   
‘CREATE FUNCTION ‘+ @objectName+'(@a
char(1)) returns char(1) with encryption as 
begin return @a end’   
when ‘IF’ then   
‘CREATE FUNCTION ‘+ @objectName+'(@a
char(1)) returns table with encryption as return select @a as
a’   
end)  
    
if @type=’TR’    
begin      
if @tr_parent_xtype=’V’   
begin   
set @OrigSpText2=’CREATE
TRIGGER ‘+@objectname+’ ON ‘+OBJECT_NAME(@parentid)+’ WITH
ENCRYPTION INSTEAD OF INSERT AS PRINT 1 ‘   
end   
else   
begin   
set @OrigSpText2=’CREATE
TRIGGER ‘+@objectname+’ ON ‘+OBJECT_NAME(@parentid)+’ WITH
ENCRYPTION FOR INSERT AS PRINT 1 ‘   
end     

 

end 
     
if @type=’V’   
set @OrigSpText2=’CREATE
VIEW ‘+@objectname+’ WITH ENCRYPTION AS SELECT 1 as
f’      
set
@q=4000-len(@OrigSpText2)   
set
@OrigSpText2=@OrigSpText2+REPLICATE(‘-‘,@q)   
end   
else   
begin   
SET @OrigSpText2=REPLICATE(‘-‘,
4000)   
end   
SET @i=1 
     
SET @resultsp = replicate(N’A’,
(datalength(@OrigSpText1) / 2))   
   
WHILE
@i<=datalength(@OrigSpText1)/2   
BEGIN      
SET @resultsp = stuff(@resultsp, @i,
1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^   
                               
(UNICODE(substring(@OrigSpText2, @i, 1))
^                                   
        
UNICODE(substring(@OrigSpText3, @i, 1)))))    
SET @i=@i+1   
END   
set @orgvarbin=cast(@OrigSpText1 as
varbinary(8000))   
set @resultsp=(case when
@encrypted=1                        
                    then
@resultsp    
                    else
convert(nvarchar(4000),case when @status&2=2 then 
     uncompress(@orgvarbin) else
@orgvarbin end)                  
     end)   
print @resultsp  
    
set @n=@n+1
      
end 
     
end   
set @k=@k+1  
end      
drop table #temp   
rollback tran   
end 
—-创建存储过程sp_decrypt后,执行以下操作. 
—-调用时,exec
sp_decrypt ‘要解密的存储过程名’ 
—此时 使用 exec
sp_helptext ‘存储过程名’,即可查看存储过程的文本信息

2、

 

select * from Split(@strwhere , ‘,’))
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[Split]
(
@c VARCHAR(MAX) ,
@split VARCHAR(50)
)

RETURNS @t TABLE ( col VARCHAR(50) )
AS
BEGIN
WHILE ( CHARINDEX(@split, @c) <> 0 )
BEGIN
INSERT @t( col )
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) – 1) )
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), ”)
END
INSERT @t( col ) VALUES ( @c )
RETURN
END
GO

四、 sqlserver 日期字段类型转字符串
(Select Convert(Varchar(10),FeeTime,120) Username,
把日期类型字段转为指定长度的字符串

五、创建分页存储过程
Create PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200),–表名,可以是多个表,但不能用别名
@PrimaryKey VARCHAR(100),–主键,可以为空,但@Order为空时该值不能为空
@Fields
VARCHAR(200),–要取出的字段,可以是多个表的字段,可以为空,为空表示select*
@PageSize INT,–每页记录数
@CurrentPage INT,–当前页,表示第页
@Filter VARCHAR(200)=”,–条件,可以为空,不用填where
@Group VARCHAR(200)=”,–分组依据,可以为空,不用填groupby
@Order
VARCHAR(200)=”–排序,可以为空,为空默认按主键升序排列,不用填orderby
AS
BEGIN
DECLARE @SortColumn VARCHAR(200)
DECLARE @Operator CHAR(2)
DECLARE @SortTable VARCHAR(200)
DECLARE @SortName VARCHAR(200)
IF @Fields=”
SET @Fields=’*’
IF @Filter=”
SET @Filter=’Where1=1′
ELSE
SET @Filter=’Where’+@Filter
IF @Group<>”
SET @Group=’GROUPBY’+@Group

IF @Order<>”
BEGIN
DECLARE @pos1 INT,@pos2 INT
SET @Order=REPLACE(REPLACE(@Order,’asc’,’ASC’),’desc’,’DESC’)
IF CHARINDEX(‘DESC’,@Order)>0
IF CHARINDEX(‘ASC’,@Order)>0
BEGIN
IF CHARINDEX(‘DESC’,@Order)<CHARINDEX(‘ASC’,@Order)
SET @Operator='<=’
ELSE
SET @Operator=’>=’
END
ELSE
SET @Operator='<=’
ELSE
SET @Operator=’>=’
SET
@SortColumn=REPLACE(REPLACE(REPLACE(@Order,’ASC’,”),’DESC’,”),”,”)
SET @pos1=CHARINDEX(‘,’,@SortColumn)
IF @pos1>0
SET @SortColumn=SUBSTRING(@SortColumn,1,@pos1-1)
SET @pos2=CHARINDEX(‘.’,@SortColumn)
IF @pos2>0
BEGIN
SET @SortTable=SUBSTRING(@SortColumn,1,@pos2-1)
IF @pos1>0
SET @SortName=SUBSTRING(@SortColumn,@pos2+1,@pos1-@pos2-1)
ELSE
SET @SortName=SUBSTRING(@SortColumn,@pos2+1,LEN(@SortColumn)-@pos2)
END
ELSE
BEGIN
SET @SortTable=@TableNames
SET @SortName=@SortColumn
END
END
ELSE
BEGIN
SET @SortColumn=@PrimaryKey
SET @SortTable=@TableNames
SET @SortName=@SortColumn
SET @Order=@SortColumn
SET @Operator=’>=’
END

DECLARE @type varchar(50)
DECLARE @prec int
Select @type=t.name,@prec=c.prec
FROM sysobjects o
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
Where o.name=@SortTable AND c.name=@SortName
IF CHARINDEX(‘char’,@type)>0
SET @type=@type+'(‘+CAST(@prec AS varchar)+’)’