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 Tuesday, April 15, 2008 9:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 30, 2010 8:02 PM
Points: 155, Visits: 215
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
Post #485395
Posted Tuesday, April 15, 2008 10:19 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:29 PM
Points: 3,840, Visits: 3,852
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.

http://www.sommarskog.se/dynamic_sql.html




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #485405
Posted Thursday, April 17, 2008 6:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:19 AM
Points: 250, Visits: 537
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.
Post #486367
Posted Thursday, April 17, 2008 10:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #486592
Posted Tuesday, December 8, 2009 6:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 8, 2009 8:24 AM
Points: 3, Visits: 5
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
Post #830625
Posted Wednesday, September 15, 2010 1:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 11:09 PM
Points: 2, Visits: 32
How to include where condition in above query.
Post #986608
Posted Wednesday, September 15, 2010 2:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473
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
Post #986669
Posted Wednesday, September 15, 2010 2:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 11:09 PM
Points: 2, Visits: 32
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'.
Post #986680
Posted Wednesday, September 15, 2010 2:26 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 6:29 PM
Points: 3,840, Visits: 3,852
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.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #986682
Posted Monday, December 20, 2010 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 5, 2013 5:04 AM
Points: 2, Visits: 43
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
Post #1037320
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse