Pass in table name to stored proc?

Results 1 to 3 of 3

Thread: Pass in table name to stored proc?

  1. #1
    Join Date
    Dec 1969

    Default Pass in table name to stored proc?

    I want to create a general stored proc that I can pass a table name to get all records and all columns within that table. In other words I want to be able to just pull all data from the "myTable" table by simply doing this:<BR><BR>EXECUTE gsp_GetAllData myTable<BR><BR>I can&#039;t figure out how to get the stored proc to use a variable as a table name. For example right now my proc looks like this<BR><BR>CREATE PROCEDURE dbo.gsp_GetAllData<BR>(@tableName nvarchar(255))<BR>AS<BR>IF EXISTS(SELECT * FROM @tableName)<BR> SELECT * FROM @tableName<BR>GO<BR><BR>Is there a way to do this?<BR>

  2. #2
    Join Date
    Dec 1969

    Default You can but kind of pointless...

    The big advantage of a SP is that it can compile and pre-plan the query.<BR><BR>When the SP doesn&#039;t even know what table it is going to be working with, there&#039;s essentially nothing it can do to optimize the work. So why not just do the query with ordinary SQL from VBS/ADO? Especially if the query is a dead simple as your example.<BR><BR>Anyway, if you are still stuck on doing it an SP, then you have to build up a string (yes, just an ordinary string, same way you would build up a SQL string in VBS, but with far fewer nice functions and such to help you) and EXEC it within the SQL. <BR><BR>&#060;shrug&#062; Your poison. &#060;/shrug&#062;<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default use sp_executesql

    You can use sp_executesql and sortof get the same benifits of a stored procedure. So long as the parameters are the same and the sql statement is the same it will compile it and make an execution plan for it. If your going to change the tables in it then for each different version of that sql statement you will get a different plan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts