Hi Wondering if anyone can help. My problem is this

I have numerous tables in my database all of which are exactly the same format, just different names. I am trying to write a stored procedure that will allow me to choose which table I insert details into based on a variable. I'm currently trying to do this with a case statement without much success. This stored proc is being run by a .net web application.I have to do this with a stored proc, and cannot generate the SQL directly from the application (due to the specifications of the application)

the basic flow of what I have is below

Variable called @Table of type int passed to SProc along with all values

    BEGIN
                INSERT INTO

             CASE @Table
            WHEN 1 THEN dbo.cAlerts
            WHEN 2 THEN dbo.cAnalysisCode
             END
            (
            type,
            IdAlpha,
            [Description])
        VALUES
            (
            @Type,
            @Alpha,
            @Description)

    END

Any help or suggestions would be appreciated

Thanks

Carl

Hi

If you know when 1 is passed it needs to insert to cAlerts and when 2 is passed to insert to cAnalysisCode etc. You can use separate INSERT statements in your SP :-

IF @Table = 1
BEGIN
    INSERT INTO dbo.cAlerts
    (type, IdAlpha, [Description])
        VALUES (@Type, @Alpha, @Description)
END

IF @Table = 2
BEGIN
    INSERT INTO dbo.cAnalysisCode
    (type, IdAlpha, [Description])
        VALUES (@Type, @Alpha, @Description)
END

Nickie

Ok, thanks. I was thinking about using a similar solution, and in the interests of getting the project working, I will. Will there be an issue of running very long scripts on SQL server, even though only a few lines of that script are executed, since there are around 30 tables like this, and I have to effectively re-write the insert for each one

Thanks again

Carl