Solved

Help me combine two stored procedures

Posted on 2011-02-28
13
245 Views
Last Modified: 2012-05-11
I'm somewhat new to writing stored procedures. I have two stored procedures, and both work correctly, but I want to insert the functionality from one of the stored procedures into the other one, and I'm not sure how to do it correctly.

The first stored procedure, called proc_DaylightSaving, accepts a date/time, looks up the BeginDt and EndDt of daylight saving time for that year, and returns '2' if that date is outside of daylight saving time, and returns '3' if the date is during daylight saving time.

I want to insert the functionality of 'proc_DaylightSaving' into a second stored procedure called 'proc_Login_Authenticate', which looks up and returns one row of data, including the date/time of the current user's last login. Currently, proc_Login_Authenticate uses DateAdd() to subtract 2 hours from the last login date/time because our server is in Saskatchewan, Canada, and we want to show the time in Pacific time. But soon, daylight saving time will start and we will need to subtract 3 hours instead of 2.

I'm pasting both stored procedures here. Can anyone show me how to put the functionality of proc_DaylightSaving into proc_Login_Authenticate? As I said, both stored procedures currently work correctly. I just need help combining them.

Thanks

USE [db_beta]
GO
/****** Object:  StoredProcedure [dbo].[proc_DaylightSaving]    Script Date: 02/25/2011 16:54:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_DaylightSaving]
	(
	@MyDt SmallDateTime
	)
AS
	Declare @NoOfHours AS int
	Declare @StartDt AS SmallDateTime
	Declare @EndDt AS SmallDateTime
	Declare @MyYear as Integer
	
	SET @MyYear = DatePart("yyyy",@MyDt)
	SELECT @StartDt = BeginDt FROM dbo.tlstDBDaylightSavingDates WHERE (YearNo = @MyYear)
	SELECT @EndDt = EndDt FROM dbo.tlstDBDaylightSavingDates WHERE (YearNo = @MyYear)
	
	IF @MyDt >= @StartDt And @MyDt < @EndDt
		BEGIN 
			Set @NoOfHours = 3
		END
	ELSE
		BEGIN 
			Set @NoOfHours = 2
		END
	RETURN @NoOfHours

Open in new window

USE [db_beta]
GO
/****** Object:  StoredProcedure [dbo].[proc_Login_Authenticate]    Script Date: 02/28/2011 12:26:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Login_Authenticate]  
 (  
   @Username varchar(50),  
   @Password varchar(50)  
 )  
 AS  
 DECLARE @LoginID as Integer
 SET @LoginID = -9999
 SELECT @LoginID=LoginID 
 FROM tblLogins
 WHERE RTRIM(Username) = RTRIM(@Username) 
 DECLARE @X AS INT
 SET @X=0
 
 SELECT TOP 1 
		X.AccessLevel,
		X.LoginID, 
		X.PersonID, 
		X.Username,
		X.FName,
		X.LName, 
		X.EmailID,
		X.EmailInterval,
		CASE 
			WHEN Y.LastLogin IS NULL THEN 'Welcome - Initial Login'
			ELSE 'Last Login - ' + CAST(DateAdd(hh,-2,Y.LastLogin) AS VARCHAR)
		END AS LastLogin
 FROM tblLogins X
 LEFT JOIN ElanBiz5.LoginLogs Y ON X.LoginID = Y.LoginID
 WHERE (X.Username) = RTRIM(@Username) AND 
 RTRIM(X.Password) = RTRIM(@Password  )
 ORDER BY Y.LastLogin DESC
 
 SELECT @X = COUNT(1)
 FROM tblLogins X
 
 WHERE RTRIM(X.Username) = RTRIM(@Username) AND 
 RTRIM(X.Password) = RTRIM(@Password)
 GROUP BY X.Username

 
 IF @LoginID <> -9999
 BEGIN
	IF @X = 1
	BEGIN
		INSERT INTO ElanBiz5.LoginLogs VALUES(@LoginID,@Username,GETDATE(),'SUCCESS')
	END
	ELSE
	BEGIN
		INSERT INTO ElanBiz5.LoginLogs VALUES(@LoginID,@Username,GETDATE(),'LOGIN FAILED')
	END 
 END
 ELSE
 BEGIN
    INSERT INTO ElanBiz5.LoginLogs VALUES(@LoginID,@Username,GETDATE(),'INVALID USERNAME')
 END

Open in new window

0
Comment
Question by:mclarkdatasimplicity
13 Comments
 
LVL 26

Expert Comment

by:tigin44
Comment Utility
alter the first procedure as

RETURN @NoOfHours    to      SELECT @NoOfHours

THEN simply call the first procedure from the second procedure... ie.

declare @NoOfHours int;
EXEC @NoOfHours = [dbo].[proc_DaylightSaving] someValue;

or change the first procedure to a function and calling it may be an other solution
0
 
LVL 6

Expert Comment

by:LCSandman8301
Comment Utility
you can do this by calling the procedure and storing the return as below
declare @timezoneoffset int

exec @timezoneoffset = proc_DaylightSaving @mydt = getdate()

select @timezoneoffset

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
just change the login procedure to use the daylight procedure like this...
USE [db_beta]
GO
/****** Object:  StoredProcedure [dbo].[proc_Login_Authenticate]    Script Date: 02/28/2011 12:26:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Login_Authenticate]  
 (  
   @Username varchar(50),  
   @Password varchar(50)  
 )  
 AS  
 DECLARE @LoginID as Integer
 SET @LoginID = -9999
 SELECT @LoginID=LoginID 
 FROM tblLogins
 WHERE RTRIM(Username) = RTRIM(@Username) 
 DECLARE @X AS INT
 SET @X=0
 
 
 Declare @accesslevel int,@personid int,@fname sysname,@lname sysname
     ,@emailid sysname,@emailint int,@lastlogindt smalldatetime,@rc int,@lastlogin varchar(30)
     
 SELECT TOP 1 
		@accesslevel=X.AccessLevel,
		 
		@personid=X.PersonID, 
		
		@fname=X.FName,
		@lname=X.LName, 
		@emailid=X.EmailID,
		@emailint=X.EmailInterval,
		@lastlogindt=y.lastlogin
		 
 FROM tblLogins X
 LEFT JOIN ElanBiz5.LoginLogs Y ON X.LoginID = Y.LoginID
 WHERE (X.Username) = RTRIM(@Username) AND 
 RTRIM(X.Password) = RTRIM(@Password  )
 ORDER BY Y.LastLogin DESC
 
if @lastlogindt is null 
 begin
  set @lastlogin= 'Welcome - Initial Login'
 end
 Else
 Begin   
     Exec @rc = [dbo].[proc_DaylightSaving] @mydt=@lastlogin
     set @lastlogin='Last Login - ' + CAST(DateAdd(hh,@rc*(-1),Y.LastLogin) AS VARCHAR)	
 end
 
 Select @accesslevel as AccessLevel,
		@loginid as LoginID, 
		@personid as PersonID, 
		@username as Username,
		@fname as FName,
		@lname as LName, 
		@emailid as EmailID,
		@emailint as EmailInterval,
		@lastlogin as lastlogin
		  
 SELECT @X = COUNT(1)
 FROM tblLogins X
 
 WHERE RTRIM(X.Username) = RTRIM(@Username) AND 
 RTRIM(X.Password) = RTRIM(@Password)
 GROUP BY X.Username

 Insert into ElanBiz5.LoginLogs
   Select @LoginID,@username,GETDATE()
        ,Case when @LoginID=-9999 
              then 'INVALID USERNAME'
              when @X=1
              Then 'SUCCESS'
              Else 'LOGIN FAILED'
              End
              
 Return
 GO

Open in new window

0
 

Author Comment

by:mclarkdatasimplicity
Comment Utility
Hi tigin44 and LCSandman8301,

Thank you both for the advice. I understand what you're suggesting, but since the SELECT statement inside proc_LoginAuthenticate is what actually gives me the date that I need to evaluate (Y.LastLogin), I'm still not quite sure how to both pull that date and evaluate it. Can I insert your code into my CASE statement somehow? Can you show me please?

Thanks,
0
 

Author Comment

by:mclarkdatasimplicity
Comment Utility
Ooops... just saw Lowfatspread's suggestion. I'll try that now. Thanks!
0
 

Author Comment

by:mclarkdatasimplicity
Comment Utility
Hi Lowfatspread,

It looks like we're really close, but i'm getting an error:

Msg 4104, Level 16, State 1, Procedure proc_Login_Authenticate_Test, Line 43
The multi-part identifier "Y.LastLogin" could not be bound.

Any ideas?

Thanks,
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
sorry left in a Y alias from the original select...

the procedure should look like this....

you shouldn't have rtrim(xxx)  conditions around the columns/expressions since trailing spaces are equivalent anyway...
and by using the function you are limiting the dbms's ability to use an index properly...

the code was overly verbose with mulitple unnecessary repeat accesses to the tables..

always try to minimise database access requirements...

... having considered your daylight saving procedure that could also be simplified... but there is a "small" problem with this processing of course... when the clocks go back you WILL report an incorrect time situation in 50% of the occasions for the repeated hour...  (do you also not have to worry about "daylight" saving time periods in both jurisditctions being out of sync?
ie UK doesn't switch at same time as US always)

good luck

is that important?
USE [db_beta]
GO
/****** Object:  StoredProcedure [dbo].[proc_Login_Authenticate]    Script Date: 02/28/2011 12:26:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[proc_Login_Authenticate]  
 (  
   @Username varchar(50),  
   @Password varchar(50)  
 )  
 AS  
 DECLARE @LoginID as Integer,@x integer
 SElect @LoginID = -9999,@x=0
 
 SELECT @LoginID=LoginID 
     , @X = case when [password]=@password then 1 else 0 end
   FROM tblLogins
  WHERE Username = @Username 
  
  Declare @accesslevel int,@personid int,@fname sysname,@lname sysname
     ,@emailid sysname,@emailint int,@lastlogindt smalldatetime,@rc int,@lastlogin varchar(30)
     
 SELECT TOP 1 
		@accesslevel=X.AccessLevel,
		 
		@personid=X.PersonID, 
		
		@fname=X.FName,
		@lname=X.LName, 
		@emailid=X.EmailID,
		@emailint=X.EmailInterval,
		@lastlogindt=y.lastlogin
		 
 FROM tblLogins X
 LEFT JOIN ElanBiz5.LoginLogs Y 
   ON X.LoginID = Y.LoginID
 WHERE X.Username = @Username
   AND X.[Password] = @Password
 ORDER BY Y.LastLogin DESC
 
if @lastlogindt is null 
 begin
  set @lastlogin= 'Welcome - Initial Login'
 end
 Else
 Begin   
     Exec @rc = [dbo].[proc_DaylightSaving] @mydt=@lastlogin
     set @lastlogin='Last Login - ' + CAST(DateAdd(hh,@rc*(-1),@LastLogin) AS VARCHAR)	
 end
 
 Select @accesslevel as AccessLevel,
		@loginid as LoginID, 
		@personid as PersonID, 
		@username as Username,
		@fname as FName,
		@lname as LName, 
		@emailid as EmailID,
		@emailint as EmailInterval,
		@lastlogin as lastlogin
		  
 
 Insert into ElanBiz5.LoginLogs
   Select @LoginID,@username,GETDATE()
        ,Case when @LoginID=-9999 
              then 'INVALID USERNAME'
              when @X=1
              Then 'SUCCESS'
              Else 'LOGIN FAILED'
              End
              
 Return
 GO

Open in new window

0
 

Author Comment

by:mclarkdatasimplicity
Comment Utility
Hi Lowfatspread,

Still very close. The stored procedure runs now, but always thinks that the LoginID = -9999. When it runs the "SELECT @LoginID=LoginID ..." statement for the Username I'm passing in, it should get a LoginID of 355 but it still returns -9999. Am I doing something wrong?

Thanks!
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
that doesn't seem to be what happens for me...

can you show me how you are executing the statement..?

my results and a slightly amended procedure...


/*
 drop table tbllogins
 drop table ElanBiz5.LoginLogs
 go
 drop schema elanbiz5
 go
 create schema elanbiz5
 go
  create table tbllogins (loginid int,username char(30) not null,password char(8) not null
   ,accesslevel int ,fname varchar(30),lname varchar(30),emailid varchar(30),personid int,
   emailinterval int ,primary key (username))
   go
   insert into tbllogins (loginid,username,password) values (0,'x','x')
   go
   create table ElanBiz5.LoginLogs (loginid int,username char(30),lastlogin smalldatetime,status varchar(30))
   go
   */
   
   exec [dbo].[proc_Login_Authenticate] 'x','y'
   exec [dbo].[proc_Login_Authenticate] 'x','X'
   exec [dbo].[proc_Login_Authenticate] 'z','y'
   
   select * from ElanBiz5.LoginLogs
   order by lastlogin desc
   

