creating procudre for dynamic table

Question

creating procudre for dynamic table

raghuldrag

Hall of Fame

Points: 3160
More actions
October 9, 2012 at 10:58 pm

#262993

Hi friends,
create tab T1(name varchar(22),age numeric(22))
create tab T2(name varchar(22),age numeric(22))
insert into t1 values('ram',22)
insert into t1 values('am',26)
insert into t1 values('sam',28)
insert into t1 values('bam',23)
insert into t1 values('kam',21)
insert into t2 values('yam',22)
insert into t2 values('dam',22)
insert into t2 values('gam',22)
insert into t2 values('pam',22)
now i need to create procedure if am giving the table name has input
show the output of name only!!!!
exec pro(t1)
expecting output:
NAME
ram
am
sam
bam
kam

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

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

raghuldrag (10/9/2012)
Hi friends,
create tab T1(name varchar(22),age numeric(22))
create tab T2(name varchar(22),age numeric(22))
insert into t1 values('ram',22)
insert into t1 values('am',26)
insert into t1 values('sam',28)
insert into t1 values('bam',23)
insert into t1 values('kam',21)
insert into t2 values('yam',22)
insert into t2 values('dam',22)
insert into t2 values('gam',22)
insert into t2 values('pam',22)
now i need to create procedure if am giving the table name has input
show the output of name only!!!!
exec pro(t1)
expecting output:
NAME
ram
am
sam
bam
kam

exec pro(t1) won't work, this will: exec pro('t1')

Assuming the table name is captured into parameter Tablename, your stored procedure could look something like this:

IF @Tablename = 'T1'

SELECT DISTINCT Name FROM t1

ELSE IF @Tablename = 'T2'

SELECT DISTINCT Name FROM t2

You could also use dynamic sql for this.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

PiMané SSChampion Points: 13965 More actions · Answer 2

ChrisM@home (10/10/2012)
You could also use dynamic sql for this.

CREATE PROCEDURE GetData(@TableName NVARCHAR(120), @Columns NVARCHAR(255))

AS

BEGIN

DECLARE @Query AS NVARCHAR(MAX)

SET @Query = 'SELECT ' + @Columns + ' FROM ' + @TableName

EXEC sp_executesql @Query

END

GO

EXEC GetData('t1', 'name')

GO

In this example you can specify the columns you want on the output...

Pedro

If you need to work better, try working less...

raghuldrag Hall of Fame Points: 3160 More actions · Answer 3

while executing dis query error occured on output:

exec

Arthur Kirchner Ten Centuries Points: 1074 More actions · Answer 4

It's

EXEC GetData 't1', 'name'

GO

... without parentheses.

raghuldrag Hall of Fame Points: 3160 More actions · Answer 5

if you dnt mind would please explain the "sp_executesql" what is the use??

and what should be do there??

PiMané SSChampion Points: 13965 More actions · Answer 6

raghuldrag (10/10/2012)
if you dnt mind would please explain the "sp_executesql" what is the use??
and what should be do there??

Check this link on the site http://www.sqlservercentral.com/Forums/Topic1244180-1292-1.aspx.

There are few more just google "sp_executesql vs EXEC"...

Pedro

If you need to work better, try working less...