davidrobert
چهارشنبه 25 اردیبهشت 1398, 14:19 عصر
این به روش پروسجیر نویسی هستش که همزمان از چندین کوئری استفاده میکنم ولی فقط یک کوئری صدا میزنم.
USE [DBJobs]
GO
/****** Object: StoredProcedure [dbo].[Pro_Register] Script Date: 25/02/1398 14:43:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Pro_Register]
(
@R_ID bigint=null,
@R_Guild int=null,
@R_Guild_Name nvarchar(50)=null,
@R_Nationality int=null,
@R_Sex int=null,
@R_Name nvarchar(50)=null,
@R_Last nvarchar(50)=null,
@R_Father nvarchar(50)=null,
@R_Date_Of_Birth varchar(10)=null,
@R_Place_Of_Brith nvarchar(50)=null,
@R_National_Code_Or_Passports nvarchar(50)=null,
@R_ID_Number varchar(10)=null,
@R_Serial_ID varchar(6)=null,
@R_Issued nvarchar(50)=null,
@R_Tel varchar(10)=null,
@R_Emial nvarchar(250)=null,
@R_Website nvarchar(250)=null,
@R_Logo nvarchar(250)=null,
@R_Sign nvarchar(250)=null,
@R_C_ID int=null,
@R_latitude nvarchar(50)=null,
@R_longitude nvarchar(50)=null,
@R_Full_Address nvarchar(250)=null,
@R_SMS_Address nvarchar(100)=null,
@R_Zip_Code varchar(10)=null,
@R_ISP nvarchar(250)=null,
@R_IP_Server nvarchar(50)=null,
@R_IP_Local nvarchar(50)=null,
@R_Insert_Date varchar(10)=null,
@R_Insert_Time time=null,
@C_Titles nvarchar(max)=null,
@S_Title nvarchar(6)=null,
@StatementType nvarchar(150)=''
)
as begin
--Declare @id int;
declare @date date;
declare @time time;
--Select @id = isnull(max(CBF_ID),0) + 1 from Tbl_Cart_Bank_Forosh;
select @date = (SELECT CONVERT(date, getdate()));
select @time=(SELECT CONVERT(VARCHAR(8),GETDATE(),108));
if @StatementType='Select'
SELECT R_ID, R_Guild, R_Guild_Name, R_Nationality, R_Sex, R_Name, R_Last, R_Father,
(SELECT dbo.MakeCompleteShmsiDate(Tbl_Register.R_Date_Of_B irth, '/') AS Expr1) AS R_Date_Of_Birth, R_Place_Of_Brith, R_National_Code_Or_Passports, R_ID_Number, R_Serial_ID, R_Issued, R_Tel, R_Emial,
R_Website, R_Logo, R_Sign, R_C_ID, R_latitude, R_longitude, R_Full_Address, R_SMS_Address, R_Zip_Code, R_ISP, R_IP_Server, R_IP_Local,
(SELECT dbo.MakeCompleteShmsiDate(Tbl_Register.R_Insert_Da te, '/') AS Expr1) AS R_Insert_Date, R_Insert_Time,
(SELECT dbo.MakeCompleteShmsiDate(Tbl_Register.R_Update_Da te, '/') AS Expr1) AS R_Update_Date, R_Update_Time, R_Update_Count
FROM Tbl_Register
ORDER BY R_ID DESC
if @StatementType='Insert'
INSERT INTO Tbl_Register
(R_Guild, R_Guild_Name, R_Nationality, R_Sex, R_Name, R_Last, R_Father, R_Date_Of_Birth, R_Place_Of_Brith, R_National_Code_Or_Passports, R_ID_Number, R_Serial_ID, R_Issued, R_Tel, R_Emial, R_Website, R_Logo, R_Sign, R_C_ID, R_latitude, R_longitude, R_Full_Address, R_SMS_Address, R_Zip_Code, R_ISP, R_IP_Server, R_IP_Local, R_Insert_Date, R_Insert_Time)
VALUES (CASE WHEN @R_Guild is null THEN 0 ELSE @R_Guild END ,@R_Guild_Name,CASE WHEN @R_Nationality is null THEN 0 ELSE @R_Nationality END ,CASE WHEN @R_Sex is null THEN 0 ELSE @R_Sex END ,@R_Name,@R_Last,@R_Father,CASE WHEN @R_Date_Of_Birth is null THEN null ELSE (select dbo.ShamsiToMiladi( @R_Date_Of_Birth)) end,@R_Place_Of_Brith,@R_National_Code_Or_Passport s,@R_ID_Number,@R_Serial_ID,@R_Issued,@R_Tel,@R_Em ial,@R_Website,@R_Logo,@R_Sign,@R_C_ID,@R_latitude ,@R_longitude,@R_Full_Address,@R_SMS_Address,@R_Zi p_Code,@R_ISP,@R_IP_Server,@R_IP_Local,@date,@time )
if @StatementType='Fetch_ID'
SELECT Tbl_Register.R_ID, Tbl_Register.R_National_Code_Or_Passports, Tbl_Sex.S_Title, Tbl_Register.R_Name, Tbl_Register.R_Last
FROM Tbl_Register INNER JOIN
Tbl_Sex ON Tbl_Register.R_Sex = Tbl_Sex.S_ID
WHERE (Tbl_Register.R_National_Code_Or_Passports = @R_National_Code_Or_Passports)
ORDER BY Tbl_Register.R_ID DESC
if @StatementType='FeedBack_Register'
SELECT Tbl_Register.R_ID, Tbl_Category_Job.CJ_Title, Tbl_Jobs.J_Title, Tbl_Register.R_Guild_Name, Tbl_Countries.C_Titles, Tbl_Sex.S_Title, Tbl_Register.R_Name, Tbl_Register.R_Last, Tbl_Register.R_Father,
(SELECT dbo.MakeCompleteShmsiDate(Tbl_Register.R_Date_Of_B irth, '/') AS Expr1) AS R_Date_Of_Birth, Tbl_Register.R_Place_Of_Brith, Tbl_Register.R_National_Code_Or_Passports, Tbl_Register.R_ID_Number,
Tbl_Register.R_Serial_ID, Tbl_Register.R_Issued, Tbl_Register.R_Tel, Tbl_Mobile.M_Mobile, Tbl_Register.R_Emial, Tbl_Register.R_Website, Tbl_Register.R_Logo, Tbl_Register.R_Sign, Tbl_Province.P_Title, Tbl_City.C_Title,
Tbl_Register.R_latitude, Tbl_Register.R_longitude, Tbl_Register.R_Full_Address, Tbl_Register.R_SMS_Address, Tbl_Register.R_Zip_Code, Tbl_Register.R_ISP, Tbl_Register.R_IP_Server, Tbl_Register.R_IP_Local,
(SELECT dbo.MakeCompleteShmsiDate(Tbl_Register.R_Insert_Da te, '/') AS Expr1) AS R_Insert_Date, Tbl_Register.R_Insert_Time,
(SELECT dbo.MakeCompleteShmsiDate(Tbl_Register.R_Update_Da te, '/') AS Expr1) AS R_Update_Date, Tbl_Register.R_Update_Time, Tbl_Register.R_Update_Count
FROM Tbl_Register INNER JOIN
Tbl_Jobs ON Tbl_Register.R_Guild = Tbl_Jobs.J_ID INNER JOIN
Tbl_Category_Job ON Tbl_Jobs.J_CJ_ID = Tbl_Category_Job.CJ_ID INNER JOIN
Tbl_Countries ON Tbl_Register.R_Nationality = Tbl_Countries.C_ID INNER JOIN
Tbl_Sex ON Tbl_Register.R_Sex = Tbl_Sex.S_ID INNER JOIN
Tbl_City ON Tbl_Register.R_C_ID = Tbl_City.C_ID INNER JOIN
Tbl_Province ON Tbl_City.C_P_ID = Tbl_Province.P_ID INNER JOIN
Tbl_Mobile ON Tbl_Register.R_ID = Tbl_Mobile.M_R_ID
WHERE (Tbl_Register.R_National_Code_Or_Passports = @R_National_Code_Or_Passports) AND (Tbl_Register.R_Name = @R_Name) AND (Tbl_Register.R_Last = @R_Last) AND (Tbl_Register.R_Father = @R_Father)
ORDER BY Tbl_Register.R_ID DESC
if @StatementType='Fetch_Info'
SELECT Tbl_Register.R_ID, Tbl_Register.R_National_Code_Or_Passports, Tbl_Sex.S_Title, Tbl_Register.R_Name, Tbl_Register.R_Last, Tbl_Register.R_Sign, Tbl_Mobile.M_Mobile, Tbl_Register.R_Emial
FROM Tbl_Register INNER JOIN
Tbl_Sex ON Tbl_Register.R_Sex = Tbl_Sex.S_ID INNER JOIN
Tbl_Mobile ON Tbl_Register.R_ID = Tbl_Mobile.M_R_ID
WHERE (Tbl_Register.R_National_Code_Or_Passports = @R_National_Code_Or_Passports)
ORDER BY Tbl_Register.R_ID DESC
end
و برای روش شما امدم دستور تاریخ و زمان جداگانه یک بار دادم. به این صورت
--Declare @id int;
declare @date date;
declare @time time;
--Select @id = isnull(max(CBF_ID),0) + 1 from Tbl_Cart_Bank_Forosh;
select @date = (SELECT CONVERT(date, getdate()));
select @time=(SELECT CONVERT(VARCHAR(8),GETDATE(),108));
و به این صورت برای ذخیره استفاده کردم .
if @StatementType='Insert'
INSERT INTO Tbl_Register
(R_Guild, R_Guild_Name, R_Nationality, R_Sex, R_Name, R_Last, R_Father, R_Date_Of_Birth, R_Place_Of_Brith, R_National_Code_Or_Passports, R_ID_Number, R_Serial_ID, R_Issued, R_Tel, R_Emial, R_Website, R_Logo, R_Sign, R_C_ID, R_latitude, R_longitude, R_Full_Address, R_SMS_Address, R_Zip_Code, R_ISP, R_IP_Server, R_IP_Local, R_Insert_Date, R_Insert_Time)
VALUES (CASE WHEN @R_Guild is null THEN 0 ELSE @R_Guild END ,@R_Guild_Name,CASE WHEN @R_Nationality is null THEN 0 ELSE @R_Nationality END ,CASE WHEN @R_Sex is null THEN 0 ELSE @R_Sex END ,@R_Name,@R_Last,@R_Father,CASE WHEN @R_Date_Of_Birth is null THEN null ELSE (select dbo.ShamsiToMiladi( @R_Date_Of_Birth)) end,@R_Place_Of_Brith,@R_National_Code_Or_Passport s,@R_ID_Number,@R_Serial_ID,@R_Issued,@R_Tel,@R_Em ial,@R_Website,@R_Logo,@R_Sign,@R_C_ID,@R_latitude ,@R_longitude,@R_Full_Address,@R_SMS_Address,@R_Zi p_Code,@R_ISP,@R_IP_Server,@R_IP_Local,@date,@time )
مقدار @date برای تاریخ جاری سیستم که پرامتر صدا زدم و تاریخ قرار دادم و برای زمان @time که بار نوشتم و تعداد دفعات زیاد صدا زدم. و برای تاریخ زمان ویرایش که هر بار ویرایش میشه تاریخ و زمان و تعداد دفعات اش روجداگانه توسط تریگیر برای اون ستون میگیرم.
به این لینک سر بزنید بابت تبدیل های تاریخ میلادی و شمسی کمک میکنه.
http://barnamenevis.org/showthread.php?554375-%D8%AA%D8%A7%D8%B1%DB%8C%D8%AE-%D8%B4%D9%85%D8%B3%DB%8C-%D8%AF%D8%B1-WinForm-Application
این هم بگم تاریخ میلادی ذخیره میکنم دیتابیس و تاریخ تبدیل میکنم.
vBulletin® v4.2.5, Copyright ©2000-1403, Jelsoft Enterprises Ltd.