Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

creating procudre for dynamic table Expand / Collapse
Author
Message
Posted Tuesday, October 09, 2012 10:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
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




Post #1370673
Posted Wednesday, October 10, 2012 1:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,058, Visits: 5,740
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.



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1370718
Posted Wednesday, October 10, 2012 4:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:49 AM
Points: 513, Visits: 1,123
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...
Post #1370813
Posted Wednesday, October 10, 2012 5:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
while executing dis query error occured on output:
exec
Post #1370863
Posted Wednesday, October 10, 2012 6:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:32 AM
Points: 152, Visits: 384
It's

EXEC GetData 't1', 'name'
GO

... without parentheses.
Post #1370890
Posted Wednesday, October 10, 2012 10:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
if you dnt mind would please explain the "sp_executesql" what is the use??
and what should be do there??
Post #1371207
Posted Thursday, October 11, 2012 12:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 10:49 AM
Points: 513, Visits: 1,123
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...
Post #1371230
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse