473,902 Members | 5,842 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

t-sql alter table udf help!

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_listextend edproperty (NULL, 'user', 'dbo', 'table',
@TableName, 'column', @FieldName)
GROUP BY objname))
BEGIN

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

Jul 23 '05 #1
8 9739
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

Jul 23 '05 #2
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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!
Jul 23 '05 #4
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_fieldcreat or'
AND type = 'P')
DROP Procedure sp_fieldcreator
GO

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

AS
BEGIN
DECLARE @sql1 nvarchar(400)
DECLARE @sql2 nvarchar(400)
if (not EXISTS (SELECT count (objname)
FROM ::fn_listextend edproperty (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_addextendedp roperty '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

Jul 23 '05 #5
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!
Jul 23 '05 #6
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 :).

Jul 23 '05 #7
if not exists (select * from information_sch ema.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!
Jul 23 '05 #8
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
15044
by: Laurence Breeze | last post by:
Is it possible to add a column to a table using the "alter table" statement and specify where in the sequence of columns the new column sits. If not is there any way to alter the order of columns using TSQL rather than Enterprise Manager / Design Table. TIA Laurence Breeze
2
21365
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something similar. here is a rough idea of what the stored procedure should do. (I do not know the syntax to accomplish this can anyone help or explain this? Thanks much, CBL
1
7603
by: Matik | last post by:
Hello, I need to change collation in my database (more databases acctualy). Therefore, I wanted to make a script, which will do it at one more time. I already have a cursor, updating collation on all tables (fields) in database. The problem is, before I will to update the collations, I need to drop all constrains and pk's.
10
26160
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not null references another, FK_LASTLYOID bigint not null references lastly, unique (FK_OTHEROID,FK_ANOTHEROID))
0
6246
by: Gianfranco | last post by:
Hi, I'm not familiar at access and at VBA either, so I need some help or at least some hint please. I have table "availabledrivers", coming from a query to the table "drivers". Then I have a table "availablebus" coming from a query to "bus". Bus and Drivers have autonumber ID, so the tables I get have the IDs screwed up..I mean..there are "holes" in them..it's like 1,2,5,7,11,20 etc Now..I need to link the 2 resulting table...and I have...
5
49705
by: Mike L | last post by:
Hello all I'm trying to migrate a BE from Access to SQL Server. I've been making changes to the Access BE from the FE with SQL statements, and want to do the same with the BE in SQL Server. When running the statement "ALTER TABLE tblTest DROP COLUMN TestColumn3" from the FE with a SQL Server BE, it works OK. Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
2
31528
by: RamaKrishna Narla | last post by:
In MS SQL Server, I have the following tables with some data in it. create table table1 ( column1 varchar(32), column2 int not null, column10 varchar(255), ..... primary key (column1, column2), );
4
23845
by: Brian Wotherspoon | last post by:
Hi all, I'm using SQL Server 2000 SP3 to store data for real time transaction processing. I have set up replication to another server using a push subscription to give me immediate backup. I need to alter the data type of one of the columns and am using the following basic sql:
1
3529
by: vasilip | last post by:
I'm testing out db2 for a project I'm starting that requires proper xml support and I can't seem to get both xml and spatial data to work well in the same table. Once having created a table containing both xml and spatial data fields I can't seem to find a way to alter the table I have created a table containing an id, xmldata field and a ST_Point If I try to drop the xml field with ALTER TABLE TEST DROP COLUMN
5
4106
by: Giacomo | last post by:
Hi, I’ve the following error message: --------------- ALTER TABLE . ALTER COLUMN varchar(10) Go Server: messaggio 4929, livello 16, stato 1, riga 1
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10870
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5893
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6085
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4725
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4306
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.