results
AccessLevel LoginID     PersonID    Username                                           FName                                                                                                                            LName                                                                                                                            EmailID                                                                                                                          EmailInterval lastlogin
----------- ----------- ----------- -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------------------
NULL        0           NULL        x                                                  NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL          Welcome - Initial Login

AccessLevel LoginID     PersonID    Username                                           FName                                                                                                                            LName                                                                                                                            EmailID                                                                                                                          EmailInterval lastlogin
----------- ----------- ----------- -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------------------
NULL        0           NULL        x                                                  NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL          NULL

AccessLevel LoginID     PersonID    Username                                           FName                                                                                                                            LName                                                                                                                            EmailID                                                                                                                          EmailInterval lastlogin
----------- ----------- ----------- -------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------------------
NULL        -9999       NULL        z                                                  NULL                                                                                                                             NULL                                                                                                                             NULL                                                                                                                             NULL          Welcome - Initial Login

loginid     username                       lastlogin               status
----------- ------------------------------ ----------------------- ------------------------------
0           x                              2011-03-02 17:23:00     LOGIN FAILED
0           x                              2011-03-02 17:23:00     SUCCESS
-9999       z                              2011-03-02 17:23:00     INVALID USERNAME
USE [achTest]
GO

/****** Object:  StoredProcedure [dbo].[proc_Login_Authenticate]    Script Date: 02/28/2011 12:26:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[proc_Login_Authenticate]  
 (  
   @Username varchar(50),  
   @Password varchar(50)  
 )  
 AS  
 set nocount on
 DECLARE @LoginID as Integer,@x integer
 SElect @LoginID = -9999,@x=0
 
 SELECT @LoginID=LoginID 
     , @X = case when [password]=@password then 1 else 0 end
   FROM tblLogins
  WHERE Username = @Username 
  
  Declare @accesslevel int,@personid int,@fname sysname,@lname sysname
     ,@emailid sysname,@emailint int,@lastlogindt smalldatetime,@rc int,@lastlogin varchar(30)
 if @x=1
 begin    
 SELECT TOP 1 
		@accesslevel=X.AccessLevel,
		 
		@personid=X.PersonID, 
		
		@fname=X.FName,
		@lname=X.LName, 
		@emailid=X.EmailID,
		@emailint=X.EmailInterval,
		@lastlogindt=y.lastlogin
		 
 FROM tblLogins X
 LEFT JOIN ElanBiz5.LoginLogs Y 
   ON X.LoginID = Y.LoginID
 WHERE X.Username = @Username
   AND X.[Password] = @Password
 ORDER BY Y.LastLogin DESC
end 
if @lastlogindt is null 
 begin
  set @lastlogin= 'Welcome - Initial Login'
 end
 Else
 Begin   
 /*
     Exec @rc = [dbo].[proc_DaylightSaving] @mydt=@lastlogin
     set @lastlogin='Last Login - ' + CAST(DateAdd(hh,@rc*(-1),@LastLogin) AS VARCHAR)	
     */
       set @lastlogin='Last Login - ' + CAST(DateAdd(hh,2*(-1),@LastLogin) AS VARCHAR)
 end
 
 Select @accesslevel as AccessLevel,
		@loginid as LoginID, 
		@personid as PersonID, 
		@username as Username,
		@fname as FName,
		@lname as LName, 
		@emailid as EmailID,
		@emailint as EmailInterval,
		@lastlogin as lastlogin		  
 
 Insert into ElanBiz5.LoginLogs
   Select @LoginID,@username,GETDATE()
        ,Case when @LoginID=-9999 
              then 'INVALID USERNAME'
              when @X=1
              Then 'SUCCESS'
              Else 'LOGIN FAILED'
              End              
 Return
 GO

Open in new window

0
 

Author Comment

by:mclarkdatasimplicity
Comment Utility
Hi Lowfatspread,

I'm sorry... I can see now that my original SQL statement is misleading. I don't know if this is all that's causing the problem or not, but the "LoginID" is a number used to identify the person logging in. So for example, in tblLogins, the LoginID for the Username "shelly@abc.com" is 355. That number needs to go into LoginLogs.loginid..  There is also a field in LoginLogs called "logID" that increments a number so that we have a unique key. Of course we don't need to fill that in because SQL Server is taking care of that for us. When shelly@abc.com logs in, 355 should get stored in LoginLogs.LoginID, and the results should look more like this:

logid     loginid     username                       lastlogin                      status
-------  ----------- ------------------------------ ----------------------- ------------------------------
1        355           shelly@abc.com             2011-03-02 17:23:00     LOGIN FAILED
2        355           shelly@abc.com             2011-03-02 17:23:00     SUCCESS
3        -9999       bob@abc                         2011-03-02 17:23:00     INVALID USERNAME
4        492          bob@abc.com                 2011-03-02 17:24:00      SUCCESS
5        492          bob@abc.com                 2011-03-02 19:35:00      SUCCESS

I really appreciate your help on this!

0
 

Author Comment

by:mclarkdatasimplicity
Comment Utility
One more thing: My passwords get passed in already hashed, and are stored hashed. Will your stored procedure still work with my hashed passwords? I'm thinking it will, but wanted to be sure.

Thanks,
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
sorry -- but thats what my output shows doesn't it?

the hashed password shouldn't be a problem ...

are you storing it on the table as varchar?

if your storing as varbinary (or image) then pass it as that datatype.
/****** Object:  StoredProcedure [dbo].[proc_Login_Authenticate]    Script Date: 02/28/2011 12:26:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[proc_Login_Authenticate]  
 (  
   @Username varchar(50),  
   @Password varchar(50)  
 )  
 AS  
 set nocount on
 DECLARE @LoginID as Integer,@x integer
 SElect @LoginID = -9999,@x=0
 
 SELECT @LoginID=LoginID 
     , @X = case when [password]=@password then 1 else 0 end
   FROM tblLogins
  WHERE Username = @Username 
  
  Declare @accesslevel int,@personid int,@fname sysname,@lname sysname
     ,@emailid sysname,@emailint int,@lastlogindt smalldatetime,@rc int,@lastlogin varchar(30)
 if @x=1
 begin    
 SELECT TOP 1 
		@accesslevel=X.AccessLevel,
		 
		@personid=X.PersonID, 
		
		@fname=X.FName,
		@lname=X.LName, 
		@emailid=X.EmailID,
		@emailint=X.EmailInterval,
		@lastlogindt=y.lastlogin
		 
 FROM tblLogins X
 LEFT JOIN ElanBiz5.LoginLogs Y 
   ON X.LoginID = Y.LoginID
 WHERE X.Username = @Username
   AND X.[Password] = @Password
 ORDER BY Y.LastLogin DESC
end 
if @lastlogindt is null 
 begin
  set @lastlogin= 'Welcome - Initial Login'
 end
 Else
 Begin   
 
     Exec @rc = [dbo].[proc_DaylightSaving] @mydt=@lastlogin
     set @lastlogin='Last Login - ' + CAST(DateAdd(hh,@rc*(-1),@LastLogin) AS VARCHAR)	
     
 --      set @lastlogin='Last Login - ' + CAST(DateAdd(hh,2*(-1),@LastLogin) AS VARCHAR)
 end
 
 Select @accesslevel as AccessLevel,
		@loginid as LoginID, 
		@personid as PersonID, 
		@username as Username,
		@fname as FName,
		@lname as LName, 
		@emailid as EmailID,
		@emailint as EmailInterval,
		@lastlogin as lastlogin		  
 
 Insert into ElanBiz5.LoginLogs
   Select @LoginID,@username,GETDATE()
        ,Case when @LoginID=-9999 
              then 'INVALID USERNAME'
              when @X=1
              Then 'SUCCESS'
              Else 'LOGIN FAILED'
              End              
 Return
 GO

Open in new window

0
 

Author Closing Comment

by:mclarkdatasimplicity
Comment Utility
Thanks very much for all your  help!
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 69
SQL Connection (Error 18456) 14 29
Classic ASP application Will support SQL 2014 5 30
replication - alerts? 4 19
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now