如何为动态创建的数据库用SQL语句创建存储过程

goodhal 2003-07-14 03:09:40
如题，例如
DECLARE @sDatabaseName varchar(100) --动态的数据库名
用存储过程动态创建出一数据库(数据库名是由参数动态指定的)，
但如何为这个动态创建出的数据库创建一个存储过程?

...全文
254 29 打赏 收藏 转发到动态 举报
写回复
用AI写文章
29 条回复
切换为时间正序
请发表友善的回复…
发表回复
fayifu 2003-07-17
  • 打赏
  • 举报
 回复
1.执行时，将连接字符串作适当调整;
2.执行时，可得到正确结果。但有两处报错未解决，如你解决后，麻烦告诉我正确方法。

/*
存储过程名称：初始化新数据库
功能描述：1.创建新数据库
2.建表、存储过程等
编制者：fayifu
完成日期：2003.07.17
*/
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'InitializeNewDatabase' AND type = 'P')
DROP PROCEDURE InitializeNewDatabase

GO
CREATE PROCEDURE InitializeNewDatabase
@strDbName varchar(20) --创建的数据库名

AS
--SET NOCOUNT ON
DECLARE @Conn int, --创建XLS的ADO.Connection连接对象
@nHr int, --OLE返回值
@strSrc varchar(255), --OLE错误源
@strDesc varchar(255), --OLE错误描述
@strConn varchar(255), -- OLE DB Connection string for Jet 4 Excel ISAM
@strSql varchar(8000) --INSERT INTO XLS T-SQL

--创建XLS的ADO连接字符串
SET @strConn = 'DSN=DlSecond;UID=sa;PWD=;
APP=Microsoft(R) Windows (R) 2000 Operating System;WSID=DSY-WU;
DATABASE=DlDataSecond'
--创建ADO.Connection对象
EXEC @nHr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @nHr <> 0
BEGIN --返回OLE错误
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4),@nHr), Source = @strSrc, Description = @strDesc
RETURN
END

--设置ADO.Connection对象连接字符串属性
EXEC @nHr = sp_OASetProperty @Conn, 'ConnectionString', @strConn
IF @nHr <> 0
BEGIN --返回OLE错误
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4), @nHr), Source = @strSrc, Description = @strDesc
RETURN
END

--调用ADO.Connection对象的方法Open打开ADO连接
EXEC @nHr = sp_OAMethod @Conn, 'Open'
IF @nHr <> 0
BEGIN --返回OLE错误
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4),@nHr), Source = @strSrc, Description = @strDesc
RETURN
END

SET @strSql = 'CREATE DATABASE ' + @strDbName

EXEC @nHr = sp_OAMethod @Conn, 'Execute', NULL, @strSql, NULL, 129 --adCmdText + adExecuteNoRecords
-- IF @nHr <> 0
-- BEGIN
-- EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
-- SELECT Error = convert(varbinary(4), @nHr), Source = @strSrc, Description = @strDesc
-- RETURN
-- END

--删除ADODB.Connection对象
EXEC @nHr = sp_OADestroy @Conn
IF @nHr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4), @nHr), Source = @strSrc, Description = @strDesc
RETURN
END

--创建XLS的ADO连接字符串
SET @strConn = 'DSN=DlSecond;UID=sa;PWD=;
APP=Microsoft(R) Windows (R) 2000 Operating System;WSID=DSY-WU;
DATABASE=' + @strDbName
--创建ADO.Connection对象
EXEC @nHr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @nHr <> 0
BEGIN --返回OLE错误
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4),@nHr), Source = @strSrc, Description = @strDesc
RETURN
END

--设置ADO.Connection对象连接字符串属性
EXEC @nHr = sp_OASetProperty @Conn, 'ConnectionString', @strConn
IF @nHr <> 0
BEGIN --返回OLE错误
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4), @nHr), Source = @strSrc, Description = @strDesc
RETURN
END

--调用ADO.Connection对象的方法Open打开ADO连接
EXEC @nHr = sp_OAMethod @Conn, 'Open'
IF @nHr <> 0
BEGIN --返回OLE错误
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4),@nHr), Source = @strSrc, Description = @strDesc
RETURN
END

SET @strSql = 'CREATE PROCEDURE Test AS SELECT TestCol = 4 '

EXEC @nHr = sp_OAMethod @Conn, 'Execute', NULL, @strSql, NULL, 129 --adCmdText + adExecuteNoRecords
IF @nHr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4), @nHr), Source = @strSrc, Description = @strDesc
RETURN
END

--删除ADODB.Connection对象
EXEC @nHr = sp_OADestroy @Conn
IF @nHr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @strSrc OUT, @strDesc OUT
SELECT Error = convert(varbinary(4), @nHr), Source = @strSrc, Description = @strDesc
RETURN
END

--SET NOCOUNT OFF

GO

goodhal 2003-07-17
  • 打赏
  • 举报
 回复
我试了你的代码，确实可行，不过只提示了一个错误：
Error:0x80042732
Source:ODSOLE Extended Procedure
Description:结果集中不允许有对象类型的输出值。

将代码中的
EXEC @nHr = sp_OAMethod @Conn, 'Execute', NULL, @strSql, NULL, 129 --adCmdText + adExecuteNoRecords

改成：
DECLARE @nTemp int
EXEC @nHr = sp_OAMethod @Conn, 'Execute', @nTemp OUT, @strSql
即可

困扰我多日的问题，终于可以解决了。
多谢!
goodhal 2003-07-16
  • 打赏
  • 举报
 回复
to fayifu(fayifu)
按你说的代码，在查询分析器中执行，提示有语法错误。
fayifu 2003-07-16
  • 打赏
  • 举报
 回复
在存储过程中好象是不能使用USE语句的。但要在存储过程中再建另一数据库存储过程可以这样。
declare @strSql varchar(8000),
@strDbName varchar(50)

set @strSql = 'create proc ' + @strDbName + '..过程名'
.......
as
.......
fayifu 2003-07-16
  • 打赏
  • 举报
 回复
有办法在存储过程中解决，但执行速度很慢，建库平均15秒，不实用。而且建库成功率为90%，我在VC下还不能捕获异常。
goodhal 2003-07-15
  • 打赏
  • 举报
 回复
to qiubolecn(来自差生市)
有没有用SQLDMO调用一个SQL执行语句的例子（最好能是CB的）。感觉用SQLDMO有些麻烦。
goodhal 2003-07-15
  • 打赏
  • 举报
 回复
to :fjdelphiboy(努力学习中的菜鸟)
　　这样做确实可以，但如前面所说，这样就无法使用触发器调用存储过程由数据库系统自动完成。
goodhal 2003-07-15
  • 打赏
  • 举报
 回复
期待大家的回答......
fjdelphiboy 2003-07-15
  • 打赏
  • 举报
 回复
to goodhal:
to pengdali(大力 V2.0)
　　我现在需要实现的一个功能如下：有一个数据累增的数据库Data，考虑数据量很大，
所以由主程序按照一定的存储策略（如：每月保存一个数据）按时间对其划分，因此就
以Data_2003_7、Data_2003_8等名称，动态的创建数据库，这些数据库的表的数据结构
都是相同的，所以对这些表的增加修改操作若能做成存储过程，会提高效率。
　　动态创建数据库因为需要将数据库名以参数的形式传递，所以最好也能做成存储过程。
动态创建数据库是没有问题的，但在动态创建出来的数据库中再动态创建存储过程就不行了。

另：
用你的Delphi程序，SQL文件如下：
USE DB_ES_CData_1_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]
@a_iPoint_Id int,@a_iMV_Id int , @a_iSPI_SysId int,@a_dtCreateDT datetime
AS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
会出现异常，提示SQL语句有语法错误


Re:
memo1.text:='USE DB_ES_CData_1_1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]'
adoquery1.Close;
adoquery1.SQL.Text:=memo1.text;
adoquery1.ExecSQL;
memo1.text:='CREATE PROCEDURE [dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]
@a_iPoint_Id int,@a_iMV_Id int , @a_iSPI_SysId int,@a_dtCreateDT datetime
AS'
adoquery1.Close;
adoquery1.SQL.Text:=memo1.text;
adoquery1.ExecSQL;

应该可以你试试看。


goodhal 2003-07-15
  • 打赏
  • 举报
 回复
to wei9625()
sql脚本确实可以在存储过程中执行，但要实现如题的功能不行。如果你有好的办法，不妨给出解决的代码。
qiubolecn 2003-07-15
  • 打赏
  • 举报
 回复
强烈推荐大家用SQLDMO，其实簋多问题，只要我们用用SQLDMO，就会发现，任何问题都将不成为问题，比如大力写的更新的方法，得先去掉go，如果其中有set..之类的，还是不好用。
如果用SQLDMO，就和在查询分析器中执行的一样。。。
wei9625 2003-07-15
  • 打赏
  • 举报
 回复
sql脚本也可以在存储过程中执行,不必在程序中执行,所以可以用一个存储过程实现
goodhal 2003-07-15
  • 打赏
  • 举报
 回复
to zjcxc(邹建)
　　在我们的系统中有一张主表T_SysMain，主表中每新增一条记录就动态创建一个数据库，把所有的工作都放在存储过程中，就可以由触发器自动触发。
　　你说的方法是我们的备用方案之一，但我们感觉不是最优方案。
我们想把与数据自身相关的逻辑与应用程序尽量的分开。
zjcxc 2003-07-15
  • 打赏
  • 举报
 回复
我现在需要实现的一个功能如下：有一个数据累增的数据库Data，考虑数据量很大，所以由主程序按照一定的存储策略（如：每月保存一个数据）按时间对其划分，因此就以Data_2003_7、Data_2003_8等名称，动态的创建数据库，这些数据库的表的数据结构都是相同的，所以对这些表的增加修改操作若能做成存储过程，会提高效率。
　　动态创建数据库因为需要将数据库名以参数的形式传递，所以最好也能做成存储过程。


