August 28, 2008 at 1:18 am
I am having a table with fields like this:
iTableFieldIdint
iMenuIdint
vFieldsNamevarchar100
iDataTypeNamevarchar50
iFieldLengthint
bIsPrimarybit
bIsIdentitybit
bIsNull bit
vDefaultvarchar50
bArchivebit
And values like this:
iTableFieldId iMenuId vFieldName iDataTypeName iFieldLength 1 109 iPhotoGalIdint NULL 2 109 vPhotoGalTitlevarchar 50
3 109 dModifiedDatedatetime null
4 109 iOrder int null
IsPrimary IsIdentity IsNull vDefault bArchive
11 0 NULL 1
00 1 NULL 1
00 1 getdate() 1
0 0 1 NULL 1
Now i want to create a table depending on the menu id, that is i should get the o/p like this
CREATE TABLE @TableName (iPhotoGalId INT PRIMARY KEY IDENTITY(1,1) NOT NULL,vPhotoGalTitle VARCHAR(50),dModifiedDate DATETIME DEFAULT GETDATE(),iOrder INT)
So i have written the query like this,
DECLARE @vFieldsName VARCHAR(100),@vFieldsDataType VARCHAR(50),@vDefault VARCHAR(50),@Str1 VARCHAR(3000)
DECLARE @iFieldLength INT
DECLARE @bIsPrimary VARCHAR(20),@bIsIdentity VARCHAR(25),@bIsNull VARCHAR(15)
SET @Str1=NULL
DECLARE CreateTable CURSOR FOR
SELECT vFieldsName,DataTypeDesc,iFieldLength ,bIsPrimary=
CASE
WHEN bIsPrimary=1 THEN 'Primary KEY'
ELSE ''
END
,bIsIdentity=
CASE
WHEN bIsIdentity=1 THEN 'IDENTITY(1,1)'
ELSE ''
END
,bIsNull=
CASE
WHEN bIsNull=0 THEN 'NOT NULL'
ELSE ''
END
,ISNULL(vDefault,'') AS vDefault FROM TableField TF JOIN
DataTypeValue DTV ON DTV.iDataTypeId=TF.iDataTypeId
OPEN CreateTable
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM CreateTable
INTO @vFieldsName,@vFieldsDataType,@iFieldLength,@bIsPrimary,@bIsIdentity,@bIsNull,@vDefault
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
BEGIN
IF @Str1 IS NULL
SET @Str1=@vFieldsName+' '+@vFieldsDataType+' '+CONVERT(VARCHAR(25),@bIsPrimary)+' '+CONVERT(VARCHAR(25),@bIsIdentity)+' '+CONVERT(VARCHAR(25),@bIsNull)+' '+@vDefault
ELSE
SET @Str1=@Str1+','+@vFieldsName+' '+@vFieldsDataType+'('+CONVERT(VARCHAR(10),@iFieldLength)+')'+ CONVERT(VARCHAR(25),@bIsPrimary)+''+CONVERT(VARCHAR(25),@bIsIdentity)+''+CONVERT(VARCHAR(25),@bIsNull)+''+@vDefault
END
--print @Str1
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM CreateTable
INTO @vFieldsName,@vFieldsDataType,@iFieldLength,@bIsPrimary,@bIsIdentity,@bIsNull,@vDefault
END
print @Str1
CLOSE CreateTable
DEALLOCATE CreateTable
GO
But the output is not as desired:
iPhotoGalId INT Primary KEY IDENTITY(1,1) NOT NULL ,vPhotoGalTitle VARCHAR(50),dModifiedDate DATETIME(8),iOrder INT(0))
I NEED SOME HELP ON THIS
Thanks in Advance,
Syed Sanaullah Khadri
DBA
August 28, 2008 at 1:51 am
If I'm understanding you corectly, you want the following script generated:
CREATE TABLE TableName (iPhotoGalId INT PRIMARY KEY IDENTITY(1,1) NOT NULL,vPhotoGalTitle VARCHAR(50),dModifiedDate DATETIME DEFAULT GETDATE(),iOrder INT)
but you're getting this:
iPhotoGalId INT Primary KEY IDENTITY(1,1) NOT NULL ,vPhotoGalTitle VARCHAR(50),dModifiedDate DATETIME(8),iOrder INT(0))
Is that correct?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2008 at 4:23 am
Ok but while concatenate we need to add this CREATE TABLE TableName with that, but my main problem is
iPhotoGalId INT Primary KEY IDENTITY(1,1) NOT NULL ,vPhotoGalTitle VARCHAR(50),dModifiedDate DATETIME(8),iOrder INT(0)
How can we remove dModifiedDate DATETIME(8) (open brace,8 and close brace from here).This should come only when the selected datatype is varchar or char.
August 28, 2008 at 5:46 am
There is no need for a cursor, try something like:
-- *** Test Data ***
DECLARE @t TABLE
(
    iTableFieldId int NOT NULL
    ,iMenuId int NULL
    ,vFieldsName varchar(100) NULL
    ,iDataTypeName varchar(50) NULL
    ,iFieldLength int NULL
    ,bIsPrimary bit NULL
    ,bIsIdentity bit NULL
    ,bIsNull bit NULL
    ,vDefault varchar(50) NULL
    ,bArchive bit NULL
)
INSERT INTO @t
SELECT 1, 109, 'iPhotoGalId', 'int', NULL, 1, 1, 0, NULL, 1 UNION ALL
SELECT 2, 109, 'vPhotoGalTitle', 'varchar', 50, 0, 0, 1, NULL, 1 UNION ALL
SELECT 3, 109, 'dModifiedDate', 'datetime', NULL, 0, 0, 1, 'GETDATE()', 1 UNION ALL
SELECT 4, 109, 'iOrder', 'int', NULL, 0, 0, 1, NULL, 1
-- *** End Test Data ***
DECLARE @SQLString varchar(8000)
SET @SQLString = ''
SELECT
    @SQLString = @SQLString
    + ',' + vFieldsName + ' ' + iDataTypeName
    + CASE
        WHEN iFieldLength IS NOT NULL
        THEN '(' + CAST(iFieldLength AS varchar(20)) + ')'
        ELSE ''
    END
    + CASE
        WHEN ISNULL(bIsIdentity, 0) = 1
        THEN ' IDENTITY'
        ELSE ''
    END
    + ' '
    + CASE
        WHEN ISNULL(bIsNull, 0) = 1
        THEN 'NULL'
        ELSE 'NOT NULL'
    END
    + CASE
        WHEN ISNULL(bIsPrimary, 0) = 1
        THEN ' PRIMARY KEY'
        ELSE ''
    END
    + CASE
        WHEN vDefault IS NOT NULL
        THEN ' DEFAULT(' + vDefault + ')'
        ELSE ''
    END
