Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

pass table name as a parameter in stored procedure Expand / Collapse
Author
Message
Posted Saturday, September 24, 2011 7:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:38 AM
Points: 56, 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.
Post #1180576
Posted Saturday, September 24, 2011 10:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1180586
Posted Sunday, September 25, 2011 5:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:38 AM
Points: 56, 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.
Post #1180619
Posted Sunday, September 25, 2011 5:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
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 2008, MVP
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

Post #1180623
Posted Sunday, September 25, 2011 9:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1180652
Posted Sunday, September 25, 2011 5:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:38 AM
Points: 56, 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.
Post #1180693
Posted Sunday, September 25, 2011 5:28 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 27, 2014 6:38 AM
Points: 56, Visits: 69
I'll check it out. Thanks for everything!
Post #1180695
Posted Sunday, September 25, 2011 9:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1180714
Posted Friday, January 11, 2013 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 10:29 AM
Points: 1, 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
Post #1406191
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse