sql server 2008中EXECUTE语句中的错误 [英] error in EXECUTE statment in sql server 2008

问题描述

Hy。我希望你很开心



我在sql server 2008中写了这段代码

Hy . I hope you are happy

I wrote yhis code in sql server 2008

ALTER PROC [dbo].[CreateDatabase]
(
	@DatabaseName NVARCHAR(MAX),
	@TableName NVARCHAR(MAX) = 'LogAlarm',
	@path NVARCHAR(MAX) = 'D:\BackupDatabase\'
)
AS
EXECUTE(N'
IF NOT EXISTS
(
SELECT * FROM sys.databases WHERE [name] = ''' + @DatabaseName + '''
 )
BEGIN
DECLARE @Query VARCHAR(MAX)
SET @Query = @Query + ''CREATE DATABASE '+@DatabaseName +' ON  PRIMARY ''
SET @Query = @Query + ''( NAME = '''''+@DatabaseName +''''', FILENAME = '''''+@path+@DatabaseName +'.mdf'''' )''
SELECT(@Query)
END
')

但是@query是NULL。为什么？



请求帮助我。

but @query is NULL .why ?

pleas help me .

推荐答案

因为你没有初始化@Query。



你的

Because you didn't initialise @Query.

Your

DECLARE @Query VARCHAR(MAX)

应该是

should be

DECLARE @Query VARCHAR(MAX) = ''

so ：

so:

ALTER PROC [dbo].[CreateDatabase]
(
	@DatabaseName NVARCHAR(MAX),
	@TableName NVARCHAR(MAX) = 'LogAlarm',
	@path NVARCHAR(MAX) = 'D:\BackupDatabase\'
)
AS
EXECUTE(N'
IF NOT EXISTS
(
SELECT * FROM sys.databases WHERE [name] = ''' + @DatabaseName + '''
 )
BEGIN
   DECLARE @Query VARCHAR(MAX) = '' -- <-- ADD THIS BIT
   SET @Query = @Query + ''CREATE DATABASE '+@DatabaseName +' ON  PRIMARY ''
   SET @Query = @Query + ''( NAME = '''''+@DatabaseName +''''', FILENAME = '''''+@path+@DatabaseName +'.mdf'''' )''
   SELECT(@Query)
END
')

您好，



请用此更改您的程序

Hi,

Please Alter your Procedure with this

ALTER PROC [dbo].[CreateDatabase]
(
	@DatabaseName NVARCHAR(MAX),
	@TableName NVARCHAR(MAX) = 'LogAlarm',
	@path NVARCHAR(MAX) = 'D:\BackupDatabase\'
)
AS
BEGIN 

	DECLARE @Query NVARCHAR(MAX),
	@DBName Nvarchar(100),
	@LogName NVARCHAR(100),
	@LogFile NVARCHAR(100)
	
	
	SET @DBName = @DatabaseName+'.mdf'
	SET @LogName = @DatabaseName+'_log'
	SET @LogFile = @DatabaseName+'_log.ldf'
	
SET @Query = 
'
IF NOT EXISTS
(
SELECT * FROM sys.databases WHERE [name] = ''' + @DatabaseName + '''
 )
 
BEGIN

CREATE DATABASE [' + @DatabaseName +'] ON  PRIMARY ( NAME = N'''+ @DatabaseName + ''', FILENAME = N'''+@path+@DBName+''', SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'''+ @LogName +''', FILENAME = N'''+@path+@LogFile+''' , SIZE = 1024KB , FILEGROWTH = 10%)

END'

EXEC (@Query);

END  

问候，

Ahmed Mandour

Regards,
Ahmed Mandour

这篇关于sql server 2008中EXECUTE语句中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！