既然你的数据库的结构都是相同的,用这样的处理方法不行吗?
1.生成一个SQL脚本,里面包含所有的数据库脚本(不包含建库脚本)

2.在你的程序中写一个过程aa,参数包括:conn(数据库连接对象),sqlfile(数据库脚本文件)
作用就是在 conn 中执行 sqlfile 文件中的sql脚本

3.在你的程序中再写一个过程bb,参数包括:conn(服务连接对象),dbname(要创建的数据库名)
实现如下的功能:
a.用conn.execute "create database ["+dbname+"]" 实现创建数据库
b.用conn.defaultdatabase=dbname 实现将当前数据库切换到新创建的数据库
c.调用2.中创建的过程aa,完成数据结构的创建

这样不是就达到你的目的了吗?为什么一定要将所有的工作都放在存储过程中去实现呢?
goodhal 2003-07-14
  • 打赏
  • 举报
 回复
to fjdelphiboy(努力学习中的菜鸟)
你贴的代码在查询分析器中可以执行，但是做成存储过程就不行了，注意'xxx'为参数。
goodhal 2003-07-14
  • 打赏
  • 举报
 回复
to pengdali(大力 V2.0)
　　我现在需要实现的一个功能如下：有一个数据累增的数据库Data，考虑数据量很大，所以由主程序按照一定的存储策略（如：每月保存一个数据）按时间对其划分，因此就以Data_2003_7、Data_2003_8等名称，动态的创建数据库，这些数据库的表的数据结构都是相同的，所以对这些表的增加修改操作若能做成存储过程，会提高效率。
　　动态创建数据库因为需要将数据库名以参数的形式传递，所以最好也能做成存储过程。
动态创建数据库是没有问题的，但在动态创建出来的数据库中再动态创建存储过程就不行了。

另：
用你的Delphi程序，SQL文件如下：
USE DB_ES_CData_1_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[P_Create_T_CDataInfo_SPISysId_Year_Month_Day_Hour]
@a_iPoint_Id int,@a_iMV_Id int , @a_iSPI_SysId int,@a_dtCreateDT datetime
AS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
会出现异常，提示SQL语句有语法错误

　
pengdali 2003-07-14
  • 打赏
  • 举报
 回复
完全没有必要用过程!

如delphi:

s:string;
sqlfile:textfile;

AssignFile(sqlfile, 'update.sql');
Reset(sqlfile);
while not eof(sqlfile) do
begin
Readln(sqlfile, s);
sqltext:=s;
while (not eof(sqlfile)) and (uppercase(trim(s))<>'GO') do
begin
Readln(sqlfile, s);
if (uppercase(trim(s))<>'GO') then
sqltext:=sqltext+' '+s;
end;
adoquery1.Close;
adoquery1.SQL.Clear;
adoquery1.SQL.Add(sqltext);
adoquery1.ExecSQL;
end;
CloseFile(sqlfile);

application.MessageBox('数据库升级完成！','提示',MB_OK+MB_ICONINFORMATION);

//注：上述代码要求update.sql文件中语句无注释
pengdali 2003-07-14
  • 打赏
  • 举报
 回复
你在你的代码里直接exec('create dateabase '+@sDatabaseName)不就是了？

如果你是用前台开发工具来做，那你就象用update语句一样来调用create datebase
fjdelphiboy 2003-07-14
  • 打赏
  • 举报
 回复
set @sDatabaseName='xxx'
exec('create dateabase '+@sDatabaseName)
use xxx
exec('create dateabase '+@sDatabaseName)
goodhal 2003-07-14
  • 打赏
  • 举报
 回复
真的不能解决吗？
加载更多回复（9）
SQL 语句存储过程
里面包括动态创建数据库，动态查询，登录存储过程等，这些SQL语句都是动态应用的，不需要在使用Use指令，在一个数据库，就可以操作所有数据库
精通SQL--结构化查询语言详解
15.3.5 使用enterprise manager创建存储过程和函数 315 15.3.6 修改和删除存储过程和函数 317 15.4 oracle中的流控制语句 319 15.4.1 条件语句 319 15.4.2 循环语句 320 15.4.3 标号和goto 322 15.5 oracle...
创建动态MSSQL数据库表
下面是利用SQL语句创建数据库、表、存储过程、视图、索引、规则、修改表、查看数据等的方法。
【数据库】SQL语句大全
一、基础1、创建数据库2、删除数据库3、备份sql server4、创建新表根据已有的表创建新表： 5、删除新表6、增加一个列注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的...
数据库常用的sql语句大全--sql
前言 本片博客使用mysql数据库进行数据操作，使用Navicat for mysql 这个IDE进行可视化操作。每个SQL语句都是亲身实验验证的，并且经过自己的思考的。...创建数据库 create database <数据库名
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章