September 19, 2011 at 3:41 am
I have a table and want to display column names and their values as given below
Create table TestTable
(TransID int identity(1,1)Primary key,
FirstName varchar(50),
LastName Varchar(50)
)
GO
insert into TestTable
values('Azhar','Iqbal')
GO
insert into TestTable
values('Khalid','Lateef')
GO
insert into TestTable
values('Waseem','Sattar')
GO
select * from TestTable
I want to pass TransID to tableValue function and Table Value function Should Display fiiltered Data on the basis of TranID
--Example of Table Value Function would as
Create function dbo.BeforeAfterValue(@TransID in,@TableName varchar(50))
returns @tbl TABLE
(
ID int identity(1,1),
ColName Varchar(50),
ColValue varchar(50)
)
AS
returns @tbl
We will call This Table Value Function as
select * from dbo.BeforeAfterValue(1,'TestTable')
Table parameter would vary So code should be dynamic .Whenever we will pass any table , it should return all column Name v/s values of Columns.
--It should return Result set as
--If TranID=1 Then Result Set would be
ColNames ColValues
FirstName Azhar
LastName Iqbal
Result set can Vary b/c table can be of varibales columns.
Any Help.
Thanks
September 19, 2011 at 4:58 am
Hi,
This CREATE TYPE code will run on only SQL 2008
First Create User Defined Table:-
CREATE TYPE dbo.DynamicTbl AS TABLE
(
@TransID as int,
@TableName as varchar(50)
)
Then Create Procedure or Function:-
Create Procedure dbo.BeforeAfterValue
@TABLENAME as DynamicTbl
AS
BEGIN
select ColNames, ColValues from
(
select Transid, 'FirstName' as ColNames, FirstName as ColValues
from @TABLENAME
union
select Transid, 'LastName'as ColNames, LastName as ColValues
from @TABLENAME
) as A
where @TransID = 1
END
Simple Select Statement:-
select ColNames, ColValues from
(
select Transid, 'FirstName' as ColNames, FirstName as ColValues
from TestTable
UNION
select Transid, 'LastName'as ColNames, LastName as ColValues
from TestTable
) as A
where TransID = 1
Thanks
Palash Gorai
September 19, 2011 at 5:31 am
I am using SQL Server 2008R2. When I run the following Code it give me following Error.
CREATE TYPE dbo.DynamicTbl AS TABLE
(
@TransID as int,
@TableName as varchar(50)
)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@TransID'.
And Also FirstName and LastName are hardcoded and if there are more than two columns then We need to hardCode more values.
Further Explanation is as under.
declare @tbl TABLE
(
ID int identity(1,1),
ColName Varchar(50),
ColValue varchar(50)
)
I want solutions like
insert into @tbl
SELECT column_name
FROM information_schema.columns
WHERE table_name = @TableName
This will Store all Columns names of given table into @tbl. And Then itrerate through a table to get Values of a particular Column and update the @tbl Table with values of Clumns from passes. This is not exact way but I am just giving idea.
The result set should like that
ColName ColValue
Col1 Val1
Col2 Val2
Col3 Val3
I Thing You understand .
Thanks for your time.
Azhar Iqbal
September 19, 2011 at 5:37 am
Okay,
Try this one..
CREATE TYPE dbo.DynamicTbl AS TABLE
(
@TransID int,
@TableName varchar(50)
)
For Dynamic Columns, let me check....
Regards
Palash Gorai
September 19, 2011 at 6:04 am
I removed @ Sign From Code and It just work For me.
Thaks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply