Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
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
')
 
but @query is NULL .why ?
 
pleas help me .
Posted 28-Sep-12 23:55pm
Edited 29-Sep-12 0:20am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Because you didn't initialise @Query.
 
Your
    DECLARE @Query VARCHAR(MAX) 
should be
   DECLARE @Query VARCHAR(MAX) = ''
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
')
  Permalink  
v2
Comments
Maciej Los at 29-Sep-12 10:27am
   
I'm not sure that is a correct answer...
1. Declaration of @Query exists and the value of @Query variable was initialized (see: SET @Query = ...)!
2. Depends on SQL Server version, the declaration of variable and its assign is NOT possible in one line (ex. MS SQL Server 2005).
Chris Maunder at 29-Sep-12 10:40am
   
1. the original was
 
DECLARE @Query VARCHAR(MAX)
SET @Query = @Query + ...
 
So @Query has not been initialised.
 
2. Yeah, I was being lazy. Break out DECLARE @Query VARCHAR(MAX) = '' into
 
DECLARE @Query VARCHAR(MAX)
SET @Query = ''
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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  
 
Regards,
Ahmed Mandour
  Permalink  
v2
Comments
Maciej Los at 29-Sep-12 10:29am
   
It might works! +5!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 369
1 Maciej Los 208
2 George Jonsson 144
3 Richard MacCutchan 140
4 Dave Kreskowiak 130


Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 29 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid