Hi,
I'm trying to create some tables dynamically based on the content of another table in the same database. I found apost that does what I want to do, but I can't get my code (that is similar to the post) to work.
Given below is my code:
1DECLARE @.deptCodevarchar(50), @.numberOfDeptint, @.tableNamevarchar(MAX), @.columnNamevarchar(MAX)2DECLARE @.lengthDeptCodeint, @.lengthTableNameint, @.lengthColumnNameint34SELECT @.numberOfDept =COUNT(DISTINCT DeptCode)5FROM tbl_Department;67WHILE (@.numberOfDept >=0)8BEGIN9SELECT @.deptCode = DeptCode, @.lengthDeptCode =LEN(DeptCode)10FROM tbl_Department;1112SET @.tableName ='tbl_ProjectNumber'+@.deptCode13SET @.lengthTableName =LEN(@.tableName)14SET @.columnName ='ProjectNumber'+@.deptCode15SET @.lengthColumnName =LEN(@.columnName)1617CREATE TABLECAST(@.tableNameas char(@.lengthTableName))18(19CAST(@.columnNameas char(@.lengthColumnName))int IDENTITY(1,1)NOT NULL20)2122SET @.numberOfDept = @.numberOfDept - 123END
This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly.
The error I get is:
Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '@.tableName'.
Thanks.
Well, I figured it out myself. For anyone interested, this is how I did it:
DECLARE @.deptCodevarchar(50)DECLARE @.SqlStmtvarchar(300)DECLARE MyCursorCURSOR FORSELECT DeptCodeFROM tbl_DepartmentOPEN MyCursorFETCH NEXT FROM MyCursorINTO @.deptCodeWHILE@.@.FETCH_STATUS = 0BEGINSELECT @.SqlStmt ='CREATE TABLE tbl_ProjectNumber' +REPLACE(CONVERT(char,@.deptCode),'.','') +'(ProjectNumber'+REPLACE(CONVERT(char,@.deptCode),'.','')+' int PRIMARY KEY IDENTITY(1,1) NOT NULL)'EXECUTE(@.SqlStmt)FETCH NEXT FROM MyCursorINTO @.deptCodeENDCLOSE MyCursorDEALLOCATE MyCursor
