How to change the table name in Store Proc

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to change the table name in Store Proc

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

Posted - 2015-01-19 : 13:58:14

Oh gotcha! You will need dynamic SQL for that, however this will not scale. You should reconsider this table design and instead use partitioning. Please read this in its entirety: http://www.sommarskog.se/dynamic_sql.html

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

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.]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources