mjt10063
دوشنبه 14 مهر 1393, 17:50 عصر
با سلام دوستان
مشکلم تو قسمت گزارش گیری
یا جستجوی پبشرفته
می خواستم کاربر مثلا نمایش اطلاعات رو خودش انتخاب کنه حالا صعودی یا نزولی
و یا یه سری دیگه از مشخصات مربوط به سایت
مشکل اینجاست که من با کلی IF توی sql تونستم مثلا تاریخ اگر وارد شد
و یا اگر نزولی بود و یا غیره رو در بیارم
حالا خواستم بدونم اصلا این روش اصولی هست یا نه
و اگر نیست اساتید چه روشی رو پیشنهاد میدن
با تشکر
اینم کد جستجوی من
ALTER PROCEDURE Select_Jarime
(
@keshti int,
@sh_keshti int,
@tarikh_az nvarchar(50),
@tarikh_ta nvarchar(50),
@jamjarim_out Float output,
@jamekht_out Float output
)
AS
IF (@sh_keshti = '' AND @tarikh_az = '' AND @tarikh_ta = '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime;
set @jamjarim_out = (Select SUM(jarime) from t_jarime);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true';
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true');
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true');
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false';
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false');
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false');
END
END
ELSE IF (@sh_keshti <> '' AND @tarikh_az = '' AND @tarikh_ta = '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime where sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true' AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true' AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true' AND sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false' AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false' AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false' AND sh_keshti = @sh_keshti);
END
END
ELSE IF (@sh_keshti = '' AND @tarikh_az <> '' AND @tarikh_ta <> '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta);
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta);
END
END
ELSE IF (@sh_keshti <> '' AND @tarikh_az <> '' AND @tarikh_ta <> '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
END
END
RETURN
مشکلم تو قسمت گزارش گیری
یا جستجوی پبشرفته
می خواستم کاربر مثلا نمایش اطلاعات رو خودش انتخاب کنه حالا صعودی یا نزولی
و یا یه سری دیگه از مشخصات مربوط به سایت
مشکل اینجاست که من با کلی IF توی sql تونستم مثلا تاریخ اگر وارد شد
و یا اگر نزولی بود و یا غیره رو در بیارم
حالا خواستم بدونم اصلا این روش اصولی هست یا نه
و اگر نیست اساتید چه روشی رو پیشنهاد میدن
با تشکر
اینم کد جستجوی من
ALTER PROCEDURE Select_Jarime
(
@keshti int,
@sh_keshti int,
@tarikh_az nvarchar(50),
@tarikh_ta nvarchar(50),
@jamjarim_out Float output,
@jamekht_out Float output
)
AS
IF (@sh_keshti = '' AND @tarikh_az = '' AND @tarikh_ta = '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime;
set @jamjarim_out = (Select SUM(jarime) from t_jarime);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true';
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true');
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true');
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false';
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false');
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false');
END
END
ELSE IF (@sh_keshti <> '' AND @tarikh_az = '' AND @tarikh_ta = '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime where sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true' AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true' AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true' AND sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false' AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false' AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false' AND sh_keshti = @sh_keshti);
END
END
ELSE IF (@sh_keshti = '' AND @tarikh_az <> '' AND @tarikh_ta <> '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta);
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta);
END
END
ELSE IF (@sh_keshti <> '' AND @tarikh_az <> '' AND @tarikh_ta <> '')
BEGIN
IF (@keshti = 0)
BEGIN
Select * from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 1)
BEGIN
Select * from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'true' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
END
ELSE IF (@keshti = 2)
BEGIN
Select * from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti;
set @jamjarim_out = (Select SUM(jarime) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
set @jamekht_out = (Select SUM(ekhtelaf) from t_jarime where status = 'false' AND tarikh Between @tarikh_az AND @tarikh_ta AND sh_keshti = @sh_keshti);
END
END
RETURN