November 14, 2005 at 12:43 pm
Hi there-
I have two tables: orders and orders_archive. Tables are identical exept for date of storage--for performance reasons. I'd like to create a single sproc that can operate on either tbl, with the name of the tbl being the input parm and do not want to resort to sp_executesql.
So something like this.
---
CREATE PROCEDURE sp_order @tablename char 100 AS
SELECT * FROM
@tablemame WHERE blah blah blah
-------
Is there anyway to this or do I have write the SELECTs twice? Tried as-is and error says "Must declare variable" which I'm interpretting as it cannot be done.
Would be most gratefull for any assitance.
al
November 14, 2005 at 1:00 pm
Shooting from the hip, (i.e. no Query Analyzer)... Two things come to mind:
1. if the data is NOT duplicated in the archive with the current orders (i.e., Orders has records from current month forward and the OrdArch has records prior to the current month, etc) THEN you can simply create a view with UNION ALL between the two tables. Be sure you have all the appropriate indexes.. based on your criteria (dates, orderID's, etc)
OR,
2. Create your proc with an input parameter indicating which table to use then inside your proc, have two select statements - one for each table. You run one or the other depending on the value of @tableName. You can do this in a CASE or IF statement
hth
Mark
November 14, 2005 at 1:48 pm
Mark is correct you could have two separate SQL statements within the proc, however beware of the execution plans. It will be saved with the first performance and whatever table is used you'll end up with performance issues on the other side. My suggestion is to have two procedures or views and handle which one in the User Interface of the application.
Do some tests and I think you'll see it's worth writing almost identical code twice. Also an indexed view on a union of the two tables might make sense.
Hope this helps.
If the phone doesn't ring...It's me.
November 15, 2005 at 12:54 am
Hi,
1) If you send archive date as a parameter and store archive date in some table you can solve this by if statements:
IF @QueryDate > @ArchiveDate
Select * from orders
else
Select * from orders_archive
2) If not you can use UNION ALL:
Select * from orders
UNION ALL
Select * from orders_archive
3) Maybe you can also use dynamic SQL:
declare @stmt varchar (1000)
SET @stmt = "SELECT * FROM " + @tablemame
EXEC ( 'EXEC sp_executesql N'''+@stmt +'''')
Hope these help.
Cenk
November 15, 2005 at 12:58 pm
Write two procedures. One for each table.
Then create a third procedure that decides which of the other two to call based upon the input parameter. I think this will eliminate any execution plan problems, while satisfying the criteria you specified.
hth jg
November 15, 2005 at 5:25 pm
USE
AdventureWorks
GO
CREATE
PROCEDURE udp_Order
@tablename
varchar(100)
AS
EXEC('SELECT * FROM ' +
''
+ @tablename + '' )
-- WHERE blah blah blah
GO
Execute the stored procedure using syntax below (2005 example, use dbo.tablename for SQL 2000)
EXEC
udp_Order 'HumanResources.Department'
- I would recommend against using "sp_" prefix for your user-defined stored procedures.
- "var 100" needed parens around data size.
- I would recommend using datatype varchar, as the length of your table names varies. Use of static size var may pad your table name with spaces requiring trim or other handling.
- Cent's dynamic SQL approach also looks good, as use of sp_executeSQL can provide better performance.
- Could add default on end of input
variable line: @tablename varchar(100) = 'Order'
This will cause Order table to be selected by default when no table name was provided.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply