Passing Table Name variables into a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Passing Table Name variables into a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-28 : 08:37:46

Spearion writes "Hi

Is it possible to pass a table name as a parameter into a SQL Server 7.0 stored procedure? I need the stored procedure to do the same function but over many and different tables ...

If so could you please send me a simple example so I can get the syntax correct ...

Cheers S"

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-28 : 09:33:33

I hope this is to assist in automating admin functions...otherwise don't do this...



USE Northwind
GO

CREATE PROC mySproc
	@TABLE_NAME sysname
AS

	DECLARE @SQL varchar(8000)
	SELECT @SQL = 'SELECT * FROM ' + @TABLE_NAME
	EXEC(@SQL)
GO


EXEC mySproc sysobjects 
GO

DROP PROC mySproc
GO

Brett

8-)

Granick
Starting Member

46 Posts

Posted - 2003-10-29 : 14:05:03

A word of caution when running a proc with Dynamic SQL like this. The user that runs the proc will have to have rights to the table(s) used, unlike if you did a straight select, and the permissions are based on the Proc permissions.

Just something I have run into, and had been frustrated with in the past.

Shannon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-29 : 14:07:07

Yes that is one of the problems with dynamic sql. The other is performance.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-29 : 14:20:51

That's why I said to make sure it's under your control for thing like administration.

It shouldn't be used in applications...

Where did I read that some sql is embedded in a URL...

Imagine someone mucking with that...check out this:


USE Northwind
GO

CREATE PROC mySproc
	@TABLE_NAME sysname
AS

	DECLARE @SQL varchar(8000)
	SELECT @SQL = 'SELECT * FROM ' + @TABLE_NAME 
		+ ' GO SELECT '+ ''''+ 'Imagine some very Nasty SQL Statement here' + ''''
	EXEC(@SQL)
GO


EXEC mySproc sysobjects 
GO

DROP PROC mySproc
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources