1. 创建用户自定义的表类型(Table Type)
CREATE TYPE [dbo].[TVPGameDataItem] AS TABLE (
[UserID] [BIGINT] NULL,
[DBID] [INT] NULL,
[Wins] [INT] NULL,
[Balances] [INT] NULL,
[Loses] [INT] NULL,
[Escapes] [INT] NULL,
[Score] [INT] NULL,
[Money] [BIGINT] NULL,
[Experience] [INT] NULL
)
2. 存储过程中使用TVP (Table Valued Parameter)
CREATE PROCEDURE [dbo].[xp_User_ChangeGameResources]
@GameID int,
@Items dbo.TVPGameDataItem READONLY
AS
SET NOCOUNT ON
BEGIN TRAN
— 更新game_data记录
MERGE INTO tb_game_data t1 USING @Items t2 ON t1.GameID = @GameID AND t1.UserID = t2.UserID
WHEN MATCHED THEN
UPDATE SET
t1.Wins = t1.Wins + t2.Wins,
t1.Balances = t1.Balances + t2.Balances,
t1.Loses = t1.Loses + t2.Loses,
t1.Escapes = t1.Escapes + t2.Escapes,
t1.Score = t1.Score + t2.Score,
t1.Money = t1.Money + t2.Money,
t1.Experience = t1.Experience + t2.Experience
WHEN NOT MATCHED THEN
INSERT (UserID, GameID, Wins, Balances, Loses, Escapes, Score, Money, Experience)
VALUES (t2.UserID, @GameID, t2.Wins, t2.Balances, t2.Loses, t2.Escapes, t2.Score, t2.Money, t2.Experience);
— 更新user信息
UPDATE tb_user_BaseInfo_0
SET tb_user_BaseInfo_0.Money = t1.Money + t2.Money
FROM tb_user_BaseInfo_0 t1
INNER JOIN @Items t2 ON t1.UserID = t2.UserID
COMMIT TRAN
RETURN @@error
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE PROC [dbo].[xp_UserLog_GameResChange]
@DataType int,
@GameResChangeType int,
@GameID int,
@IsPlaying int,
@Items dbo.TVPGameLogItem READONLY
AS
SET NOCOUNT ON
DECLARE @BattleID BIGINT
SET @BattleID = 0
BEGIN TRAN
IF (@IsPlaying = 1)
BEGIN
SELECT @BattleID = Intvalue FROM tb_LGSystermParam WHERE SysID = 1
if @BattleID = 0
BEGIN
SET @BattleID = 1
END
UPDATE tb_LGSystermParam SET Intvalue = (@BattleID + 1) WHERE SysID = 1
END
INSERT INTO tb_game_log
(
UserID ,
GameID ,
GameResult ,
cashChanged ,
scoreChanged ,
ExperienceChanged ,
TableFee ,
ChangingType ,
BattleID ,
ChangingTime
)
SELECT
UserID,
@GameID,
Result,
Money,
Score,
Experience,
TableFee,
@GameResChangeType,
@BattleID,
GETDATE()
FROM @Items
COMMIT TRAN
RETURN @@error
3. 程序中使用ODBC向存储过程的TVP参数绑定多行数据
1) 绑定TVP参数时，参数类型（第5个参数为SQL_SS_TABLE）
// Bind TVP parameter
SQLLEN cbTVP;
m_cbValue[++m_nBindParam] = 0;
SQLBindParameter(m_hstmt,
m_nBindParam, // ParameterNumber
SQL_PARAM_INPUT, // InputOutputType
SQL_C_DEFAULT, // ValueType
SQL_SS_TABLE, // Parametertype
MAX_CHAIRS, // ColumnSize – for a TVP this the row array size
0, // DecimalDigits – for a TVP this is the number of columns in the TVP
NULL, // ParameterValuePtr – for a TVP this is the type name of the TVP
// (not needed with stored proc)
NULL, // BufferLength – for a TVP this is the length of the type name or SQL_NTS
// (not needed with stored proc)
&cbTVP); // StrLen_or_IndPtr – for a TVP this is the number of rows available
2) 对TVP参数内的每个Column绑定数据（实际就是一个包含该Column多行数据的数组）分2步：
– 先使用SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS …) 聚焦到TVP参数，
– 然后调用SQLBindParameter依次绑定TVP内部的各个Column
SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)m_nBindParam, SQL_IS_INTEGER);
SQLBindParameter(m_hstmt, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, items.userid, sizeof(SQLBIGINT), NULL);
SQLBindParameter(m_hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.dbid, sizeof(SQLINTEGER), NULL);
SQLBindParameter(m_hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.result, sizeof(SQLINTEGER), NULL);
SQLBindParameter(m_hstmt, 4, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.score, sizeof(SQLINTEGER), NULL);
SQLBindParameter(m_hstmt, 5, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_BIGINT, 0, 0, items.money, sizeof(SQLBIGINT), NULL);
SQLBindParameter(m_hstmt, 6, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.experience, sizeof(SQLINTEGER), NULL);
SQLBindParameter(m_hstmt, 7, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, items.tablefee, sizeof(SQLINTEGER), NULL);
3) 绑定完TVP内部字段后，再次调用SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS …) 重置Focus.
// Reset param focus
SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_PARAM_FOCUS, (SQLPOINTER)0, SQL_IS_INTEGER);
// Initialize TVP row count
cbTVP = (SQLUINTEGER)uicts;
4. 注意事项
1） TVP (Table Valued Parameter)是SQL Server 2008 R2引入的新特性，之前的版本不支持。
2） ODBC驱动的版本是从10.0开始支持TVP参数，所以使用TVP操作SQL Server的程序运行环境上需要安装10.0或以上版本的数据库连接驱动。
注：可以通过运行odbc数据源管理程序查看本机安装的odbc驱动版本。
3） 关于odbc错误“[Microsoft][ODBC SQL Server Driver]无效的参数类型”
出现该错误，说明我们的程序使用的是10.0以前的odbc驱动访问的SQL Server，需要在创建DB连接的地方，将DB连接串中DRIVER指定10.0或以上版本，如下所示：
"DRIVER={SQL Server Native Client 10.0};SERVER=x.x.x.x;DATABASE=MiniGameLogNewDB;UID=sa;PWD=123456;"
4) 关于” [Microsoft][SQL Server Native Client 10.0]Dialog failed”错误
当出现该错误时，请将SQLDriverConnect()函数的最后一个参数由SQL_DRIVER_COMPLETE改为SQL_DRIVER_NOPROMPT