Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PoseyRobert
Starting Member
27 Posts |
 Posted - 2015-01-19 : 12:33:06
|
I am trying to create a store proc that I can use to change the table name based on the year.
How can I amend the year each time the year changes.
ALTER PROCEDURE [dbo].[sp_rpt_Ar_Invoice_Register]
@startDate datetime,
@endDate datetime
AS
IF @startDate = NULL or @startDate = ''
BEGIN
SET @startDate = CONVERT(VARCHAR(8), GETDATE(), 101)
SET @endDate = CONVERT(VARCHAR(8), GETDATE(), 101)
END
DECLARE @tableYear int
set @tableYearr = YEAR(@startDate)
select invoice_no, invoice_date, invoice_Qty, Invoice_Amt
[red]from 'INVOICE_' + @tableYear[\red]
where invoice_date >= @startDate and invoice_date <= @endDate |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-01-19 : 12:38:17
|
You can use sp_rename to change the name of an object, however I question this database design. I would look into table partitioning instead.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
 |
|
|
PoseyRobert
Starting Member
27 Posts |
Posted - 2015-01-19 : 12:47:43
|
Hi Tara,
I believe you might be miss understanding what I am asking.
I am not trying to rename the table.
We have a very large table that has over 10 million records. We separated the table by years
For example: Tables
INVOICE_2015
INVOICE_2014
INVOICE_2013
In the store proc I want to be able to use the same store proc but change the table name based on the year.
I hope that helps.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2015-01-19 : 14:57:43
|
Look into partitioning - instead of individual yearly tables. Partitioning would allow you to separate the table into yearly partitions and give you the same advantages as separate tables - without having the coding issues of accessing separate tables.
If you can't do that - then look into setting up synonyms for your tables:
CREATE SYNONYM dbo.INVOICE_CY FOR dbo.INVOICE_2015;
CREATE SYNONYM dbo.INVOICE_PY FOR dbo.INVOICE_2014;
Then in your code - use the synonym for the current year:
SELECT ...
FROM dbo.INVOICE_CY
WHERE ...
On the first of the year - drop the synonyms and recreate them for the new year. |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-01-19 : 17:41:00
|
10M rows is not really that many for SQL, as long as you:
Cluster the table by: invoice_date
[best guess you're now clustering on an identity column: get rid of that. cluster instead by how you search for the data. The idea/claim by some that tables should by "default" be clustered on identity is the biggest myth in dbs, and it does the most to harm overall performance.] |
|
|
|
|
|
|
How to change the table name in Store Proc