Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,637 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi!
I am trying to create a series of 'Alter table, Alter column' statements dynamically.
The problem is it automatically creates the column as Nullable, even if it should be not Null.
Im trying to put a CASE statment inside the dynamic SQL, but im doing something wrong.
SELECT sql='alter table ['+d.name+'] alter column ['+a.name+'] char(' + CONVERT(VARCHAR(20), a.prec) + ')'
+ CASE(a.isnullable) WHEN 0 THEN ' Not NULL' WHEN 1 THEN ' NULL'
,d.name AS Tablename, b.name AS datatype
FROM syscolumns a
INNER JOIN systypes b
ON a.xtype=b.xusertype
INNER JOIN sysobjects d
ON a.id=d.id
WHERE d.xtype='U' AND d.name<>'dtproperties'
AND b.name IN ('varchar', 'nvarchar')
Any idea how I should be doing this?
Thanks,
Zoe
Hi @zoe Ohara ,
Please try the following.
Notable points:
IIF()
instead of CASE
statement. QUOTENAME()
function. SQL
SELECT sql='alter table ' + QUOTENAME(d.name) + ' alter column ' + QUOTENAME(a.name) + ' char(' + CONVERT(VARCHAR(20), a.prec) + ')'
+ IIF(a.isnullable=0, ' NOT NULL', ' NULL')
,d.name AS Tablename, b.name AS datatype
FROM syscolumns a
INNER JOIN systypes b
ON a.xtype=b.xusertype
INNER JOIN sysobjects d
ON a.id=d.id
WHERE d.xtype='U' AND d.name<>'dtproperties'
AND b.name IN ('varchar', 'nvarchar') ;
For the CASE itself try something like this:
CASE(a.isnullable) WHEN 1 THEN 'NULL' ELSE '' END
Thanks!
That did the trick!
Zoe x