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
 Need Procedure

Author  Topic 

raky
Aged Yak Warrior

767 Posts

Posted - 2008-02-16 : 01:40:47


hi,I need a procedure which retrieves specified no.of columns starting from specified position from a specified table. Can any one help me.

Thanks in Advance.........


visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 01:47:29
Specified no of columns?didnt understand that? did you mean specified no of rows? or columns within a row?Explain
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 2008-02-16 : 02:29:25
quote:
Originally posted by visakh16

Specified no of columns?didnt understand that? did you mean specified no of rows? or columns within a row?Explain



suppose assume there is a table emp
table :emp
col1 col2 col3 col4 col5 col6
45 34 12 6 99 100
38 88 77 37 86 56


then if i pass the values emp, 3,2
the i should get the output

col3 col4
12 6
77 37

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-16 : 02:55:22
Use INFORMATION_SCHEMA.COLUMNS view.
Use dynamic SQL http://www.sommarskog.se/dynamic_sql.html



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-16 : 23:03:24
Check this for getting idea on systems catalog views under INFORMATION_SCHEMA

http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-18 : 03:53:03
Seems something like this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53384

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-18 : 10:03:55
Try:

Create Procedure GetColumnList(@TableName varchar(100), @StartAtCol int, @ColumnList varchar(8000) OUTPUT)
As Begin
Declare @Result varchar(8000)

Select @Result = Coalesce(@Result + ',', '') + SC.name
From sysobjects so
inner join syscolumns sc on so.id = sc.id
Where SO.xtype = 'U'
And SO.name = @TableName
And SC.colorder >= @StartAtCol
Order By SC.colorder

Set @ColumnList = @Result
End

--Usage
Declare @Result varchar(8000)
Exec GetColumnList 'sysdtslog90', 3, @Result OUTPUT
Print @Result
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 2008-02-19 : 04:30:45
quote:
Originally posted by Qualis

Try:

Create Procedure GetColumnList(@TableName varchar(100), @StartAtCol int, @ColumnList varchar(8000) OUTPUT)
As Begin
Declare @Result varchar(8000)

Select @Result = Coalesce(@Result + ',', '') + SC.name
From sysobjects so
inner join syscolumns sc on so.id = sc.id
Where SO.xtype = 'U'
And SO.name = @TableName
And SC.colorder >= @StartAtCol
Order By SC.colorder

Set @ColumnList = @Result
End

--Usage
Declare @Result varchar(8000)
Exec GetColumnList 'sysdtslog90', 3, @Result OUTPUT
Print @Result



Msg 134, Level 15, State 1, Procedure GetColumnList, Line 22
The variable name '@Result' has already been declared. Variable names must be unique within a query batch or stored procedure.

this is the error what iam getting.
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 2008-02-19 : 04:34:40
quote:
Originally posted by Qualis

Try:

Create Procedure GetColumnList(@TableName varchar(100), @StartAtCol int, @ColumnList varchar(8000) OUTPUT)
As Begin
Declare @Result varchar(8000)

Select @Result = Coalesce(@Result + ',', '') + SC.name
From sysobjects so
inner join syscolumns sc on so.id = sc.id
Where SO.xtype = 'U'
And SO.name = @TableName
And SC.colorder >= @StartAtCol
Order By SC.colorder

Set @ColumnList = @Result
End

--Usage
Declare @Result varchar(8000)
Exec GetColumnList 'sysdtslog90', 3, @Result OUTPUT
Print @Result




next even i tried with this
Declare @CList varchar(8000)
Exec GetColumnList 'tbl_abc_xyz', 3, @CList OUTPUT
Print @CList

iam getting the following error

Msg 217, Level 16, State 1, Procedure GetColumnList, Line 22
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Go to Top of Page

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-19 : 09:45:45
Execute each piece separately... The second piece is an example of usage.
Go to Top of Page
   

- Advertisement -