Post reply

Pass Tablename in a Stored Procedure

  • October 11, 2007 at 7:20 am

    #175878

    Hi guys,

    How do i pass a table name as a parameter to a stored procedure?

    The following code giving an error stated below

    ALTER PROCEDURE dbo.GetNextCourseID

    @TableName1 varchar(10)

    AS

    BEGIN

    SELECT * FROM @TableName1

    END

    RETURN 0

    Error: Must declare the variable @Tablename1

    Thanks

  • October 11, 2007 at 7:37 am

    #741200

    you can only do that with dynamic sql.

    dynamic sql has some advantages, but you loose the speed advantage of compiled execution plans.

    here's the example i always use:

    --for users who are too lazy to type "SELECT * FROM"

    CREATE procedure sp_show

    ----USAGE: sp_show gmact

    @TblName varchar(128)

    ----WITH ENCRYPTION

    As

    Begin

    exec('Select * from ' + @TblName)

    End

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 12, 2007 at 1:08 am

    #741447

    thanks lowell

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply