declare @sql varchar(8000) set @sql='create database use'+convert(varchar(6),getdate(),112) exec(@sql)
SQL code动态sql语句基本语法 1 :普通SQL语句可以用Exec执行 eg: Select * from tableName Exec('select * from tableName') Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg: declare @fname varchar(20) set @fname = 'FiledName' Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。 Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 declare @fname varchar(20) set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3. 输出参数 declare @num int, @sqls nvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中? declare @num int, @sqls nvarchar(4000) set @sqls='select @a=count(*) from tableName ' exec sp_executesql @sqls,N'@a int output',@num output select @num
老大的 第一个是全备,第二个是日志备份,在job里设置具体执行时间间隔. Create PROC P_DB_FULL_BACKUP @DBNAME NVARCHAR(500), @PATH NVARCHAR(500) AS /* Create : [email protected] DATE : 2007 DESC :DB FULL BACKUP EXAMPLE: EXEC P_DB_FULL_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup\' */ DECLARE @SQL NVARCHAR(4000) DECLARE @DATE NVARCHAR(600) DECLARE @FILENAME NVARCHAR(1000) IF ISNULL(@DBNAME,'')='' or ISNULL(@PATH,'')='' BEGIN RETURN END SET @DATE=REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,GETDATE(),120),' ','.'),'-',''),':','') SET @PATH=@PATH+N'\' SET @FILENAME = @DBNAME+N'_FULL_BACK_'+@DATE+N'.bak'
SET @SQL = N' BACKUP DATABASE ['+ @DBNAME +N'] TO DISK = N'''+ @PATH+ @FILENAME +''' WITH INIT,NOUNLOAD,NAME = N'''+@DBNAME+N'_FULL_备份'',NOSKIP,STATS=10,NOFORMAT ' EXEC(@SQL) --------------------------------------------------------------------------------
Create PROC P_DB_LOG_BACKUP @DBNAME NVARCHAR(500), @PATH NVARCHAR(500) AS /* Create : [email protected] DATE : 2007 DESC :DB LOG BACKUP EXAMPLE: EXEC P_DB_LOG_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup\' */ DECLARE @SQL NVARCHAR(4000) DECLARE @DATE NVARCHAR(600) DECLARE @FILENAME NVARCHAR(1000) IF ISNULL(@DBNAME,'')='' or ISNULL(@PATH,'')='' BEGIN RETURN END SET @DATE=REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,GETDATE(),120),' ','.'),'-',''),':','') SET @PATH=@PATH+N'\' SET @FILENAME = @DBNAME+N'_LOG_BACK_'+@DATE+N'.bak'
SET @SQL = N' BACKUP LOG ['+ @DBNAME +N'] TO DISK = N'''+ @PATH+ @FILENAME +''' WITH RETAINDAYS = 2, NOINIT,NAME = N'''+@DBNAME+N'_LOG_备份'' ' EXEC(@SQL)
--------------------------------------------------------------------------------------调用: EXEC P_DB_FULL_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup'EXEC P_DB_LOG_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup\' 恢复[1个全备,四个日志]: RESTORE DATABASE DB_Xxx FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_FULL_BACK_20080217.201159.bak' WITH FILE=1,NORECOVERY GORESTORE LOG DB_Xxx FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201220.bak' WITH FILE=1,NORECOVERY GORESTORE LOG DB_Xxx FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201227.bak' WITH FILE=1,NORECOVERY GORESTORE LOG DB_Xxx FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201232.bak' WITH FILE=1,NORECOVERY GORESTORE LOG DB_Xxx FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201237.bak' WITH FILE=1,RECOVERY GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBL_ALARM]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBL_ALARM]( [EXTYPE] [varchar](50) NULL, [DATE] [varchar](50) NULL, [TIME] [varchar](50) NULL, [EXCHID] [varchar](50) NULL, [OBJECT] [varchar](50) NULL, [ALMTXT] [varchar](500) NULL ) ON [PRIMARY] 如果是在动态的数据库里创建存储过程怎么写呢?
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBL_ALARM]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[TBL_ALARM]( [EXTYPE] [varchar](50) NULL, [DATE] [varchar](50) NULL, [TIME] [varchar](50) NULL, [EXCHID] [varchar](50) NULL, [OBJECT] [varchar](50) NULL, [ALMTXT] [varchar](500) NULL ) ON [PRIMARY] 能就这个存储过程写个完整的语句么
declare @sql varchar(8000) set @sql=' IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[TBL_ALARM]'') AND type in (N''U'')) BEGIN CREATE TABLE [dbo].[TBL_ALARM]( [EXTYPE] [varchar](50) NULL, [DATE] [varchar](50) NULL, [TIME] [varchar](50) NULL, [EXCHID] [varchar](50) NULL, [OBJECT] [varchar](50) NULL, [ALMTXT] [varchar](500) NULL ) ON [PRIMARY] end' exec(@SQL)
declare @s varchar(8000) declare @dt varchar(20) set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4) set @s= ' use ' + 'Radar'+ @dt +' create PROCEDURE [GetRecordFromPage_single] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where) as --第12行declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(1000) -- 临时变量 declare @strOrder varchar(500) -- 排序类型if @OrderType != 0 begin set @strTmp = ''<(select min'' set @strOrder = '' order by [''+ @fldName + ''] desc'' end else begin set @strTmp = ''>(select max'' set @strOrder = '' order by ['' + @fldName +''] asc'' endset @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [ ''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)'' + @strOrderif @strWhere != '''' set @strSQL = ''select top '' + str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' ['' + @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')'' + @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1 begin set @strTmp = '''' if @strWhere != '''' set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + '']'' + @strTmp + '' '' + @strOrder endif @IsReCount != 0 set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)' exec(@s) 为什么这个代码运行出来出现这个错误:消息 111,级别 15,状态 1,过程 GetRecordFromPage_single,第 12 行 'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
declare @s varchar(8000) declare @dt varchar(20) set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4) set @s= ' use ' + 'Radar'+ @dt +'go create PROCEDURE [GetRecordFromPage_single] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where) as --第12行declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(1000) -- 临时变量 declare @strOrder varchar(500) -- 排序类型if @OrderType != 0 begin set @strTmp = ''<(select min'' set @strOrder = '' order by [''+ @fldName + ''] desc'' end else begin set @strTmp = ''>(select max'' set @strOrder = '' order by ['' + @fldName +''] asc'' endset @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [ ''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)'' + @strOrderif @strWhere != '''' set @strSQL = ''select top '' + str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' ['' + @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')'' + @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1 begin set @strTmp = '''' if @strWhere != '''' set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + '']'' + @strTmp + '' '' + @strOrder endif @IsReCount != 0 set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)' exec(@s)
declare @s varchar(8000) declare @dt varchar(20) set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4) set @s= ' use ' + 'Radar'+ @dt +'go create PROCEDURE [GetRecordFromPage_single] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where) as --第12行declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(1000) -- 临时变量 declare @strOrder varchar(500) -- 排序类型if @OrderType != 0 begin set @strTmp = ''<(select min'' set @strOrder = '' order by [''+ @fldName + ''] desc'' end else begin set @strTmp = ''>(select max'' set @strOrder = '' order by ['' + @fldName +''] asc'' endset @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [ ''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)'' + @strOrderif @strWhere != '''' set @strSQL = ''select top '' + str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' ['' + @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')'' + @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1 begin set @strTmp = '''' if @strWhere != '''' set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + '']'' + @strTmp + '' '' + @strOrder endif @IsReCount != 0 set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)' exec(@s) 消息 102,级别 15,状态 1,第 1 行 'go' 附近有语法错误。 消息 111,级别 15,状态 1,第 12 行 'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
declare @s varchar(8000) declare @dt varchar(20) set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4) set @s= ' use ' + 'Radar'+ @dt +' go create PROCEDURE [GetRecordFromPage_single] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where) as --第12行declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(1000) -- 临时变量 declare @strOrder varchar(500) -- 排序类型if @OrderType != 0 begin set @strTmp = ''<(select min'' set @strOrder = '' order by [''+ @fldName + ''] desc'' end else begin set @strTmp = ''>(select max'' set @strOrder = '' order by ['' + @fldName +''] asc'' endset @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [ ''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)'' + @strOrderif @strWhere != '''' set @strSQL = ''select top '' + str(@PageSize) + '' * from ['' + @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''(['' + @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' ['' + @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')'' + @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1 begin set @strTmp = '''' if @strWhere != '''' set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from ['' + @tblName + '']'' + @strTmp + '' '' + @strOrder endif @IsReCount != 0 set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)' exec(@s)
set @sql='create database use'+convert(varchar(6),getdate(),112)
exec(@sql)
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
第一个是全备,第二个是日志备份,在job里设置具体执行时间间隔.
Create PROC P_DB_FULL_BACKUP
@DBNAME NVARCHAR(500),
@PATH NVARCHAR(500)
AS
/*
Create : [email protected]
DATE : 2007
DESC :DB FULL BACKUP
EXAMPLE: EXEC P_DB_FULL_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup\'
*/
DECLARE @SQL NVARCHAR(4000)
DECLARE @DATE NVARCHAR(600)
DECLARE @FILENAME NVARCHAR(1000)
IF ISNULL(@DBNAME,'')='' or ISNULL(@PATH,'')='' BEGIN RETURN END
SET @DATE=REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,GETDATE(),120),' ','.'),'-',''),':','')
SET @PATH=@PATH+N'\'
SET @FILENAME = @DBNAME+N'_FULL_BACK_'+@DATE+N'.bak'
SET @SQL = N'
BACKUP DATABASE ['+ @DBNAME +N'] TO DISK = N'''+ @PATH+ @FILENAME +'''
WITH INIT,NOUNLOAD,NAME = N'''+@DBNAME+N'_FULL_备份'',NOSKIP,STATS=10,NOFORMAT
'
EXEC(@SQL) --------------------------------------------------------------------------------
Create PROC P_DB_LOG_BACKUP
@DBNAME NVARCHAR(500),
@PATH NVARCHAR(500)
AS
/*
Create : [email protected]
DATE : 2007
DESC :DB LOG BACKUP
EXAMPLE: EXEC P_DB_LOG_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup\'
*/
DECLARE @SQL NVARCHAR(4000)
DECLARE @DATE NVARCHAR(600)
DECLARE @FILENAME NVARCHAR(1000)
IF ISNULL(@DBNAME,'')='' or ISNULL(@PATH,'')='' BEGIN RETURN END
SET @DATE=REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR,GETDATE(),120),' ','.'),'-',''),':','')
SET @PATH=@PATH+N'\'
SET @FILENAME = @DBNAME+N'_LOG_BACK_'+@DATE+N'.bak'
SET @SQL = N'
BACKUP LOG ['+ @DBNAME +N'] TO DISK = N'''+ @PATH+ @FILENAME +'''
WITH RETAINDAYS = 2, NOINIT,NAME = N'''+@DBNAME+N'_LOG_备份''
'
EXEC(@SQL)
--------------------------------------------------------------------------------------调用:
EXEC P_DB_FULL_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup'EXEC P_DB_LOG_BACKUP N'DB_Xxx',N'D:\data\DB_Xxx\Bakup\' 恢复[1个全备,四个日志]:
RESTORE DATABASE DB_Xxx
FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_FULL_BACK_20080217.201159.bak'
WITH FILE=1,NORECOVERY
GORESTORE LOG DB_Xxx
FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201220.bak'
WITH FILE=1,NORECOVERY
GORESTORE LOG DB_Xxx
FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201227.bak'
WITH FILE=1,NORECOVERY
GORESTORE LOG DB_Xxx
FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201232.bak'
WITH FILE=1,NORECOVERY
GORESTORE LOG DB_Xxx
FROM DISK=N'D:\data\DB_Xxx\Bakup\DB_Xxx_LOG_BACK_20080217.201237.bak'
WITH FILE=1,RECOVERY
GO
例如exec(...)
BEGIN
CREATE TABLE [dbo].[TBL_ALARM](
[EXTYPE] [varchar](50) NULL,
[DATE] [varchar](50) NULL,
[TIME] [varchar](50) NULL,
[EXCHID] [varchar](50) NULL,
[OBJECT] [varchar](50) NULL,
[ALMTXT] [varchar](500) NULL
) ON [PRIMARY]
如果是在动态的数据库里创建存储过程怎么写呢?
BEGIN
CREATE TABLE [dbo].[TBL_ALARM](
[EXTYPE] [varchar](50) NULL,
[DATE] [varchar](50) NULL,
[TIME] [varchar](50) NULL,
[EXCHID] [varchar](50) NULL,
[OBJECT] [varchar](50) NULL,
[ALMTXT] [varchar](500) NULL
) ON [PRIMARY]
能就这个存储过程写个完整的语句么
set @sql='
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[TBL_ALARM]'') AND type in (N''U''))
BEGIN
CREATE TABLE [dbo].[TBL_ALARM](
[EXTYPE] [varchar](50) NULL,
[DATE] [varchar](50) NULL,
[TIME] [varchar](50) NULL,
[EXCHID] [varchar](50) NULL,
[OBJECT] [varchar](50) NULL,
[ALMTXT] [varchar](500) NULL
) ON [PRIMARY]
end'
exec(@SQL)
declare @dt varchar(20)
set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4)
set @s= ' use ' + 'Radar'+ @dt +' create PROCEDURE [GetRecordFromPage_single]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where)
as --第12行declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = ''<(select min''
set @strOrder = '' order by [''+ @fldName + ''] desc''
end
else
begin
set @strTmp = ''>(select max''
set @strOrder = '' order by ['' + @fldName +''] asc''
endset @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [
''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)''
+ @strOrderif @strWhere != ''''
set @strSQL = ''select top '' + str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' [''
+ @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')''
+ @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''''
if @strWhere != ''''
set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + '']'' + @strTmp + '' '' + @strOrder
endif @IsReCount != 0
set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)'
exec(@s) 为什么这个代码运行出来出现这个错误:消息 111,级别 15,状态 1,过程 GetRecordFromPage_single,第 12 行
'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
declare @dt varchar(20)
set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4)
set @s= ' use ' + 'Radar'+ @dt +'go create PROCEDURE [GetRecordFromPage_single]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where)
as --第12行declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = ''<(select min''
set @strOrder = '' order by [''+ @fldName + ''] desc''
end
else
begin
set @strTmp = ''>(select max''
set @strOrder = '' order by ['' + @fldName +''] asc''
endset @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [
''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)''
+ @strOrderif @strWhere != ''''
set @strSQL = ''select top '' + str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' [''
+ @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')''
+ @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''''
if @strWhere != ''''
set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + '']'' + @strTmp + '' '' + @strOrder
endif @IsReCount != 0
set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)'
exec(@s)
declare @dt varchar(20)
set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4)
set @s= ' use ' + 'Radar'+ @dt +'go create PROCEDURE [GetRecordFromPage_single]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where)
as --第12行declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = ''<(select min''
set @strOrder = '' order by [''+ @fldName + ''] desc''
end
else
begin
set @strTmp = ''>(select max''
set @strOrder = '' order by ['' + @fldName +''] asc''
endset @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [
''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)''
+ @strOrderif @strWhere != ''''
set @strSQL = ''select top '' + str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' [''
+ @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')''
+ @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''''
if @strWhere != ''''
set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + '']'' + @strTmp + '' '' + @strOrder
endif @IsReCount != 0
set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)'
exec(@s)
消息 102,级别 15,状态 1,第 1 行
'go' 附近有语法错误。
消息 111,级别 15,状态 1,第 12 行
'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
declare @dt varchar(20)
set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4)
set @s= ' use ' + 'Radar'+ @dt +' go create PROCEDURE [GetRecordFromPage_single]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where)
as --第12行declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = ''<(select min''
set @strOrder = '' order by [''+ @fldName + ''] desc''
end
else
begin
set @strTmp = ''>(select max''
set @strOrder = '' order by ['' + @fldName +''] asc''
endset @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [
''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)''
+ @strOrderif @strWhere != ''''
set @strSQL = ''select top '' + str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' [''
+ @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')''
+ @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''''
if @strWhere != ''''
set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + '']'' + @strTmp + '' '' + @strOrder
endif @IsReCount != 0
set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)'
exec(@s)
'go' 附近有语法错误。
消息 111,级别 15,状态 1,第 12 行
'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where)
as --第12行declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = ''<(select min''
set @strOrder = '' order by [''+ @fldName + ''] desc''
end
else
begin
set @strTmp = ''>(select max''
set @strOrder = '' order by ['' + @fldName +''] asc''
endset @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [
''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)''
+ @strOrderif @strWhere != ''''
set @strSQL = ''select top '' + str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' [''
+ @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')''
+ @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''''
if @strWhere != ''''
set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + '']'' + @strTmp + '' '' + @strOrder
endif @IsReCount != 0
set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)'
exec(@s)这样就可以运行,不过呢,就是存储过程建在master数据库里了诶,我要的是自动创建到动态的数据库里
declare @dt varchar(20)
set @dt=left(CONVERT(varchar(100), GETDATE(), 12),4)
set @ss=' use ' + 'Radar'+ @dt
exec(@ss)
go
declare @s varchar(8000)
set @s= ' create PROCEDURE GetRecordFromPagesingle
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '''' -- 查询条件 (注意: 不要加 where)
as --第12行declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型if @OrderType != 0
begin
set @strTmp = ''<(select min''
set @strOrder = '' order by [''+ @fldName + ''] desc''
end
else
begin
set @strTmp = ''>(select max''
set @strOrder = '' order by ['' + @fldName +''] asc''
endset @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) + '' [
''+ @fldName + ''] from ['' + @tblName + '']'' + @strOrder + '') as tblTmp)''
+ @strOrderif @strWhere != ''''
set @strSQL = ''select top '' + str(@PageSize) + '' * from [''
+ @tblName + ''] where ['' + @fldName + '']''+ @strTmp + ''([''
+ @fldName + '']) from (select top '' + str((@PageIndex-1)*@PageSize) +'' [''
+ @fldName + ''] from [''+ @tblName + ''] where ('' + @strWhere + '')''
+ @strOrder + '') as tblTmp) and (''+ @strWhere + '') '' + @strOrderif @PageIndex = 1
begin
set @strTmp = ''''
if @strWhere != ''''
set @strTmp = '' where (''+ @strWhere + '')''set @strSQL = ''select top ''+ str(@PageSize) + '' * from [''
+ @tblName + '']'' + @strTmp + '' '' + @strOrder
endif @IsReCount != 0
set @strSQL = ''select count(*) as Total from [''+ @tblName + '']''+'' where ''+ @strWhereexec (@strSQL)'
exec(@s)
去掉go也不行,sql 语句里有换行符么?
use xxx
go -----go要占一行 不然运行都有错误
create proc dhakjd象 use xxx
go create proc dhajd
这样就不行诶,问下sql有没有换行符号
set @sql=left(CONVERT(varchar(100),GETDATE(), 12),4)
set @sql='create database '+'Use'+@sql
exec(@sql)