sqlServer 存储过程编写和调用

来源：互联网发布：自行车风火轮如何编程编辑：程序博客网时间：2024/05/21 17:04

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

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

1：创建表：

SET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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 ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[GSP_GG_Add_Account]@iUserID int OUTPUT,--玩家唯一标识@szUserName varchar(32),@szPassWord varchar(32),@CreateTime datetimeASSET NOCOUNT ONBEGIN TRAN--家族名称是否已经被用IF EXISTS(SELECT @iUserID FROM tbluAccount WHERE UserID = @iUserID)BEGIN ROLLBACK TRANRETURN -1ENDIF @iUserID < 0 BEGINROLLBACK TRANRETURN -2ENDSET @iUserID = @@IDENTITYINSERT INTO tbluAccount(UserName,PassWord,CreateTime) VALUES (@szUserName,@szPassWord,@CreateTime);COMMIT TRANSET ANSI_NULLS ONSET QUOTED_IDENTIFIER ON

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

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

declare @iUserID intdeclare @Now datetimeSET @Now = GetDate()EXEC [dbo].[GSP_GG_Add_Account] @iUserID OUTPUT,'lg8','lg8',@NowSELECT @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();}

0 0