March 26, 2011 at 11:52 pm
I have a table in which there are many COLUMNS based upon the selection in the front end i would like a return the 3rd columen named value upon the selection yes or no so can you please adivce with the stored procedure how to write upon which i can try as i am a beginner in TSQL..
CREATE TABLE [dbo].[test](
[Group] [nvarchar](255) NULL,
[Value] [nvarchar](255) NULL,
[Prod] [nvarchar](255) NULL,
[Ph] [nvarchar](255) NULL,
[FPh] [nvarchar](255) NULL,
[FD] [nvarchar](255) NULL,
[FT] [nvarchar](255) NULL,
[1M] [nvarchar](255) NULL,
[3M] [nvarchar](255) NULL,
[7M] [nvarchar](255) NULL,
[15M] [nvarchar](255) NULL,
[FTV] [nvarchar](255) NULL,
[D13] [nvarchar](255) NULL,
[F10] [nvarchar](255) NULL,
[F15] [nvarchar](255) NULL,
[F20 ] [nvarchar](255) NULL,
[F21] [nvarchar](255) NULL,
[F25] [nvarchar](255) NULL,
[F30] [nvarchar](255) NULL,
[F35] [nvarchar](255) NULL,
[F50] [nvarchar](255) NULL,
[F150] [nvarchar](255) NULL
) ON [PRIMARY]
GO
Insert into test values ('PRE','BDOT','XXXXX','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A','N/A')
Insert into test values ('OOF','BD1T','XXEDE','N','N','N','N','N','N','N','N','N','N/A','N','N','N','N','N','N','N','N','N')
Insert into test values ('OOF','BD2T','CEVXX','Y','N','N','N','N','N','N','N','N','N/A','N','N','N','N','N','N','N','N','N')
Insert into test values ('DSD','BD3T','VXEEX','Y','Y','Y','Y','Y','Y','Y','Y','Y','USF','N','Y','N','N','Y','N','Y','Y','Y')
Insert into test values ('OSF','BD4T','WDEWX','Y','Y','Y','Y','N','N','N','N','N','USF','N','Y','N','N','Y','N','Y','Y','Y')
Insert into test values ('OSD','BD5T','CVXDX','Y','Y','Y','Y','N','N','N','N','N','USF','N','Y','N','N','Y','N','Y','Y','N')
Insert into test values ('SOF','BD6T','CVFDX','Y','Y','Y','Y','N','N','N','N','N','VS2','N','Y','Y','N','N','N','N','Y','N')
Insert into test values ('DSD','BD7T','VXEEX','Y','Y','Y','Y','N','N','N','N','N','VS1','Y','N','N','Y','N','Y','N','N','N')
Insert into test values ('OSF','BD8T','WDEWX','N','Y','Y','Y','N','N','N','N','N','USF','N','Y','N','N','Y','N','Y','Y','Y')
Insert into test values ('OSD','BD9T','CVXDX','N','Y','Y','Y','Y','Y','Y','Y','Y','USF','N','Y','N','N','Y','N','Y','Y','N')
Insert into test values ('SOF','BD10T','CVFX','Y','Y','Y','Y','N','N','N','N','N','NULL','N','Y','Y','N','N','N','N','Y','N')
When i select the the options Y or N for the columns i should return the column "Value" ..So please adivce how to write a stored procedure to return BD1T when it has selected the criteria for all columns....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 27, 2011 at 8:41 am
I didn't quite follow your exact requirement, so I am partly guessing. Is it something like this that you are looking for?
create procedure dbo.BDValues
@colName varchar(31),
@colValue varchar(31)
as
select
[group],
[value],
[prod]
from
test
where
(@colName = 'Ph' and Ph = @colValue)
or
(@colName = 'FPh' and FPh = @colValue)
-- etc
And then use it like
exec dbo.BDValues 'FPh','Y';
If this is not what you are looking for, can you post an example input and corresponding output that you expect from the stored proc?
March 27, 2011 at 1:57 pm
I would recommend to unpivot your table. While doing so, you should adjust the column size and/or data type (I don't think NVARCHAR(255) is an appropriate data type). Maybe even a simple tinyint column with a constraint would do it...
March 27, 2011 at 11:19 pm
Thanks Lutz but can you explain in more what would be the adv and how it works if i unpivot it..
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 28, 2011 at 9:28 am
Please provide some explicit examples of output you expect given certain inputs. This could be a case for either front-end work or dynamic sql.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply