Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Trying to add a column via a variable.

3 posts in Windows NT Last posting was on 2000-07-25 08:13:05.0Z
Cameron Price Posted on 2000-07-25 01:57:18.0Z
Reply-To: "Cameron Price" <cprice@cortexsystems.com.au>
From: "Cameron Price" <cprice@cortexsystems.com.au>
Subject: Trying to add a column via a variable.
Date: Tue, 25 Jul 2000 11:57:18 +1000
Lines: 15
Organization: Cortex Systems
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <ZBuzDxd9$GA.259@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: CPE-24-192-90-54.qld.bigpond.net.au 24.192.90.54
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1829
Article PK: 1089297

Hi, Im trying to add a column to a table via a stored proc, where the column
name is passed in as a parameter.
However i cant seem to get the "value" inside the parameter to work, instead
it just inserts a column name "@parametername".

Is there anyway to get around this? I have also constructed a char local
variable that has the correct syntax (with the column name i want in it) but
i dont know how to exec it so that it uses the string in the variable.


Any help would be great.
Cheers
Cameron Price


Cameron Price Posted on 2000-07-25 02:22:04.0Z
Reply-To: "Cameron Price" <cprice@cortexsystems.com.au>
From: "Cameron Price" <cprice@cortexsystems.com.au>
References: <ZBuzDxd9$GA.259@forums.sybase.com>
Subject: Re: Trying to add a column via a variable.
Date: Tue, 25 Jul 2000 12:22:04 +1000
Lines: 42
Organization: Cortex Systems
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <aQ#$MDe9$GA.85@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: CPE-24-192-90-54.qld.bigpond.net.au 24.192.90.54
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1828
Article PK: 1089296

Heres some more info on what i have done.
When i run this, it does nothing... :(


ALTER procedure DBA.SP_INS_RPTCONFIGIDbackup(@ConfigID integer,@PropName
char(40))
as
declare @datatype integer
declare @szSQL varchar(255)
select @szsql="alter table RPTEMPLOYEETRANS add "+@propname
select @datatype=(select datatypeid from sysconfig where configid =
@configid)
if @datatype = 1 select @szsql=@szsql+' char(40) null'
if @datatype = 2 select @szsql=@szsql+' integer null'
if @datatype = 3 select @szsql=@szsql+' double null'
if @datatype = 4 select @szsql=@szsql+' datetime null'
execute(@szsql)

Cameron Price <cprice@cortexsystems.com.au> wrote in message
news:ZBuzDxd9$GA.259@forums.sybase.com...
> Hi, Im trying to add a column to a table via a stored proc, where the
column
> name is passed in as a parameter.
> However i cant seem to get the "value" inside the parameter to work,
instead
> it just inserts a column name "@parametername".
>
> Is there anyway to get around this? I have also constructed a char local
> variable that has the correct syntax (with the column name i want in it)
but
> i dont know how to exec it so that it uses the string in the variable.
>
>
> Any help would be great.
> Cheers
> Cameron Price
>
>


Andy Price Posted on 2000-07-25 08:13:05.0Z
From: "Andy Price" <andy.price@canada.com>
References: <ZBuzDxd9$GA.259@forums.sybase.com> <aQ#$MDe9$GA.85@forums.sybase.com>
Subject: Re: Trying to add a column via a variable.
Date: Tue, 25 Jul 2000 09:13:05 +0100
Lines: 100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4133.2400
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400
Message-ID: <fwSdNFh9$GA.85@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: dyn84-ras15.screaming.net 212.49.238.84
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1823
Article PK: 1089298

Cameron,

Here is what I ran and it works. There is no such thing as 'alter
procedure', so you have to drop and recreate it. I added a return to the end
of the proc as well, but other than that it is the same as what you did and
I got it to add an additional column to you table.

Andy

--

Andy Price
Sybase Certified Professional - Adaptive Server DBA v12.0
Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5



drop procedure SP_INS_RPTCONFIGIDbackup

go

create procedure SP_INS_RPTCONFIGIDbackup(@configid integer,@propname
char(40))

as

declare @datatype integer, @szsql varchar(255)

select @szsql="alter table RPTEMPLOYEETRANS add "+@propname

select @datatype=(select datatypeid from sysconfig where configid =

@configid)

if @datatype = 1 select @szsql=@szsql+' char(40) null'

if @datatype = 2 select @szsql=@szsql+' integer null'

if @datatype = 3 select @szsql=@szsql+' double null'

if @datatype = 4 select @szsql=@szsql+' datetime null'

execute (@szsql)

return

--------------------------------------------------

SP_INS_RPTCONFIGIDbackup 1, "testcol2"

sp_help RPTEMPLOYEETRANS

"Cameron Price" <cprice@cortexsystems.com.au> wrote in message
news:aQ#$MDe9$GA.85@forums.sybase.com...
> Heres some more info on what i have done.
> When i run this, it does nothing... :(
>
>
> ALTER procedure DBA.SP_INS_RPTCONFIGIDbackup(@ConfigID integer,@PropName
> char(40))
> as
> declare @datatype integer
> declare @szSQL varchar(255)
> select @szsql="alter table RPTEMPLOYEETRANS add "+@propname
> select @datatype=(select datatypeid from sysconfig where configid =
> @configid)
> if @datatype = 1 select @szsql=@szsql+' char(40) null'
> if @datatype = 2 select @szsql=@szsql+' integer null'
> if @datatype = 3 select @szsql=@szsql+' double null'
> if @datatype = 4 select @szsql=@szsql+' datetime null'
> execute(@szsql)
>
>
>
> Cameron Price <cprice@cortexsystems.com.au> wrote in message
> news:ZBuzDxd9$GA.259@forums.sybase.com...
> > Hi, Im trying to add a column to a table via a stored proc, where the
> column
> > name is passed in as a parameter.
> > However i cant seem to get the "value" inside the parameter to work,
> instead
> > it just inserts a column name "@parametername".
> >
> > Is there anyway to get around this? I have also constructed a char local
> > variable that has the correct syntax (with the column name i want in it)
> but
> > i dont know how to exec it so that it uses the string in the variable.
> >
> >
> > Any help would be great.
> > Cheers
> > Cameron Price
> >
> >
>
>