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

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  October 10, 2012 at 1:03 am

  #1547482

  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

  October 10, 2012 at 4:08 am

  #1547562

  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

  October 10, 2012 at 5:46 am

  #1547602

  while executing dis query error occured on output:

  exec

• Arthur Kirchner

  Ten Centuries

  Points: 1239

  More actions

  October 10, 2012 at 6:35 am

  #1547624

  It's

  EXEC GetData 't1', 'name'

  GO

  ... without parentheses.

• raghuldrag

  Hall of Fame

  Points: 3160

  More actions

  October 10, 2012 at 10:48 pm

  #1547881

  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

  October 11, 2012 at 12:38 am

  #1547897

  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...