sqlServer 存储过程编写和调用

只是简单的存储过程的编写，基本的语法，留个记录。

以下是一个简单的用户表的创建，和存储过程的添加用户，sql的调用的笔记

1：创建表：

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbluAccount](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](32) NOT NULL,
	[PassWord] [varchar](32) NOT NULL,
	[CreateTime] [datetime] NULL,
 CONSTRAINT [PK_tbluAccount] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

2：添加账号的存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GSP_GG_Add_Account]
	@iUserID int OUTPUT,				--玩家唯一标识
	@szUserName varchar(32),
	@szPassWord varchar(32),
	@CreateTime datetime
AS
	SET NOCOUNT ON
	BEGIN TRAN
	--家族名称是否已经被用
	IF EXISTS(SELECT @iUserID FROM tbluAccount WHERE UserID = @iUserID)
	BEGIN 
		ROLLBACK TRAN
		RETURN -1
	END

	IF @iUserID < 0 
	BEGIN
		ROLLBACK TRAN
		RETURN -2
	END
	
	SET @iUserID = @@IDENTITY

	INSERT INTO tbluAccount(UserName,PassWord,CreateTime) VALUES (@szUserName,@szPassWord,@CreateTime);
	

	COMMIT TRAN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

注意此时的@iUserID 是从0开始滴。。。

3：sqlserver中的调用存储过程测试：

declare @iUserID int
declare @Now datetime
SET @Now = GetDate()

EXEC [dbo].[GSP_GG_Add_Account] @iUserID OUTPUT,'lg8','lg8',@Now

SELECT @iUserID

4：C++中调用存储过程

CDBSqlServer sqlServer;
SQLWCHAR * pDSN = L"DRIVER={SQL Server};SERVER=SH-LIUGAO;DATABASE=uu278;UID=lg2;PWD=lg456&&;";

STACCOUNTINFO stAccountInfo;
bool bOK = sqlServer.DriverConnectSqlServer(pDSN);
if (bOK)
{
	int iEorrID = 0;
	int iUserID = 3;
		
	sqlServer.InitBindParam();
	sqlServer.BindParam(iEorrID, SQL_PARAM_OUTPUT);
	sqlServer.BindParam(iUserID);

	SQLWCHAR * strSql3 = L"{? = call dbo.GSP_GG_AccountInfo_GetInfo(?)}";
	bool bRet = sqlServer.ExecuteDirect(strSql3);
	sqlServer.InitBindCol();
	sqlServer.BindCol(stAccountInfo.iUserID);
	sqlServer.BindCol(stAccountInfo.szUserName, MAX_NAME_LEN+1);
	sqlServer.BindCol(stAccountInfo.szUserPWD, MAX_NAME_LEN + 1);
	sqlServer.BindCol(stAccountInfo.stCreateTime);

	bool bOK = sqlServer.Fetch();
	int id = 0;
	if (bOK)
	{
		id  = stAccountInfo.iUserID;
	}

	sqlServer.ClearMoreResult();
		
}