Declare @dateofbirth datetime Declare @currentdatetime datetime Declare @years varchar(20) Declare @months varchar(2) Declare @days varchar(2) set @dateofbirth='1986-01-03'--birthdate yyyymmdd set @currentdatetime =getdate()--current datetime select @years=datediff(year,@dateofbirth,@currentdatetime)-- To find Years select @months=datediff(month,@dateofbirth,@currentdatetime)-(datediff(year,@dateofbirth,@currentdatetime)*12) -- To Find Months select @days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)-- To Find Days select @years +' years, ' +@months +' months, '+@days +' days' asYearMonthDay
sql server
select data between two dates in sql server with example
Declare @From Datetime declare @To datetime set @From='2019/01/01' set @To='2019/01/31' select * from tblEmployee where CreatedDate between @From and @To
Encrypt and Decrypt a Password using EncryptByPassPhrase and DecryptByPassPhrase in sql server
We can encrypt a password and can store a password as VarBinary in a column by using EncryptByPassPhrase function. Encrypted column can be decrypted by using DECRYPTBYPASSPHRASE function.
CREATE TABLE [dbo].[tbl_user]( [Useid] [int] IDENTITY(1,1) NOT NULL primary key, [Emailid] [nvarchar](500) NULL, [UserPassword] [varbinary](500) NULL ) select * from tbl_user
insert into tbl_user(Emailid,UserPassword) values('shahnawaz9286@gmail.com',ENCRYPTBYPASSPHRASE('shah','12345678'))
insert into tbl_user(Emailid,UserPassword) values('shahnawaz92867@gmail.com',ENCRYPTBYPASSPHRASE('shah','12345678@'))
insert into tbl_user(Emailid,UserPassword) values('shahnawaz92868@gmail.com',ENCRYPTBYPASSPHRASE('shah','123456789@'))
select Useid,Emailid,convert(varchar(50), DECRYPTBYPASSPHRASE('shah',UserPassword))as passw from tbl_user
Usage of IDENT_CURRENT function in stored procedure SQL Server 2012
IDENT_CURRENT function returns the last identity value generated for a specified table or view. To create st
ALTER proc [dbo].[InsertUser]
(
@UserName nvarchar(50)=null,@UserPassword nvarchar(50)=null,@FullName nvarchar(50)=null,
)
as
begin
insert into tbl_UserLogin(UserName,UserPassword,FullName)values(@UserName,@UserPassword,@FullName)
end
IF(@@ROWCOUNT>0)
Begin
Declare @Regid int
Set @Regid=IDENT_CURRENT('tbl_UserLogin')
Insert into tbl_Otp(OtpNum,RegId) values(@Otp,@Regid)
Select '1'result,'Data Save Successfully'msg
End
query with multiple tables
create proc [dbo].[GetAllQuestionByType]
@QuizTypeId int=null
as
Begin
SELECT dense_RANK() OVER (ORDER BY tbl_QuizQuestions.QuizQuestionId) AS RN,
tbl_Quizcategory.QuizcategoryName, tbl_QuizType.QuizTypeName,tbl_QuizQuestions.QuizQuestionId,tbl_Organizations.OrgId, tbl_QuizQuestions.QuizQuestion,
Tbl_QuizOption.options, tbl_QuizAnswer.AnswerValue, Tbl_QuizOption.QuizOptionId
FROM tbl_Quizcategory left JOIN
tbl_QuizType ON tbl_Quizcategory.QuizcategoryId = tbl_QuizType.QuizcategoryId left JOIN
tbl_QuizQuestions ON tbl_QuizType.QuizTypeId = tbl_QuizQuestions.QuizTypeId left JOIN
Tbl_QuizOption ON tbl_QuizQuestions.QuizQuestionId = Tbl_QuizOption.QuizQuestionId left join
tbl_QuizAnswer ON Tbl_QuizOption.QuizOptionId = tbl_QuizAnswer.QuizOptionId inner join
tbl_Organizations ON tbl_QuizQuestions.OrgId=tbl_Organizations.OrgId
where tbl_QuizQuestions.QuizTypeId=@QuizTypeId
Select count(*)totalques from tbl_QuizQuestions where QuizTypeId=@QuizTypeId
end
Building Dynamic SQL In a Stored Procedure
CREATE Proc [dbo].[usp_SearchStudent]
(@Name nvarchar(150)=null,@ParentName nvarchar(150)=null,@MobileNo nvarchar(15)=null,@City nvarchar(20)=null,
@Zone nvarchar(20)=null,
@Area nvarchar(50)=null,@Fromdate nvarchar(20)=null,@Todate nvarchar(20)=null,
@Emailid nvarchar(50)=null,@Tutgender nvarchar(10)=null,@Studentcode nvarchar(50)=null
)
As
Begin
Declare @SqlCondition varchar(500)
Declare @SqlQuery varchar(max)
Set @SqlCondition=”
if(@Name!=”or @Name!=null)
Begin
set @SqlCondition=’and RName=’+””+@Name+””
–print 1
–print @SqlCondition
–Set @SqlQuery=(‘Select
End
if(@ParentName!=”or @ParentName!=null)
Begin
set @SqlCondition=@SqlCondition+’ And ParentName=’+””+@ParentName+””
–print 2
–Set @SqlQuery=(‘Select
End
if(@MobileNo!=”or @MobileNo!=null)
Begin
set @SqlCondition=@SqlCondition+’ And MobileNo=’+””+@MobileNo+””
–print 3
–Set @SqlQuery=(‘Select
End
if(@City!=”or @City!=null)
Begin
set @SqlCondition=@SqlCondition+’ and City=’+”+@City+”
–Set @SqlQuery=(‘Select
–print @SqlCondition
End
if(@Zone!=”or @Zone!=null)
Begin
set @SqlCondition=@SqlCondition+’ And Zone=’+@Zone
–Set @SqlQuery=(‘Select
End
if(@Area!=”or @Area!=null)
Begin
set @SqlCondition=@SqlCondition+’ And Area=’+””+@Area+””
–Set @SqlQuery=(‘Select
End
if(@Fromdate!=”or @Fromdate!=null)
Begin
set @SqlCondition=@SqlCondition+’ and (convert(varchar(20),CreatedDate,103)between’+””+convert(varchar(20),@Fromdate,103)+”’and ‘+””+convert(varchar(20),@Todate,103)+”’)’
–Set @SqlQuery=(‘Select
End
–if(@Todate!=”or @Todate!=null)
–Begin
–set @SqlCondition=@SqlCondition+’ and convert(varchar(20),CreatedDate,103)=’+””+convert(varchar(20),@Todate,103)+””
—-Set @SqlQuery=(‘Select
–End
if(@Emailid!=”or @Emailid!=null)
Begin
set @SqlCondition=@SqlCondition+’ and Email=’+””+@Emailid+””
–Set @SqlQuery=(‘Select
End
if(@Tutgender!=”or @Tutgender!=null)
Begin
set @SqlCondition=@SqlCondition+’ and TutorGender=’+”+@Tutgender+”
–Set @SqlQuery=(‘Select
End
if(@Studentcode!=”or @Studentcode!=null)
Begin
set @SqlCondition=@SqlCondition+’ and RegCode=’+””+@Studentcode+””
–Set @SqlQuery=(‘Select
End
Set @SqlQuery=’SELECT tr.RegId, tr.RegCode, tr.RegFor, tr.RName, tr.ParentName, (case when Dob=”1900-01-01 00:00:00.000” then ”” else convert(varchar(20),tr.Dob,103) end)Dob,
tr.Gender,tg.SexName, tr.City,tc.CityName, tr.Zone, tz.ZoneName, TA.AreaId,TA.AreaName as Area, tr.Address, tr.Standar, tr.Standar as SatName,
tr.Email, tr.Subjects, tr.MobileNo, tr.AltMobileNo, tr.CallDateTime,
tr.TrailDateTime, tr.DoneAmt,tr.School,tr.DoneOn, tr.Remark, tr.CreatedBy,tr.CreatedDate
FROM tbl_Registration AS tr INNER JOIN
tbl_City AS tc ON tr.City = tc.CityId INNER JOIN
tbl_Zone AS tz ON tr.Zone = tz.ZoneId INNER JOIN
tbl_Gender AS tg ON tr.TutorGender = tg.SexId INNER JOIN
tbl_Area AS TA ON Tr.Area=TA.AreaId
Where RegFor=1 and Status=0’+@SqlCondition
print @SqlQuery
exec sp_sqlexec @SqlQuery
print @SqlQuery
End
–exec usp_SearchStudent”,”,”,”,”,”,”,”,”,’101′,”
–declare @sl nvarchar(Max)–tbl_Subject AS ts ON tr.Subjects = ts.SubId INNER JOIN
–declare @city int
–set @City=1
–set @sl=’Select * from tbl_Registration where City=101’
–print @sl
–exec sp_executesql @sl
–Print @sl
Digital Tutorials 