ALTER TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ALTER TABLE

Author

Topic

alee
Starting Member

4 Posts

Posted - 2003-06-27 : 18:23:12

OK Newbie here!!!! i'm trying to create a stored procedure with altering a table. Which I believe I got right. My issue is how to call it passing a parameter using asp. below is my stored procedure and asp code. Any help would be great.

''''''stored Procedure below'''''''''
USE JPD
go

CREATE PROCEDURE addfilterColumn
@Parm varchar(100)
AS

ALTER TABLE parm ADD filter INT NOT NULL DEFAULT -1
GO
''''''''''end'''''''''''''''''''''''''

''''''''asp code''''''''''''''''''''''

dim cmd
set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection = Con

cmd.CommandText = "addfiltercolumn"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh

cmd.Parameters.Append
cmd.CreateParameter("parm", 129, 1, 30)
cmd.Parameters("parm") = session("cust")

cmd.execute
'''''''''''end'''''''''''''''

alee
Starting Member

4 Posts

Posted - 2003-06-27 : 18:29:24

i for got to add my error

Microsoft VBScript compilation error '800a0414'

Cannot use parentheses when calling a Sub

/claims/Claims.asp, line 111

cmd.CreateParameter("parm", 129, 1, 30)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-27 : 18:35:32

Why do you want to call the SP with a parameter when you don't use the parameter in the SP.

try

declare @sql varchar(1000)
select @sql = 'alter table ' + @Parm + ' add filter int not null default 1'
exec (@sql)

Want to discuss why you are creating an SP to do this?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 06/27/2003 18:35:55

alee
Starting Member

4 Posts

Posted - 2003-06-27 : 18:44:14

ok if i'm not using a parameter in the sp... Then i didnn't quite understand what i was reading. I'm trying the sp cuz it seems to handle much of what i'm doing better than asp... plus i get to learn new stuff... I'm gonig to try what you suggested now... thanks for the help.

alee
Starting Member

4 Posts

Posted - 2003-06-27 : 18:59:01

ok so far the sp is working great! Thanks, now i'm having issues i guess with the code part... not sure if your expertise is in that area.. My code is the same and getting the same error

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-27 : 19:07:58

For calling the SP from asp I have an example of an inc file which will abstract the database layer a bit. Helps keep control of how people are accessing the database.

see
http://www.nigelrivett.net/DBAccess.inc.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources