April 16, 2008 at 3:49 am
I've been playing with the following bit of code in the query analyser and hit a small snag, the code is supposed to detect whether a table exists, if it doesn't then create it, if not add a new colomn
Declare @TableName VarChar(1000)
Declare @SQL VarChar(4000)
Declare @sTool VarChar(1000)
Declare @sNewCol VarChar(1000)
set @sTool = 'fieldname'
set @sNewCol = 'New Feild'
set @TableName = 'tester'
SET @SQL = IF not EXISTS(select * from information_schema.columns where table_name='tester')
begin
SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
Exec (@SQL)
end
else
begin
SET @SQL = 'ALTER TABLE [' + @TableName +'] ADD [' + @sNewCol +'] varchar (200)'
exec (@SQL)
end
as it stands this works fine, but if I try to make table_name dynamic in the If Not Exists line I hit a snag, can anyone help?
April 16, 2008 at 3:58 am
You shouldn't need to use dynamic SQL for the NOT EXISTS statement. Try this:
IF NOT EXISTS(select * from information_schema.columns where table_name = @TableName)
begin
SELECT @SQL = 'Create Table ' + @TableName + '('
SELECT @SQL = @SQL + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'
Exec (@SQL)
end
else
begin
SET @SQL = 'ALTER TABLE [' + @TableName +'] ADD [' + @sNewCol +'] varchar (200)'
exec (@SQL)
end
Oh, and use varchar(128) for table names and column names.
April 16, 2008 at 4:04 am
Karl, that's obsolutely brilliant, exactly what I needed many thanks !
April 16, 2008 at 4:06 am
And beware of the following:
(1) The way you have designed this, you need to give the user permission to create tables.
(2) You don't have a check for whether the column name already exists, so if you run your code multiple times, it will eventually fail.
John
April 16, 2008 at 4:10 am
I understand John, thanks for the info
April 16, 2008 at 4:17 am
Another thing to look out for is that INFORMATION_SCHEMA.COLUMNS also lists columns in views. Therefore, if @TableName happens to be the name of a view, it'll also fail because ALTER TABLE won't work on a view.
John
April 17, 2008 at 6:58 am
I know it's a bit more work but to execute dynamic SQL but...
"SQL gives you the option of using exec() or sp_executesql. Of the two, sp_executesql is the better choice, it will try to re-use previous plans that match the query exactly. You can boost this some by using parameters in your sql, something I rarely see done. SP_executesql also has the advantage of supporting output parameters so you can return info from the dynamic session back into the calling stored procedure. ADO automatically uses sp_executesql when you do a connection.execute."
from
Dynamic SQL or Stored Procedure
By Andy Warren, 2003/04/23
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply