我写了一个创建数据库的存储过程:如下:
create procedure SP_DATABASE as
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'whjxc1')
DROP DATABASE [whjxc1]
GOCREATE DATABASE [whjxc1] ON (NAME = N'whjxc_Data1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\whjxc1_Data.MDF' , SIZE = 89, FILEGROWTH = 10%) LOG ON (NAME = N'whjxc1_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\whjxc1_Log.LDF' , SIZE = 160, FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
当我创建这个存储过程时,自动执行这个存储过程,创建一个数据库。可是当我在程序中调用时却没有任何变化。而我在SQL Server的查询分析器中用EXECUTE SP_DATABASE执行也无任何变化,请各位大侠指导一下,这究竟是怎么回事,我的目的是想用存储过程来创建一个数据库,在前台调用。欢迎大家前来指导,谢谢!
create procedure SP_DATABASE as
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'whjxc1')
DROP DATABASE [whjxc1]
GOCREATE DATABASE [whjxc1] ON (NAME = N'whjxc_Data1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\whjxc1_Data.MDF' , SIZE = 89, FILEGROWTH = 10%) LOG ON (NAME = N'whjxc1_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\whjxc1_Log.LDF' , SIZE = 160, FILEGROWTH = 10%)
COLLATE Chinese_PRC_CI_AS
GO
当我创建这个存储过程时,自动执行这个存储过程,创建一个数据库。可是当我在程序中调用时却没有任何变化。而我在SQL Server的查询分析器中用EXECUTE SP_DATABASE执行也无任何变化,请各位大侠指导一下,这究竟是怎么回事,我的目的是想用存储过程来创建一个数据库,在前台调用。欢迎大家前来指导,谢谢!
下面的示例创建名为 Sales 的数据库。因为没有使用关键字 PRIMARY,第一个文件 (Sales_dat) 成为主文件。因为 Sales_dat 文件的 SIZE 参数没有指定 MB 或 KB,因此默认为 MB,以兆字节为单位进行分配。Sales_log 文件以兆字节为单位进行分配,因为 SIZE 参数中显式声明了 MB 后缀。USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
我这么写一个带参数的创建数据库的语句:
create procedure sp_database
@dbname char(30)
as
begin
create database @dbname
end;
为什么提示 @dbname附近有语句错误呢?请各位大侠前来指导!先谢谢你们了。
可是我要写一个带参数的存储过程,为什么会出现如上的错误呢?什么地方错误了呢?希望你能指导指导我。谢谢你!
@dbname char(30)
as
create database @dbname
@ds varchar(30)
AS
create database [@ds]
@dsname varchar(30)
AS
create database [@dsname]
var
dbname:string;
begin
dbname:=whdata.xt_ztb.fieldbyname('name').AsString;
with whdata.sp_database do
begin
params.Clear;
params.CreateParam(ftstring,'@dbname',ptinput).AsString:=dbname;
try
execute;
except
;
end;
end;
...end;
为什么程序一运行总是出现:
[Error] ztgl.pas(73): Undeclared identifier: 'ftstring'
[Error] ztgl.pas(73): Undeclared identifier: 'ptinput'
两个错误呢?
@dbname char(30)
as
begin
Exec('create database'+ ' '+ @dbname)
end sp_databaseTest 'Test'(Exec也可以执行多条语句,之间加空格即可)
三层不会,提示是变量没有定义 ?:)
with whdata.sp_database do
begin
params.Clear;
params.ParamByName('@dbname').AsString:=dbname;
try
execute;
except
;
end;
end;也提示错误:提示dbname在sp_database中未被发现,而存储过程里确实有dbname啊?不知为什么?请各位高手指点!
ExecSql ;可以了
begin
Close ;
Parameters.CreateParameter('DbName',ftstring,pdinput,20,null);
sql.Text:='sp_databaseTest :DbName' ;
Parameters.ParamByName('DbName').value:='Tests' ;
ExecSql ;
end ;
创建存储过程:
CREATE PROCEDURE sp_database
@dbname varchar(30)
AS
create database[@dbname]
GO
在程序中调用:
var
dbname:string;
begin
whdata.xt_ztb.Edit;
dbname:=whdata.xt_ztb.fieldbyname('name').AsString;//这都没问题,能取出值。
whdata.sp_database.CommandText:='sp_database';
with whdata.sp_database do
begin
params.Clear;
params.ParamByName('@dbname').AsString:=dbname;
try
execute;
except
;
end;
end;
end;//错误提示:'sp_database':parameter '@dbname' not found.实在是不知道哪儿错了,请大家帮我找找吧!非常感谢!
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, StdCtrls;type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
ADOStoredProc1: TADOStoredProc;
Button1: TButton;
ADOQuery1: TADOQuery;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
begin
//调用存储过程
with ADOStoredProc1 do
begin
Parameters.CreateParameter('s',ftString,Pdinput,20,null) ;
Parameters.ParamByName('s').Value := 'ADOStored' ;
ExecProc ;
end ; with ADOQuery1 do
begin
Close ;
Parameters.CreateParameter('TheDbName',ftstring,pdinput,20,null);
Parameters.ParamByName('TheDbName').value:='ADOQueryTest' ;
sql.Text:='sp_databaseTest :TheDbName' ;
ExecSql ;
end ;
end;
end.----------------
create procedure sp_databaseTest
@dbname char(30)
as
begin
Exec('create database'+ ' '+ @dbname)
end GO没错的呀
--------------------------unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, DBTables;type
TForm1 = class(TForm)
Database1: TDatabase;
Button1: TButton;
Query1: TQuery;
StoredProc1: TStoredProc;
Button2: TButton;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
begin
with Query1 do
begin
Close ;
Sql.text := 'sp_databaseTest :k' ;
paramByName('k').asstring := 'kk' ;
execSql ;
end ;
end;procedure TForm1.Button2Click(Sender: TObject);
begin
with StoredProc1 do
begin
Close ;
StoredProc1.StoredProcName := 'sp_databaseTest' ;
StoredProc1.Params.CreateParam(ftString,'@dbname',ptinput) ;
ParamByName('@dbname').AsString := 'kkkk' ;
ExecProc ;
end ;
end;
end.
begin
with ADOCommand1 do
begin
CommandText := 'sp_databaseTest :TheDbName' ;
ADOCommand1.Parameters.ParamByName('TheDbName').Value := 'ss' ;
Execute ;
end ;
end;也可以的
我试过了,按照你写的一点问题都没有,我再试着改三层看一看。
JJ 的 DD :)
应该这样:
var
dbname:TField;//需要在uses中引用db。
begin
whdata.xt_ztb.Edit;
dbname:=whdata.xt_ztb.fieldbyname('name');
whdata.sp_database.CommandText:='sp_database';
with whdata.sp_database do
begin
Params.Clear;
Params.CreateParam(ftString,'@dbname',ptInput).AsString:=trim(dbname.AsString);
try
execute;
except
;
end;
end;
end;