SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


pass table name as a parameter in stored procedure


pass table name as a parameter in stored procedure

Author
Message
mjbkm
mjbkm
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 69
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211975 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mjbkm
mjbkm
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 69
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220915 Visits: 46279
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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211975 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mjbkm
mjbkm
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 69
OK - Thanks. What I will do is start from the beginning and post a new question about my indexes first to make sure I have that setup correct.
mjbkm
mjbkm
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 69
I'll check it out. Thanks for everything!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211975 Visits: 41977
Seggerman-675349 (4/17/2008)
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.


And if two instances of the same proc run at the same time, what then? ;-)

And, yeah... I realize the post is more than 3 years old but since this thread has been reactivated, I wanted to make sure folks knew this is likely a bad idea.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jessie 20540
jessie 20540
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 17
you may try this:
create a table type and define the table structure;
declare the SP that has a parameter of tble type;
declare a table type variable and reference the table type;
insert the data from your table to the variable;
Now you can pass the variable to the procedure and execute it.
Here is the link by Pinaldave
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/

Good luck
Jessie
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search