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
Nuts
Nuts
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 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
John Rowan
John Rowan
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14080 Visits: 4591
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
Seggerman-675349
Seggerman-675349
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 559
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.
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16257 Visits: 25280
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
palsatish1
palsatish1
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 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
Simhadri Basava
Simhadri Basava
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 33
How to include where condition in above query.
scott.pletcher
scott.pletcher
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1192 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
Simhadri Basava
Simhadri Basava
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 33
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'.
John Rowan
John Rowan
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14080 Visits: 4591
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
JohnBevan
JohnBevan
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 58
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
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