Simple Stored Procedure - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Simple Stored Procedure

Author

Topic

rajiv460
Starting Member

3 Posts

Posted - 2010-09-13 : 11:53:50

Hi,
I need to write a simple parametarized stored Proc which takes a table name as an input and displays the contents of the table.

If I say
Exec Tabledata 'Employee'

the result set should
Select * From Employee
I tried Create Procedure USP_TableData
@TableName Varchar(30)
As
Begin
Declare @Name Varchar(30)
Set @Name=@TableName
Select * From Name
End

Drop Procedure USP_TableData
Exec Usp_TableData 'Person.Address'

But its not working.

Rajiv V

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-13 : 12:10:37

You need dynamic sql for that.

But can I ask why you do need a Stored Procedure? Why not just doing the SELECT-Statement?

No, you're never too old to Yak'n'Roll if you're too young to die.

rajiv460
Starting Member

3 Posts

Posted - 2010-09-13 : 12:16:20

Hi,
Thanks for the reply.

I am new to SQL. So I am like practicing Stored Procs. I saw this somewhere and wanted to see whether I can do it or not. So can you help me out in Dynamic SQL?

Rajiv V

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-13 : 12:54:13

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

No, you're never too old to Yak'n'Roll if you're too young to die.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-13 : 15:30:41

quote:
Originally posted by rajiv460

I need to write a simple parametarized stored Proc which takes a table name as an input and displays the contents of the table.

You need to read a book on RDBMS and data modeling. A table models one and only one kind of entity or relationship. So you want a magic procedure that works on automobiles, squids, and anything else in the entire universe.

No such thing. You will wind up dynamic SQL, poor performing code and a general mess.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Ancy
Starting Member

23 Posts

Posted - 2010-09-14 : 06:06:51

If you still want to create the proc, here is the syntax

Create Procedure USP_TableData
@TableName Varchar(30)
As
Begin
exec('Select * From '+ @TableName)
End

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 06:21:02

exec('Select * From ' + QUOTENAME(@TableName))

would be safer.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-14 : 06:57:20

And here's why


EXEC USP_TableData 'employee; DROP TABLE payroll ; --'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-14 : 07:21:05

And this select shows the difference:

select
'employee; DROP TABLE payroll ; --',
quotename('employee; DROP TABLE payroll ; --')

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources