sql server stored procedure

Question

Post reply

sql server stored procedure

r.vijaykanth

SSC Rookie

Points: 32
More actions
March 12, 2010 at 6:23 am

#94643

i want to create a stored procedure
which takes tablename as input and returns table data as out put
create procedure sp1(@tablename varchar(20)) returns table
as
return(select * from @tablename)
i've also tried
return('select * from' +@tablename)
its not working ....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

--for users who are too lazy to type "SELECT * FROM TABLENAME"

CREATE procedure sp_show

--USAGE: sp_show TABLENAME

@TblName varchar(128)

As

Begin

exec('Select * from ' + @TblName)

End

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LythamsFinest SSCommitted Points: 1786 More actions · Answer 2

Not sure this is what you are after but the following syntax will create you a stored procedure taking a table name as input and will output the contents:

CREATE PROC [dbo].[ProcTest]

@TableSYSNAME

AS

BEGIN

DECLARE @SelectSyntaxNVARCHAR(200)

SET @SelectSyntax = 'SELECT * FROM ' + @Table

EXEC sp_executeSQL @SelectSyntax

END

Execute as follows:

EXEC [dbo].[ProcTest] @Table = '{table}'

Hope this helps

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Spencer Evans (3/12/2010)
Execute as follows:
EXEC [dbo].[ProcTest] @Table = '{table}'
Hope this helps

No, better like this:

EXEC [dbo].[ProcTest] @Table = '{table}; exec sp_resetpassword ... '

Just to make sure you take control over those optimists' server.

For those who is not ready to hand their servers to smart strangers it should look like this:

CREATE PROC [dbo].[ProcTest]

@Table SYSNAME

AS

BEGIN

DECLARE @SelectSyntaxNVARCHAR(200)

SET @Table = QUOTENAME(Object_Name(Object_ID(@Table, 'U')) )

IF @Table IS NULL

Report SQL injection here

ELSE

BEGIN

SET @SelectSyntax = 'SELECT * FROM ' + @Table

EXEC sp_executeSQL @SelectSyntax

END

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

r.vijaykanth (3/12/2010)
i want to create a stored procedure
which takes tablename as input and returns table data as out put

Why? What is the business reason for this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

r.vijaykanth SSC Rookie Points: 32 More actions · Answer 5

r.vijaykanth

SSC Rookie

Points: 32

March 17, 2010 at 3:59 am

#1135070

thank u very much....