t-sql alter table udf help!

[David Housman]

Hi,

I'm trying to write a function check if a column exists on a table, and
creates it if it doesn't. The line that the query analyzer is citing
is noted. It seems unhappy taking variables in the ALTER TABLE
command. I can think of a hack around this, but I'm hoping there's a
better way? Muchas gracias in advance :)

-DJ
Code follows

CREATE FUNCTION fieldCreator
(@TableName varchar(20),
@FieldName varchar(20))
RETURNS BIT
AS
BEGIN
if (EXISTS (SELECT count (objname)
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
@TableName, 'column', @FieldName)
GROUP BY objname))
BEGIN

ALTER TABLE @TableName ADD @FieldName int DEFAULT 4 --ERRORS HERE!!!
EXEC sp_addextendedproperty 'caption', 'Created by script for
analysis', 'user', dbo, 'table', @TableName, 'column', @FieldName
return 1
END
return 0
END
GO

[PromisedOyster]

Try using the sp_executesql stored procedure

eg

declare @sql as nvarchar(500)
select @sql = 'alter table ' + @TableName + ' ADD ' + @FieldName + '
int DEFAULT 4'
exec sp_executesql @sql

[Erland Sommarskog]

David Housman (dh******@gmail.com) writes:

I'm trying to write a function check if a column exists on a table, and
creates it if it doesn't. The line that the query analyzer is citing
is noted. It seems unhappy taking variables in the ALTER TABLE
command. I can think of a hack around this, but I'm hoping there's a
better way? Muchas gracias in advance :)

To start with, you cannot invoke ALTER TABLE from a function. A UDF
must not change database state. But you use a stored procedure instead.

And, yes, you cannot use variables in the ALTER TABLE statement. You need
to use dynamic SQL for this sort of thing. The sample from PromisedOyster
should be enough to get you going. For a much longer discussion on
dynamic SQL - on when to use it and when to not - there is an article
on my web site, http://www.sommarskog.se/dynamic_sql.html, that you
may find interesting.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Nigel Rivett]

You have a few more problems here

You can't use alter table within a function.
You can't call a stored proc from a function
You can't exec dynamic sql from a function

Nigel Rivett
www.mindsdoor.net
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dave]

Hi,

Thanks for the help- I changed the function to a procedure, and used
sp_executesql to execute the dynamic sql. In return for your help, I
promise to go easy on you when the revolution comes :-D. Fixed script
is below, if anyone cares to disprove the theory that the crap I
produce is useless to anyone other than me :).

-DJ

IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'sp_fieldcreator'
AND type = 'P')
DROP Procedure sp_fieldcreator
GO

CREATE Procedure sp_fieldcreator
(@TableName varchar(20) = 'SelectedGroupsAtCMU',
@FieldName varchar(20))

AS
BEGIN
DECLARE @sql1 nvarchar(400)
DECLARE @sql2 nvarchar(400)
if (not EXISTS (SELECT count (objname)
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
@TableName, 'column', @FieldName)
GROUP BY objname))
BEGIN
select @sql1 = 'alter table ' + @TableName + ' ADD ' + @FieldName +
'
int DEFAULT 4'
exec sp_executesql @sql1
EXEC sp_addextendedproperty 'description', 'Created by script for
analysis', 'user', dbo, 'table', @TableName, 'column', @FieldName

END
select @sql2 = 'UPDATE ' + @TableName + ' SET ' + @FieldName +'= 4
WHERE '
+ @FieldName + ' is null OR ' + @FieldName +' != 1'
exec sp_executesql @sql2
END
GO

[Nigel Rivett]

Well I would question the use of such a procedure.
And even if it was useful the idea of checking the existence of a column
by the extended property is a bit dubious.
And even if that was ok count(objname) in an existence check?
And then a group by clause in an existence check?
Nigel Rivett
www.mindsdoor.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dave]

You're absolutely right- it doesn't make sense to use count and exists.

I don't know another way to check the existence of a column on a table-
perhaps you can suggest another function?
Thanks for the feedback :).

[Nigel Rivett]

if not exists (select * from information_schema.columns where TABLE_NAME
= @tblname and COLUMN_NAME = @colname and TABLE_SCHEMA = @user)
begin

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Dave (dh******@gmail.com) writes:

You're absolutely right- it doesn't make sense to use count and exists.

I don't know another way to check the existence of a column on a table-
perhaps you can suggest another function?

IF col_length('tbl', 'col') IS NULL
-- Column does not exist
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp