February 6, 2008 at 7:59 am
Hi, qucik q: is it possible to pass a parameter to a stored procdure, containing a table name?
like
CREATE PROCEDURE [dbo].[sp_rtt_update_usage_UsageMonth12]
@tbl_name as varchar(50)
AS
UPDATE [50008_tbl_Item_Usage] SET @tbl_name =
(
SELECT SUM(Quantity) as Sum3M FROM
(
........
i tried it , but havent spent that much time. so if some know how to do that, i really appreciate it!
thx tom
February 6, 2008 at 8:45 am
Nope. That isn't possible.
What you can do, although it is VERY strongly not recommended, is pass the table name in as a string and then use dynamic TSQL to use it. Something like this:
CREATE PROCEDURE MyProc
@Table1 nvarchar(50)
AS
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM ' + @Table1
exec sp_executesql @sql
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 6, 2008 at 9:41 am
In your example, that is a column name you're using in an update statement, not a table name.
You cannot dynamically set tables in a DML statement like
select * from @mytable
or update @mytable set x = y
because the compiler needs to resolve the table when the statement compiles. You can use dynamic SQL, but this potentialy gives you a SQL Injection point.
create procedure Myproc
@param
as
declare @cmd varchar(max)
select @cmd = 'update ' + @param + ' set x = y'
exec(@cmd)
return.
February 6, 2008 at 7:09 pm
If you do go with passing the table name in and using dynamic SQL in your queries, it is suggested that you first validate the text that came in before using it to build your query.
Is it a valid table name? Pass the text field to sp_tables_ex, and check its response. Or if the table is one of a fixed list of valid names, or if its something like an output table from another process (like a monthly summary report), then you can directly validate the name passed in yourself as matching an element of the list or having the correct format
for the known process's output tables.
Does it have the proper field names/structure for my code? Query the sys.syscolumns table for the fields contained in the now-valid table name you have, and compare to the fields your query will require, for datatypes and field names.
Doing these checks before using the text in a dynamic query will greatly reduce the chances of the string containing "rogue" SQL commands designed to compromise your system.
February 8, 2008 at 1:21 pm
Joe Celko (2/7/2008)
No, of course not! Think about it for a minute. Tables are all different; they model one and only one kind of entity. So your magical procedure would work on Britney Spears, Squids and Automobiles or anything in the entire Universe. That is magic, not logic.
But Joe, that's EASY!
create proc UniversalMagic
(@SQL_in varchar(max))
as
begin try
exec (@sql_in)
end try
begin catch
exec dbo.universalmagic @sql_in
end catch
There! Perfect! The ultimate stored procedure for Universal Magic!
Wait ... never mind ... it still can't do Julien Fries. I give up.
(Or am I trying too hard on the whole irony thing?)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2008 at 1:27 pm
GSquared (2/8/2008)
Joe Celko (2/7/2008)
No, of course not! Think about it for a minute. Tables are all different; they model one and only one kind of entity. So your magical procedure would work on Britney Spears, Squids and Automobiles or anything in the entire Universe. That is magic, not logic.But Joe, that's EASY!
create proc UniversalMagic
(@SQL_in varchar(max))
as
begin try
exec (@sql_in)
end try
begin catch
exec dbo.universalmagic @sql_in
end catch
There! Perfect! The ultimate stored procedure for Universal Magic!
Wait ... never mind ... it still can't do Julien Fries. I give up.
(Or am I trying too hard on the whole irony thing?)
Actually I'm almost done with the true ultimate stored procedure. Mine doesn't require any input parameters though. It just knows what you want and gives you the result.
Oddly enough I think I've been asked for that particular SP at almost every programming job I've ever had.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 8, 2008 at 2:10 pm
Of course!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply