-- EXEC usp_DyanmicColumnBuilding 'Tbl_Pms_Fields',5
ALTER PROC usp_DyanmicColumnBuilding
(
		@TableName      VARCHAR(128),
		@NoOfColumns	INT
)
AS
SET NOCOUNT ON
BEGIN

		CREATE TABLE  #temp  ( ColName VARCHAR(128), ordinalpos int )

		INSERT INTO #temp
		select column_name,ordinal_position from information_schema.columns
		where table_name = @TableName
		order by ordinal_position

		declare @col varchar(8000)
		select @col = stuff((select ','+colname from #temp where ordinalpos <= @NoOfColumns for xml path('')),1,1,'')

		Declare @str varchar(max)
		select @str = 'SELECT '+ @col +' FROM  ' +@TableName

		print @str
		exec (@str)

END
SET NOCOUNT OFF

quote:

---

Originally posted by swathigardas

Hi ,

Is there way to add columns dynamically to my result set based on the input sent by the user. If the user sends the value as 2 , then first 2 columns of the result set should be sent as ouput.

Any help would be thankful

---

quote:

---

Originally posted by swathigardas

Yes thanks so much for both the solution. But in my scenario the result set comes from a stored procedure.
SO i need to Store the result set in a #Temp Table and then apply the query given by you on the Temp Table.

Correct me if i am wrong.
Thanks a lot for the Solution.

---