pass table name as a parameter in stored procedure

  • Hi

    Can I pass a table name as a stored procedure ?? when I did that, it gives me anerror saying you must declare a parameter.

    Here is what i did

    CREATE PROCEDURE AddnewServiceCategory

    (

    @tablename nvarchar(50)

    )

    as

    SELECT @tablename.Funder, @tablename.[Facility ID]

    FROM @tablename

    It gave me the following error

    Must declare the table variable "@tablename".

    What should I do?

    Thanks

  • You'll need to work with using dynamic SQL to make that work properly. But beware, there are some things to consider when working with dynamic SQL. Here's a good read on the topic.

    http://www.sommarskog.se/dynamic_sql.html

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • I am not sure of the feasibility of this but I would recommend writing your program to use a synonym for the table name - then use dynamic sql to create the synonym from whatever the table name is. Then DROP the synonym at the end. That way the logic of the stored procedure is static and only the synonym creation is dynamic.

  • Using dynamic SQL try this:

    CREATE PROCEDURE AddnewServiceCategory

    (

    @tablename nvarchar(50)

    )

    as

    DECLARE @sSQL nvarchar(500)

    SET @sSQL = 'SELECT ' + @tablename +'.Funder, ' + @tablename +'.[Facility ID]' +

    ' FROM ' + @tablename

    PRINT @sSQL

    /* test as: addnewservicecategory 'dbo.DBinfo'*/

    Here is the result of the PRINT statement

    SELECT dbo.DBinfo.Funder, dbo.DBinfo.[Facility ID] FROM dbo.DBinfo

    if the statement is correct, syntax, columns selected etc then modify

    the procedure by removing / commenting the print statement and insert EXECUTE @sSQL to retrieve the data

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • you just write as:

    create procedure [dbo].[GetDataFromTable]

    (

    @tablename varchar(50)

    )

    as

    begin

    EXEC('Select * from '+@tablename)

    end

    this will give you whole data of that table which one name you passed in the parameter

    By:- Satish Pal

  • How to include where condition in above query.

  • To be safe, you should add brackets around the table name in bitbucket's code.

    Also, I strongly suggest using an alias on the table name instead of repeating the full table name throughout the query.

    So:

    SET @sSQL = 'SELECT t1.Funder, t1.[Facility ID] FROM ' +

    '[' + @tablename + '] AS t1'

    PRINT @sSQL

    Scott Pletcher, SQL Server MVP 2008-2010

  • Dear Scott thanks for your reply,

    Please check the below query,I have used palsatish query.Need to add where condition in that.

    Alter procedure tabproc

    (

    @tablename Varchar(500)

    )

    as

    begin

    EXEC('Select count(*) from '+@tablename where isactive=0 )

    End

    I'm error like

    Incorrect syntax near the keyword 'where'.

  • Simhadri Basava (9/15/2010)


    How to include where condition in above query.

    This thread is a year old. I would suggest creating a new thread with your question.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Heya,

    possibly a bit late, but if it's still of use. . .

    Alter procedure tabproc

    (

    @tablename sysname

    )

    as

    begin

    EXEC('Select count(*) from ' + @tablename + ' where isactive = 0' )

    End

    Basically the code in the exec statement is creating a string which contains your statement - you're concatenating the string value held by the variable @tablename with the rest of the statement. Once concatenated, this new string is sent to the exec command, which runs the string as if it had been typed as a statement.

    I also changed the type of @tablename from varchar(500) to sysname. That's not required, but is the "correct" data type for holding object names. I doubt it affects performance, but it may give you a little future proofing on SQL upgrades, should the allowed table name length ever change.

    Seggerman suggested dynamically creating a synonym. That's a nice idea, but may have concurrency issues (e.g. if the same code is called to run for two different tables at the same time your synonym may be incorrectly updated for one thread by the other). Also, I suspect this may have adverse affects on performance. I'm not a DB expert though, and haven't experimented, so these may not be problems.

    Hope that helps,

    JB

  • Hello. This post was a huge help for me. I am able to get it to work in a stored procedure. Is it possible in a function? Cannot get the same similar code to work in a function.

    Thank you in advance.

  • mjbkm (9/24/2011)


    Hello. This post was a huge help for me. I am able to get it to work in a stored procedure. Is it possible in a function? Cannot get the same similar code to work in a function.

    Thank you in advance.

    Nope... not possible. You cannot use dynamic SQL in a function.

    Shifting gears a bit... what do you want the function to do?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Well, I have a huge table. Going on 3,000,000+ records with a aspx web front where users actively query this table with around 10 different parameters. Just completed SQL/aspx classes and newbie. I have been an Access/vb programmer for 10+ years. So my job is to figure out how to handle a large table that needs this type of activity and how to do it quickly. So far I am pretty successful... I have the largest possible query running in 18 seconds. However, I think that is still pretty slow.... Been working on:

    Limit columns returned

    Primary Key

    Indexes on where clause fields

    avoiding case statements.

    grouping, joins, & sort last

    Found this option. And it worked pretty good -I mean looking forward, splitting this table up by year. Because the user always queries a specific year at a time. Never a range of years. So I was going to use a table name as a parameter to pick what table(year) to query by. Making the number of tables I am working with less.

    However, if you have any ideas that would be great. Thank you for your help.

  • mjbkm (9/25/2011)


    Found this option. And it worked pretty good -I mean looking forward, splitting this table up by year. Because the user always queries a specific year at a time. Never a range of years. So I was going to use a table name as a parameter to pick what table(year) to query by. Making the number of tables I am working with less.

    Do be honest, if your indexing is good, this won't help much, if at all.

    Maybe create a new thread and post your query and we'll help you tune it without manual partitioning (which can be a nightmare to manage)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • mjbkm (9/25/2011)


    Well, I have a huge table. Going on 3,000,000+ records with a aspx web front where users actively query this table with around 10 different parameters. Just completed SQL/aspx classes and newbie. I have been an Access/vb programmer for 10+ years. So my job is to figure out how to handle a large table that needs this type of activity and how to do it quickly. So far I am pretty successful... I have the largest possible query running in 18 seconds. However, I think that is still pretty slow.... Been working on:

    Limit columns returned

    Primary Key

    Indexes on where clause fields

    avoiding case statements.

    grouping, joins, & sort last

    Found this option. And it worked pretty good -I mean looking forward, splitting this table up by year. Because the user always queries a specific year at a time. Never a range of years. So I was going to use a table name as a parameter to pick what table(year) to query by. Making the number of tables I am working with less.

    However, if you have any ideas that would be great. Thank you for your help.

    Do you have the Standard Edition or the Enterprise Edition of SQL Server? And, just so you know... CASE functions in SELECT list aren't so bad.

    And Gail is right... if your indexes are good, you shouldn't have a problem with SELECTs although there are some maintenance aspects that provide some payoff insofar as rebuilding indexes goes if your "manual" partitioning (which can be simplified with some intelligent scripting) is based on a temporal column.

    In either case, you shouldn't have to call out a specific table name for each year that you want to process. Lookup "Table Partitioning" if you have the Enterprise Edition and "Partitioned Views" if you have the Standard Edition.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 18 total)

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