请教大家,下面的SQl语句有什么错误? 表名不能在语句中做参数!如果要用作参数只能这样用,如:set @tablename='mytable'set @sql="select * from '+@tablenameexec(@sql) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 要把变量的定义写进语句里EXECUTE sp_executesql @SQlString,N'varchar @DataFile NVarchar(100),@DataFile NVarchar(100)',@DataFileName=@DataFile,@LogFileName=@LogFile To OpenVMS:变量的定义写了呀!写进语句也没有用。 你要改成下面的样子USE masterGOif exists (select * from sysdatabases where name='mydb1') DROP DATABASE mydb1GO--Get SQL Server Setup Pathdeclare @path varchar(200)exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLSERVER\setup', 'SQLPath',@path outputset @path = @path + '\data\'print @pathdeclare @SQLString Nvarchar(500)declare @ParamDefinition Nvarchar(255)declare @DataFile NVarchar(100)declare @LogFile NVarchar(100)set @SQLString = N'CREATE DATABASE mydb1 ON ( NAME = mydb1_dat, FILENAME = '+@DataFileName+', --看到了吗,这里错了 SIZE = 100, MAXSIZE = 500, FILEGROWTH = 10 ) LOG ON ( NAME = ''mydb1_log'', FILENAME = '+@LogFileName+', --看到了吗,还有这里错了 SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) 'set @ParamDefinition=N'@DataFileName nvarchar(100), @LogFileName nvarchar(100)'set @DataFile = @path+'\data\mydb1Dat.mdf'set @LogFile = @path+'\data\mydb1Log.mdf'EXECUTE sp_executesql @SQlString,@ParamDefinition,@DataFileName=@DataFile,@LogFileName=@LogFile USE masterGOif exists (select * from sysdatabases where name='mydb1') DROP DATABASE mydb1GO--Get SQL Server Setup Pathdeclare @path varchar(200)exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLSERVER\setup', 'SQLPath',@path outputset @path = @path + '\data\'print @pathdeclare @SQLString Nvarchar(500)set @SQLString = N'CREATE DATABASE mydb1 ON ( NAME = ''mydb1_dat'', FILENAME='''+@path+'mydb1Dat.mdf'', SIZE = 100, MAXSIZE = 500, FILEGROWTH = 10 ) LOG ON ( NAME = ''mydb1_log'', FILENAME =''' +@path+'mydb1Log.mdf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) 'print @SQLStringexec(@SQLString) 表名数据库名不能在语句中做参数的以下测试成功:USE masterGOif exists (select * from sysdatabases where name='mydb1') DROP DATABASE mydb1GO--Get SQL Server Setup Pathdeclare @path varchar(200)exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLSERVER\setup', 'SQLPath',@path outputset @path = @path + '\data\'print @pathdeclare @SQLString Nvarchar(1000)declare @ParamDefinition Nvarchar(255)declare @DataFile NVarchar(100)declare @LogFile NVarchar(100)set @DataFile = @path+'mydb1Dat.mdf'set @LogFile = @path+'mydb1Log.ldf'set @SQLString = N'CREATE DATABASE mydb1 ON ( NAME = mydb1_dat, FILENAME ='''+@DataFile+''', SIZE = 100, MAXSIZE = 500, FILEGROWTH = 10 ) LOG ON ( NAME = ''mydb1_log'', FILENAME ='''+@LogFile+''', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) 'EXEC(@sqlstring) 表名数据库名不能在语句中做参数的以下测试成功:USE masterGOif exists (select * from sysdatabases where name='mydb1') DROP DATABASE mydb1GO--Get SQL Server Setup Pathdeclare @path varchar(200)exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLSERVER\setup', 'SQLPath',@path outputset @path = @path + '\data\'print @pathdeclare @SQLString Nvarchar(1000)declare @ParamDefinition Nvarchar(255)declare @DataFile NVarchar(100)declare @LogFile NVarchar(100)set @DataFile = @path+'mydb1Dat.mdf'set @LogFile = @path+'mydb1Log.ldf'set @SQLString = N'CREATE DATABASE mydb1 ON ( NAME = mydb1_dat, FILENAME ='''+@DataFile+''', SIZE = 100, MAXSIZE = 500, FILEGROWTH = 10 ) LOG ON ( NAME = ''mydb1_log'', FILENAME ='''+@LogFile+''', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) 'EXEC(@sqlstring) 如何用sql语句删除表中重复的记录? 如何查询使结果为空 求一sql 语句 想用SQL语句查询包含 京工商字[2007]100号 的记录怎么写? 怎样删除试图中某一条记录啊?? 有谁能帮忙!解决! ??急等版主和高手帮解决一个困我很久的SQL语句问题,谢谢! 查询某时间段内的记录的问题,很急哦! 在sql server中可否动态执行sql语句 SELECT中加入一个字段判断是否是最后一条记录 请问VFP小问题。 请问这个语句怎么写? 关于sql的 90分!!!
EXECUTE sp_executesql @SQlString,N'varchar @DataFile NVarchar(100),@DataFile NVarchar(100)',@DataFileName=@DataFile,@LogFileName=@LogFile
变量的定义写了呀!
写进语句也没有用。
USE master
GO
if exists (select * from sysdatabases where name='mydb1')
DROP DATABASE mydb1
GO
--Get SQL Server Setup Path
declare @path varchar(200)
exec master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLSERVER\setup',
'SQLPath',@path output
set @path = @path + '\data\'
print @path
declare @SQLString Nvarchar(500)
declare @ParamDefinition Nvarchar(255)
declare @DataFile NVarchar(100)
declare @LogFile NVarchar(100)set @SQLString =
N'CREATE DATABASE mydb1
ON
( NAME = mydb1_dat,
FILENAME = '+@DataFileName+', --看到了吗,这里错了
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 10 )
LOG ON
( NAME = ''mydb1_log'',
FILENAME = '+@LogFileName+', --看到了吗,还有这里错了
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
'
set @ParamDefinition=N'@DataFileName nvarchar(100), @LogFileName nvarchar(100)'
set @DataFile = @path+'\data\mydb1Dat.mdf'
set @LogFile = @path+'\data\mydb1Log.mdf'EXECUTE sp_executesql @SQlString,@ParamDefinition,@DataFileName=@DataFile,@LogFileName=@LogFile
GOif exists (select * from sysdatabases where name='mydb1')
DROP DATABASE mydb1
GO--Get SQL Server Setup Path
declare @path varchar(200)
exec master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLSERVER\setup',
'SQLPath',@path outputset @path = @path + '\data\'
print @pathdeclare @SQLString Nvarchar(500)
set @SQLString =
N'CREATE DATABASE mydb1
ON
( NAME = ''mydb1_dat'',
FILENAME='''
+@path+'mydb1Dat.mdf'',
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 10 )
LOG ON
( NAME = ''mydb1_log'',
FILENAME ='''
+@path+'mydb1Log.mdf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
'
print @SQLStringexec(@SQLString)
以下测试成功:USE masterGOif exists (select * from sysdatabases where name='mydb1')
DROP DATABASE mydb1
GO--Get SQL Server Setup Path
declare @path varchar(200)
exec master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLSERVER\setup',
'SQLPath',@path output
set @path = @path + '\data\'
print @pathdeclare @SQLString Nvarchar(1000)
declare @ParamDefinition Nvarchar(255)
declare @DataFile NVarchar(100)
declare @LogFile NVarchar(100)
set @DataFile = @path+'mydb1Dat.mdf'
set @LogFile = @path+'mydb1Log.ldf'
set @SQLString =
N'CREATE DATABASE mydb1
ON
( NAME = mydb1_dat,
FILENAME ='''+@DataFile+''',
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 10 )
LOG ON
( NAME = ''mydb1_log'',
FILENAME ='''+@LogFile+''',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
'EXEC(@sqlstring)
以下测试成功:USE masterGOif exists (select * from sysdatabases where name='mydb1')
DROP DATABASE mydb1
GO--Get SQL Server Setup Path
declare @path varchar(200)
exec master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLSERVER\setup',
'SQLPath',@path output
set @path = @path + '\data\'
print @pathdeclare @SQLString Nvarchar(1000)
declare @ParamDefinition Nvarchar(255)
declare @DataFile NVarchar(100)
declare @LogFile NVarchar(100)
set @DataFile = @path+'mydb1Dat.mdf'
set @LogFile = @path+'mydb1Log.ldf'
set @SQLString =
N'CREATE DATABASE mydb1
ON
( NAME = mydb1_dat,
FILENAME ='''+@DataFile+''',
SIZE = 100,
MAXSIZE = 500,
FILEGROWTH = 10 )
LOG ON
( NAME = ''mydb1_log'',
FILENAME ='''+@LogFile+''',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
'EXEC(@sqlstring)