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
Your Filters
Interested
Ignored
     
0 Guruprasad.K.Basavaraju 460
1 Sergey Alexandrovich Kryukov 336
2 Shai Vashdi 294
3 OriginalGriff 280
4 Abhinav S 160
0 Sergey Alexandrovich Kryukov 9,185
1 OriginalGriff 5,315
2 Peter Leow 4,040
3 Maciej Los 3,535
4 Abhinav S 3,308


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