也许是因为SP行业的黑暗性太强,在网上很少能找到相关的技术实现,好不容易找到了一篇,自然要收藏一下了,希望作者不要介意我的收藏哦。
原文地址:http://blog.chinaunix.net/u/20532/showart_195949.html,也是作者的博客地址。
/**/
/****************************************************************************/
/**/ /* */
/**/ /* FileName: jc.sql */
/**/ /* */
/**/ /* Description: Transact-sql脚本 */
/**/ /* */
/**/ /* Database: db_jc */
/**/ /* */
/**/ /* Table: tb_question,tb_user,tb_score */
/**/ /* */
/**/ /* Procedure: sp_jc*/
/**/ /* */
/**/ /* Author: wiThouTTears http://withouttears.cublog.cn/ */
/**/ /* */
/**/ /* Date: 2006/11/06 */
/**/ /* */
/**/ /* History: */
/**/ /* */
/**/ /****************************************************************************/
/**/ /************************第一步:在SQL查询分析器中执行***************************/
-- ------------------创建数据库-------------------------
IF NOT EXISTS ( SELECT name FROM master.dbo.sysdatabases
WHERE name = ' db_jc ' )
create database db_jc
DROP table tb_question
DROP table tb_user
DROP table tb_score
GO
-- ------------------创建表-------------------------
use db_jc
CREATE TABLE tb_question
(
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ question ] [ varchar ] ( 50 ) ,
[ A ] [ varchar ] ( 50 ) ,
[ B ] [ varchar ] ( 50 ) ,
[ C ] [ varchar ] ( 50 ) ,
[ D ] [ varchar ] ( 50 ) ,
[ Y ] [ varchar ] ( 50 )
) ON [ PRIMARY ]
GO
CREATE TABLE tb_user (
[ mobile ] [ varchar ] ( 11 )
) ON [ PRIMARY ]
GO
CREATE TABLE tb_score (
[ count ] [ int ] NULL ,
[ result ] [ int ] NULL
) ON [ PRIMARY ]
GO
-- ------------------插入数据-------------------------
insert into tb_question (question,A,B,C,D,Y) values ( ' 语文题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 数学题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 外语题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 物理题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 化学题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 生物题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_score values ( 1 , 100 )
GO
-- ------------------定义存储过程-------------------------
use db_jc
IF EXISTS ( SELECT name FROM sysobjects
WHERE name = ' sp_jc ' AND type = ' P ' )
DROP PROCEDURE sp_jc
GO
create procedure sp_jc
@mobile varchar ( 11 ),
@content varchar ( 10 )
AS
set nocount on
declare @iRecordCount int
declare @nRecordCount int
declare @userRecordCount int
declare @count int
declare @firstid int
declare @question varchar ( 50 )
declare @A varchar ( 50 )
declare @B varchar ( 50 )
declare @C varchar ( 50 )
declare @D varchar ( 50 )
set @firstid = ( select top 1 id from tb_question) -- 题目开始id号
set @count = ( select count from tb_score) -- 答对的题目的id号
declare @result int
set @result = ( select result from tb_score)
set @iRecordCount = ( select count ( * ) from tb_question) -- 取得题目总数
-- print @iRecordCount
-- print @count
-- select * from mytable where id= @count and Y= @content
set @nRecordCount = ( select count ( * ) from tb_question where id = @count and Y = @content ) -- 正误标识,0表答错,1表答对
-- print '正误标识:'+convert(varchar,@nRecordCount)
begin
set @userRecordCount = ( select count ( * ) from tb_user where mobile = @mobile ) -- 标识,0表示用户是新用户,1表老用户
-- print '用户类型标识:'+convert(varchar,@userRecordCount)
if @userRecordCount = 0 -- 如果是新用户
begin
insert into tb_user values ( @mobile ) -- 记录用户的手机号
end
end
begin
set @question = ( select question from tb_question where id = @count + 1 )
set @A = ( select A from tb_question where id = @count + 1 )
set @B = ( select B from tb_question where id = @count + 1 )
set @C = ( select C from tb_question where id = @count + 1 )
set @D = ( select D from tb_question where id = @count + 1 )
print ' 第 ' + convert ( varchar , @count + 1 ) + ' 题: ' + @question
print ' A. ' + @A
print ' B. ' + @B
print ' C. ' + @C
print ' D. ' + @D
if @nRecordCount > 0 -- 答对题目
if @count = @iRecordCount
begin
print ''
print ' 挑战成功,你一共答对了 ' + convert ( varchar , @iRecordCount ) + ' 道题,共计: ' + convert ( varchar , @result ) + ' 分 '
print ' 再接再励! '
update tb_score set count = 0
update tb_score set result = 0
end
else
begin
update tb_score set count = count + 1
update tb_score set result = result + 100
print ''
print ' 答对了,你一共答对了 ' + convert ( varchar , @count - @firstid + 1 ) + ' 道题,共计: ' + convert ( varchar , @result ) + ' 分 '
print ' 再接再励! '
end
else -- 答错题目
begin
if @count - @firstid >= 0
begin
print ''
print ' 答错了,你一共答对了 ' + convert ( varchar , @count - @firstid ) + ' 道题,共计: ' + convert ( varchar , @result - 100 ) + ' 分 '
print ' 挑战失败,不要灰心,再来一次! '
update tb_score set count = @firstid - 1
update tb_score set result = 100
end
else
begin
print ''
print ' 准备好了,开始答题! '
update tb_score set count = @firstid
update tb_score set result = 100
end
end
end
GO
-- ---------第二步: 在SQL查询分析器中执行------------
exec sp_jc 13888888888 ,A
/**/ /* */
/**/ /* FileName: jc.sql */
/**/ /* */
/**/ /* Description: Transact-sql脚本 */
/**/ /* */
/**/ /* Database: db_jc */
/**/ /* */
/**/ /* Table: tb_question,tb_user,tb_score */
/**/ /* */
/**/ /* Procedure: sp_jc*/
/**/ /* */
/**/ /* Author: wiThouTTears http://withouttears.cublog.cn/ */
/**/ /* */
/**/ /* Date: 2006/11/06 */
/**/ /* */
/**/ /* History: */
/**/ /* */
/**/ /****************************************************************************/
/**/ /************************第一步:在SQL查询分析器中执行***************************/
-- ------------------创建数据库-------------------------
IF NOT EXISTS ( SELECT name FROM master.dbo.sysdatabases
WHERE name = ' db_jc ' )
create database db_jc
DROP table tb_question
DROP table tb_user
DROP table tb_score
GO
-- ------------------创建表-------------------------
use db_jc
CREATE TABLE tb_question
(
[ id ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ question ] [ varchar ] ( 50 ) ,
[ A ] [ varchar ] ( 50 ) ,
[ B ] [ varchar ] ( 50 ) ,
[ C ] [ varchar ] ( 50 ) ,
[ D ] [ varchar ] ( 50 ) ,
[ Y ] [ varchar ] ( 50 )
) ON [ PRIMARY ]
GO
CREATE TABLE tb_user (
[ mobile ] [ varchar ] ( 11 )
) ON [ PRIMARY ]
GO
CREATE TABLE tb_score (
[ count ] [ int ] NULL ,
[ result ] [ int ] NULL
) ON [ PRIMARY ]
GO
-- ------------------插入数据-------------------------
insert into tb_question (question,A,B,C,D,Y) values ( ' 语文题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 数学题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 外语题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 物理题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 化学题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_question (question,A,B,C,D,Y) values ( ' 生物题目 ' , ' A的内容 ' , ' B的内容 ' , ' C的内容 ' , ' D的内容 ' , ' A ' )
GO
insert into tb_score values ( 1 , 100 )
GO
-- ------------------定义存储过程-------------------------
use db_jc
IF EXISTS ( SELECT name FROM sysobjects
WHERE name = ' sp_jc ' AND type = ' P ' )
DROP PROCEDURE sp_jc
GO
create procedure sp_jc
@mobile varchar ( 11 ),
@content varchar ( 10 )
AS
set nocount on
declare @iRecordCount int
declare @nRecordCount int
declare @userRecordCount int
declare @count int
declare @firstid int
declare @question varchar ( 50 )
declare @A varchar ( 50 )
declare @B varchar ( 50 )
declare @C varchar ( 50 )
declare @D varchar ( 50 )
set @firstid = ( select top 1 id from tb_question) -- 题目开始id号
set @count = ( select count from tb_score) -- 答对的题目的id号
declare @result int
set @result = ( select result from tb_score)
set @iRecordCount = ( select count ( * ) from tb_question) -- 取得题目总数
-- print @iRecordCount
-- print @count
-- select * from mytable where id= @count and Y= @content
set @nRecordCount = ( select count ( * ) from tb_question where id = @count and Y = @content ) -- 正误标识,0表答错,1表答对
-- print '正误标识:'+convert(varchar,@nRecordCount)
begin
set @userRecordCount = ( select count ( * ) from tb_user where mobile = @mobile ) -- 标识,0表示用户是新用户,1表老用户
-- print '用户类型标识:'+convert(varchar,@userRecordCount)
if @userRecordCount = 0 -- 如果是新用户
begin
insert into tb_user values ( @mobile ) -- 记录用户的手机号
end
end
begin
set @question = ( select question from tb_question where id = @count + 1 )
set @A = ( select A from tb_question where id = @count + 1 )
set @B = ( select B from tb_question where id = @count + 1 )
set @C = ( select C from tb_question where id = @count + 1 )
set @D = ( select D from tb_question where id = @count + 1 )
print ' 第 ' + convert ( varchar , @count + 1 ) + ' 题: ' + @question
print ' A. ' + @A
print ' B. ' + @B
print ' C. ' + @C
print ' D. ' + @D
if @nRecordCount > 0 -- 答对题目
if @count = @iRecordCount
begin
print ''
print ' 挑战成功,你一共答对了 ' + convert ( varchar , @iRecordCount ) + ' 道题,共计: ' + convert ( varchar , @result ) + ' 分 '
print ' 再接再励! '
update tb_score set count = 0
update tb_score set result = 0
end
else
begin
update tb_score set count = count + 1
update tb_score set result = result + 100
print ''
print ' 答对了,你一共答对了 ' + convert ( varchar , @count - @firstid + 1 ) + ' 道题,共计: ' + convert ( varchar , @result ) + ' 分 '
print ' 再接再励! '
end
else -- 答错题目
begin
if @count - @firstid >= 0
begin
print ''
print ' 答错了,你一共答对了 ' + convert ( varchar , @count - @firstid ) + ' 道题,共计: ' + convert ( varchar , @result - 100 ) + ' 分 '
print ' 挑战失败,不要灰心,再来一次! '
update tb_score set count = @firstid - 1
update tb_score set result = 100
end
else
begin
print ''
print ' 准备好了,开始答题! '
update tb_score set count = @firstid
update tb_score set result = 100
end
end
end
GO
-- ---------第二步: 在SQL查询分析器中执行------------
exec sp_jc 13888888888 ,A