sql stored procedure

Results 1 to 3 of 3

Thread: sql stored procedure

  1. #1
    Ken Stanley Guest

    Default sql stored procedure

    I am new to creating stored procedures and I am not familiar with alot of the syntax. Here is my problem:<BR>I am trying to create a stored procedure within SQL 7 that will allow me to change the table structure of a table by adding a new field.<BR>I am passing the field name into the procedure and assigning it to a variable. Then I am attempting to apply that variable to the ALTER TABLE ddl. When I check the syntax, it tells me that it has a problem with the syntax at the variable.<BR>This is line I am using:<BR><BR>ALTER TABLE myTable2 ADD @MyID varchar(100)<BR><BR>If anyone can clue my in as to why this syntax is wrong or give me an alternative method of adding a new field to a table, I would greatly appreciate it.<BR><BR>thank you,<BR>Ken Stanley<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Why?

    You can not use variables in a DDL statement. You need to build a string that holds the statement and execute that string.<BR><BR>declare @stm varchar(200)<BR>@stm = &#039;alter table myTable2 add&#039; + @myID + &#039; varchar(100) &#039;<BR>execute (@stm)<BR><BR>Although this is possible, I find all programs, that wish to change the datamodel programatically, highly suspect.

  3. #3
    Ken Stanley Guest

    Default RE: Why?

    thanks Lars,<BR>that is exactly what I was looking for. I didn&#039;t know that I could concatenate like that in a stored procedure.<BR>The reason why I am doing it is because our database structure is already built. Now we have come across a situation where if one of the tables which help define the structure has a new record entered, a new field needs to be created in another table.<BR>I do not want to have to manually keep track of that if the situation occurs in the future, I would rather automate the process.<BR><BR>thanks,<BR>Ken<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts