July 22, 2014 at 2:11 am
Hi All,
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
CLASS Days
A sun
A Mon
A wed
How can I write this? Any Idea?
Thanks
Abhas.
July 22, 2014 at 3:01 am
Crude way, but 8 input parameters, 7 IF statements
create proc InsertClassDays (@class char(1), @sun bit = 0, @mon bit = 0, @tue bit = 0, @wed bit = 0, @thur bit = 0, @fri bit = 0, @sat bit = 0)
AS
declare @ClassDays TABLE (Class char(1), Days char(4))
BEGIN
IF @sun = 1
BEGIN
INSERT INTO @ClassDays VALUES (@class, 'Sun')
END
IF @Mon = 1
BEGIN
INSERT INTO @ClassDays VALUES (@class, 'Mon')
END
IF @tue = 1
BEGIN
INSERT INTO @ClassDays VALUES (@class, 'Tue')
END
IF @Wed = 1
BEGIN
INSERT INTO @ClassDays VALUES (@class, 'Wed')
END
IF @Thur = 1
BEGIN
INSERT INTO @ClassDays VALUES (@class, 'Thur')
END
IF @fri = 1
BEGIN
INSERT INTO @ClassDays VALUES (@class, 'Fri')
END
IF @Sat = 1
BEGIN
INSERT INTO @ClassDays VALUES (@class, 'Sat')
END
END
SELECT * FROM @ClassDays
GO
exec InsertClassDays @class = 'A', @sun = 1, @mon = 1, @wed = 1
GO
July 22, 2014 at 3:17 am
You can pass selected values as comma separated string. With below method, you can insert only selected values to table.
DECLARE @weekdaySelected NVARCHAR(50)
DECLARE @tableT TABLE
( ClassName NVARCHAR(10),
WeekdaySelected NVARCHAR(50))
SET @weekdaySelected = 'Monday,Tuesday, Wednesday'
INSERT INTO @tableT
( ClassName, WeekdaySelected )
SELECT 'ClassA' AS ClassName,
Split.a.value('.', 'VARCHAR(100)') AS WeekdaySelected
FROM
(
SELECT CAST ('<M>' + REPLACE(@weekdaySelected, ',', '</M><M>') + '</M>' AS XML) AS CVS
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
SELECT * FROM @tableT
Thanks
July 22, 2014 at 8:46 am
Following Anthony's 8 parameters idea, but replacing the 7 IFs with a single WHERE. 🙂
DECLARE
@class char(1) = 'A',
@sun bit = 1,
@mon bit = 1,
@tue bit = 0,
@wed bit = 1,
@thu bit = 0,
@fri bit = 0,
@sat bit = 0
SELECT @class AS Class,
Name AS Days
FROM (VALUES(@sun, 'SUN'),
(@mon, 'MON'),
(@tue, 'TUE'),
(@wed, 'WED'),
(@thu, 'THU'),
(@fri, 'FRI'),
(@sat, 'SAT')) DaysOfWeek(selected, Name)
WHERE selected = 1
July 22, 2014 at 8:50 am
Hardy21 (7/22/2014)
You can pass selected values as comma separated string. With below method, you can insert only selected values to table.
DECLARE @weekdaySelected NVARCHAR(50)DECLARE @tableT TABLE
( ClassName NVARCHAR(10),
WeekdaySelected NVARCHAR(50))
SET @weekdaySelected = 'Monday,Tuesday, Wednesday'
INSERT INTO @tableT
( ClassName, WeekdaySelected )
SELECT 'ClassA' AS ClassName,
Split.a.value('.', 'VARCHAR(100)') AS WeekdaySelected
FROM
(
SELECT CAST ('<M>' + REPLACE(@weekdaySelected, ',', '</M><M>') + '</M>' AS XML) AS CVS
) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
SELECT * FROM @tableT
I suggest you to change the XML splitter for a faster function as demonstrated in the following article: http://www.sqlservercentral.com/articles/Tally+Table/72993/
July 22, 2014 at 9:11 am
Thank you anthony and Hardy.
Thank you very much for both of you for quick reply.
Both approaches are working fine.
Thanks
Abhas.
July 22, 2014 at 10:27 pm
abhas (7/22/2014)
Thank you anthony and Hardy.Thank you very much for both of you for quick reply.
Both approaches are working fine.
Thanks
Abhas.
Great, good to know 😛
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply