none
Creating stored procedure to create table with parameter name...

    Question

  • I'm trying to create a stored procedure to create a table with a parameter being passed for its table name.

    Here's what I have so far:
    Code Snippet

    USE [Courses]
    GO
    /****** Object:  StoredProcedure [dbo].[Procedure_TestCreateTable]    Script Date: 12/10/2008 12:05:00 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:    <Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[Procedure_TestCreateTable]
        -- Add the parameters for the stored procedure here
        @tblName text
    AS
    BEGIN
    EXEC('CREATE TABLE [dbo].[' + @tblName  + ']')

    END


    When I execute from sql server 2008 and type in test for my parameter it gives me an error cannot find stored procedure.  Any ideas as to why?

    I know there's no column names and what not, wanted to just create a test table to make sure the procedure is working right before doing anything else.  Or do I need to specify at least one column too?
    Wednesday, December 10, 2008 6:23 PM

All replies

  • Hello,

     You cant create table without any columns in it.

     

     

    Thanks,

    NP

    Wednesday, December 10, 2008 6:36 PM
  • Hmmmm...where to start.

     

    • Your parameter should be of sysname datatype, not text
    • You do need at least one column.
    • Why are you creating permanent tables inside a sproc?  This seems like a very strange requirement

     

    This works:

     

    Code Snippet

    /****** Object:  StoredProcedure [dbo].[Procedure_TestCreateTable]    Script Date: 12/10/2008 12:05:00 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:        <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description:    <Description,,>

    -- =============================================

    ALTER PROCEDURE [dbo].[Procedure_TestCreateTable]

        -- Add the parameters for the stored procedure here

        (@tblName sysname)

    AS

    BEGIN

    EXEC('CREATE TABLE [dbo].[' + @tblName  + '] (Col1 int)')

     

    END

     

     

    EXEC dbo.Procedure_TestCreateTable 'Test'

     

     

    But it still doesn't get at why you're doing it.

    Wednesday, December 10, 2008 6:40 PM
  • Oh, I am using the membership provider in asp.net 2.0 to create users and roles.  I am also creating a database with tables for courses as well as a database with tables for students.

    So basically on student registration I'm wanting to create tables with information regarding their schedule, grades, etc. as something like StudentUsername_Schedule and StudentUsername_Grades, etc.

    On a registration of a Course I'm wanting to create course tables for information relating to the course for grades, announcements, quizes, etc. Such as CourseDeptCourseNoCouseSubNoSemesterYear_Announcements, etc.

    So you see, I'm wanting to call on the store procedure to create these tables for my by passing in either the student information or course information I'm needing to distinguish each table.

    Figured this would be the easiest way as opposed to going through and changing the default aspnetdb membership table.

    So would I not be doing it this way?
    Wednesday, December 10, 2008 7:40 PM
  • >>So would I not be doing it this way?

     

    Absolutely not! You should be normalising your table structure.

    www.r937.com/relational.html

     

    I'd love to offer more suggestions other than that link, but quite frankly there's too much lager in my system to give you sound advice Stick out tongue
    Wednesday, December 10, 2008 9:49 PM
  • I'm going to second gvee here. 

     

    You will create a whole universe of troubles creating individual tables as you've suggested.  Normalize a bit and you can probably do the whole thing with a lot less work than you might think.

     

     

     

    Wednesday, December 10, 2008 10:18 PM
  • Well, I understand the concept of normalization, and although I wasn't normalizing the tables in the traditional sense, I was in part taking on some aspects of normalizing my data.

    You see, my courses Database has a Courses and Departments tables.  The departments table simply lists the 4 character representation of each department.

    My Courses table is going to list all my courses.  I have Department (referenced to a databound control from a view on my departments table), CourseNo, CourseSubNo, Semester, Year, Instructor, DoW, StartDate, StartTime, EndDate, EndTime, Bldng, RoomNo, TotalSeats, AvailableSeats.

    Basically each Deparment is going to have multiple Courses, one to many relationship, and each Course can have the same CourseNo, but is uniquely identified by a combination of the CourseNo and CourseSubNo.

    Now, even though I could split the tables up even more to better represent this, I chose at the moment at least to keep it this way.

    Now, how was I normalizing it to an extent you may ask?  You see, I was going to derrive the unique tables from a combination of the Department,CourseNo,CourseSubNo,Semester,Year fields in my Courses table.  From a database engineering perspective this may not seem like an ideal practice.  But from my programming standpoint I can easily call a reference to the Courses table on those fields and then perform any function by passing those values as a parameter name for say UpdateCourseNotifications by passing it the parameters I recieved from Department,CourseNo,CourseSubNo,Semester,Year_Notifications if I follow the same naming practices across the database can I not?

    And to further simplify things whenever I created a Course I was going to create a Role for that course and whenever I registered a user for a course I was going to add the user to that Course's role following a very similar method, keep the naming the same across the system and its not hard to call on to the values or the data I need.

    Is this not okay for my situation or should I go ahead and break my table apart again and add a single primary key as like a guid or an auto incrementing value and then strictly normalize the tables that way?
    Thursday, December 11, 2008 12:44 AM
  • Oh and I want to add, thanks a ton for all the help so far.

    I only have a semester of database background and am basically wrapping on my final project.  I enjoy seeing different takes on how things should be done from people with alot more experience.

    And I know I might be a bit off here on the way I'm looking at things, I've been trying to approach this part from a database designer perspective but I am a programmer so I'm probably looking at it from the different side of the fence from time to time.
    Thursday, December 11, 2008 12:46 AM
  •  

    I'll try take a better look at this tomorrow, but I can't make any promises.

    Besides, this post counts as a bump for your benefit anyway Smile

    Thursday, December 11, 2008 11:15 PM