April 15, 2008 at 9:35 pm
Hi
Can I pass a table name as a stored procedure ?? when I did that, it gives me anerror saying you must declare a parameter.
Here is what i did
CREATE PROCEDURE AddnewServiceCategory
(
@tablename nvarchar(50)
)
as
SELECT @tablename.Funder, @tablename.[Facility ID]
FROM @tablename
It gave me the following error
Must declare the table variable "@tablename".
What should I do?
Thanks
April 15, 2008 at 10:19 pm
You'll need to work with using dynamic SQL to make that work properly. But beware, there are some things to consider when working with dynamic SQL. Here's a good read on the topic.
April 17, 2008 at 6:51 am
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.
April 17, 2008 at 10:15 am
Using dynamic SQL try this:
CREATE PROCEDURE AddnewServiceCategory
(
@tablename nvarchar(50)
)
as
DECLARE @sSQL nvarchar(500)
SET @sSQL = 'SELECT ' + @tablename +'.Funder, ' + @tablename +'.[Facility ID]' +
' FROM ' + @tablename
PRINT @sSQL
/* test as: addnewservicecategory 'dbo.DBinfo'*/
Here is the result of the PRINT statement
SELECT dbo.DBinfo.Funder, dbo.DBinfo.[Facility ID] FROM dbo.DBinfo
if the statement is correct, syntax, columns selected etc then modify
the procedure by removing / commenting the print statement and insert EXECUTE @sSQL to retrieve the data
December 8, 2009 at 6:57 am
you just write as:
create procedure [dbo].[GetDataFromTable]
(
@tablename varchar(50)
)
as
begin
EXEC('Select * from '+@tablename)
end
this will give you whole data of that table which one name you passed in the parameter
By:- Satish Pal
September 15, 2010 at 1:22 pm
How to include where condition in above query.
September 15, 2010 at 2:18 pm
To be safe, you should add brackets around the table name in bitbucket's code.
Also, I strongly suggest using an alias on the table name instead of repeating the full table name throughout the query.
So:
SET @sSQL = 'SELECT t1.Funder, t1.[Facility ID] FROM ' +
'[' + @tablename + '] AS t1'
PRINT @sSQL
Scott Pletcher, SQL Server MVP 2008-2010
September 15, 2010 at 2:26 pm
Dear Scott thanks for your reply,
Please check the below query,I have used palsatish query.Need to add where condition in that.
Alter procedure tabproc
(
@tablename Varchar(500)
)
as
begin
EXEC('Select count(*) from '+@tablename where isactive=0 )
End
I'm error like
Incorrect syntax near the keyword 'where'.
September 15, 2010 at 2:26 pm
Simhadri Basava (9/15/2010)
How to include where condition in above query.
This thread is a year old. I would suggest creating a new thread with your question.
December 20, 2010 at 11:50 am
Heya,
possibly a bit late, but if it's still of use. . .
Alter procedure tabproc
(
@tablename sysname
)
as
begin
EXEC('Select count(*) from ' + @tablename + ' where isactive = 0' )
End
Basically the code in the exec statement is creating a string which contains your statement - you're concatenating the string value held by the variable @tablename with the rest of the statement. Once concatenated, this new string is sent to the exec command, which runs the string as if it had been typed as a statement.
I also changed the type of @tablename from varchar(500) to sysname. That's not required, but is the "correct" data type for holding object names. I doubt it affects performance, but it may give you a little future proofing on SQL upgrades, should the allowed table name length ever change.
Seggerman suggested dynamically creating a synonym. That's a nice idea, but may have concurrency issues (e.g. if the same code is called to run for two different tables at the same time your synonym may be incorrectly updated for one thread by the other). Also, I suspect this may have adverse affects on performance. I'm not a DB expert though, and haven't experimented, so these may not be problems.
Hope that helps,
JB
September 24, 2011 at 7:20 pm
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.
September 24, 2011 at 10:43 pm
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
Change is inevitable... Change for the better is not.
September 25, 2011 at 5:14 am
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.
September 25, 2011 at 5:34 am
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
September 25, 2011 at 9:14 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply