Passing table name as parameter

  • Hello all,

    I am trying to pass table name as parameter.

    my code is like

    declare @value varchar(50)

    set @value='select name from sysobjects where name=?'

    select * from @value

    but this is giving me error like "must declare the variable @value.

    Do we have any other option from where i can do this.

    Need your help.

  • not sure what exactly you are looking for. it kind of looks like you wnat to test if a table exists?

    this code example works, but might not be what you are after:

    declare @value varchar(50)

    SET @value = 'TallyCalendar'

    select name from sysobjects where name=@value

    --Or?

    select name from sysobjects where name like @value+ '%'

    if you are trying to check if a table exists or not, I usaually use somethign like this:

    --does not exist test

    IF (OBJECT_ID(N'TallyCalendar') IS NULL)

    BEGIN

    --Do Stuff

    print 'Create Table ?'

    END

    --really does exist test

    IF (OBJECT_ID(N'Invoices') IS NOT NULL)

    BEGIN

    --Do Stuff

    print 'INSERT INTO Table ?'

    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!

  • Hi,

    I want to pass the table name as parameter,

    I am designing a dts package in sql server 2000,where i am trying to load data from one server to another server with same table structure,I want to pass the table name at the run time and loading data into that particular table

  • Sounds like dynamic sql. You can't create a varchar variable and then select from it. You would build your string as you did and then execute it via sp_executesql.

    Been a LONG time since I did anything with DTS but there may be some other that are more current with it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This might help.

    declare @value varchar(50) = 'Test'

    declare @query varchar(max)

    Set @query = 'Select * From'+' '+@value+';'

    Execute (@query)

    Vinu Vijayan

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 5 posts - 1 through 4 (of 4 total)

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