FROM @t
WHERE iMenuId = 109
ORDER BY iTableFieldId
SET @SQLString =
    'CREATE TABLE YourTable('
    + SUBSTRING(@SQLString, 2, 8000)
    + ')'
PRINT @SQLString
August 28, 2008 at 5:51 am
Sanaullah (8/28/2008)
Ok but while concatenate we need to add this CREATE TABLE TableName with that, but my main problem isiPhotoGalId INT Primary KEY IDENTITY(1,1) NOT NULL ,vPhotoGalTitle VARCHAR(50),dModifiedDate DATETIME(8),iOrder INT(0)
How can we remove dModifiedDate DATETIME(8) (open brace,8 and close brace from here).This should come only when the selected datatype is varchar or char.
You ned to modify this line:
SET @Str1=@Str1+','+@vFieldsName+' '+@vFieldsDataType+'('+CONVERT(VARCHAR(10),@iFieldLength)+')'+
to only put the brackets and field length in if @vFieldsDataType is char, nchar, varchar or nvarchar. A Case statement should do the trick.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2008 at 4:40 am
Hi All,
I have modified it and code is as follows, if we can minimize the code then please reply.
ALTER PROCEDURE [DBO].[CreateTableByMenuId]
(
@iMenuId [INT]
)
AS
BEGIN
/*
WRITTEN BY SYED SANAULLAH KHADRI
DATE 29-Aug-2008
EXEC CreateTableByMenuId 58
SELECT * FROM MainMenu
SELECT * FROM TableField
*/
SET NOCOUNT ON
BEGIN TRAN
DECLARE @vFieldsName VARCHAR(100),@vFieldsDataType VARCHAR(50),@vDefault VARCHAR(50),@Str1 VARCHAR(3000)
DECLARE @iFieldLength INT,@Count1 INT
DECLARE @bIsPrimary VARCHAR(20),@bIsIdentity VARCHAR(25),@bIsNull VARCHAR(15)
SET @Str1=NULL
SET @Count1=NULL
DECLARE @TableName VARCHAR(50)
SET @TableName=NULL
SELECT @TableName=vRefTable FROM MainMenu WHERE iMenuId=@iMenuId
SELECT @Count1=Count(Name) FROM SYSOBJECTS WHERE Name=@TableName AND XType='U'
IF @Count1>0
BEGIN
SELECT 'Table Name Already Exist' AS [Please Check This Error]
SET @Count1=NULL
END
ELSE
BEGIN
SELECT @Count1=COUNT(bIsPrimary) FROM TableField WHERE bIsPrimary=1 AND iMenuId=@iMenuId
IF @Count1>1 OR @Count1=0
BEGIN
SELECT 'Select only or one primary key in the table' AS [Please Check This Error]
END
ELSE
BEGIN
SELECT @Count1=COUNT(bIsIdentity) FROM TableField WHERE bIsIdentity=1 AND iMenuId=@iMenuId AND iDataTypeId NOT IN (2,3,4,5,7) AND bIsPrimary=0
IF @Count1>1
BEGIN
SELECT 'Select only or one Identity key With Primary Key and datatype not like VARCHAR,CHAR,DATTIME,TEXT' AS [Please Check This Error]
END
ELSE
BEGIN
SET @Count1=NULL
SELECT @Count1=COUNT([bIsNull]) FROM TableField WHERE bIsPrimary=1 AND bIsNull=1 AND iMenuId=@iMenuId
IF @Count1>0
BEGIN
SELECT 'Primary Key Cannot have Null values' AS [Please Check This Error]
END
ELSE
BEGIN
DECLARE CreateTable CURSOR FOR
SELECT vFieldsName,DataTypeDesc,iFieldLength,bIsPrimary=
CASE
WHEN bIsPrimary=1 THEN 'Primary KEY'
ELSE ''
END
,bIsIdentity=
CASE
WHEN bIsIdentity=1 THEN 'IDENTITY(1,1)'
ELSE ''
END
,bIsNull=
CASE
WHEN bIsNull=0 THEN 'NOT NULL'
ELSE ''
END
,ISNULL(vDefault,'') AS vDefault FROM TableField TF JOIN
DataTypeValue DTV ON DTV.iDataTypeId=TF.iDataTypeId
WHERE TF.iMenuId=@iMenuId
OPEN CreateTable
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM CreateTable
INTO @vFieldsName,@vFieldsDataType,@iFieldLength,@bIsPrimary,@bIsIdentity,@bIsNull,@vDefault
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
BEGIN
IF @Str1 IS NULL
SET @Str1=@vFieldsName+' '+@vFieldsDataType+' '+CONVERT(VARCHAR(25),@bIsPrimary)+' '+CONVERT(VARCHAR(25),@bIsIdentity)+' '+CONVERT(VARCHAR(25),@bIsNull)+' '+@vDefault
ELSE
IF @vFieldsDataType='VARCHAR' OR @vFieldsDataType='TEXT'
BEGIN
SET @Str1=@Str1+','+@vFieldsName+' '+@vFieldsDataType+'('+CONVERT(VARCHAR(10),@iFieldLength)+')'+ CONVERT(VARCHAR(25),@bIsPrimary)+''+CONVERT(VARCHAR(25),@bIsIdentity)+''+CONVERT(VARCHAR(25),@bIsNull)+' '+@vDefault
END
ELSE
BEGIN
IF LEN(@vDefault)>0
BEGIN
SET @Str1=@Str1+','+@vFieldsName+' '+@vFieldsDataType+CONVERT(VARCHAR(25),@bIsPrimary)+''+CONVERT(VARCHAR(25),@bIsIdentity)+' '+CONVERT(VARCHAR(25),@bIsNull)+'DEFAULT '+@vDefault
END
ELSE
BEGIN
SET @Str1=@Str1+','+@vFieldsName+' '+@vFieldsDataType+CONVERT(VARCHAR(25),@bIsPrimary)+''+CONVERT(VARCHAR(25),@bIsIdentity)+' '+CONVERT(VARCHAR(25),@bIsNull)+' '+@vDefault
END
END
END
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM CreateTable
INTO @vFieldsName,@vFieldsDataType,@iFieldLength,@bIsPrimary,@bIsIdentity,@bIsNull,@vDefault
END
SET @Str1='CREATE TABLE '+@TableName+ '('+@Str1+')'
-- PRINT @Str1
EXEC sp_sqlexec @Str1
SELECT 'Table Created Successfully' AS [PERFECT OUTPUT]
CLOSE CreateTable
DEALLOCATE CreateTable
END
END
END
END
IF @@ERROR=0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
SET NOCOUNT OFF
END
August 29, 2008 at 8:04 am
This piece is wrong
IF @vFieldsDataType='VARCHAR' OR @vFieldsDataType='TEXT'
BEGIN
SET @Str1=@Str1+','+@vFieldsName+' '+@vFieldsDataType+'('+CONVERT(VARCHAR(10),@iFieldLength)+')'+ CONVERT(VARCHAR(25),@bIsPrimary)+''+CONVERT(VARCHAR(25),@bIsIdentity)+''+CONVERT(VARCHAR(25),@bIsNull)+' '+@vDefault
END
TEXT does not have a length. char, nchar, varchar and nvarchar do. Also numeric and decimal have precision and scale.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply