February 22, 2016 at 7:31 am
Hi,
Something else I am new to and struggling with is writing Stored Procedures.
I have the following below -
ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]
@PolicyId INT, @AccountId INT, @PolicyNodeId INT, @output BIT OUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrentArrangement as bit
DECLARE @LastSystemActionId as int
DECLARE @ActionDate as date
DECLARE @NoOfBrokenArrangements as int
DECLARE @ReturnValue as bit
select @LastSystemActionId=LastSystemActionId from RentAccount where AccountId=@AccountId
--get the date of the action
select top 1 @ActionDate=convert(date, ActionDate) from RentAction
where SystemActionId=@LastSystemActionId and AccountId=@AccountId
order by ActionDate desc
--LastSystemActionId 8 - Repayment Arrangement Broken
if @LastSystemActionId = 8 AND DATEDIFF(d,@ActionDate, GetDate()) = 1
set @ReturnValue = 1
else
set @ReturnValue = 0
set @output = @ReturnValue
END
GO
Now what I want to do is test parts of this as I build it. So with the following code below I just want to call back the LastSystemActionId from RentAction....but I want to see the result, so could I SET the AccountID somewhere in the Stored Procedure in order to test?
select @LastSystemActionId=LastSystemActionId from RentAccount where AccountId=@AccountId
February 22, 2016 at 7:48 am
Of course you can. Did you try it?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2016 at 8:01 am
Yes I did try but kept getting errors as I'm obviously using the SET in the wrong place.
February 22, 2016 at 8:06 am
TSQL Tryer (2/22/2016)
Yes I did try but kept getting errors as I'm obviously using the SET in the wrong place.
Perhaps if you share what you tried or even the error message we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2016 at 8:13 am
ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]
@PolicyId INT, @AccountId INT, @PolicyNodeId INT, @output BIT OUT
AS
BEGIN
SET NOCOUNT ON;
SET @AccountId = 1
DECLARE @CurrentArrangement as bit
DECLARE @LastSystemActionId as int
DECLARE @ActionDate as date
DECLARE @NoOfBrokenArrangements as int
DECLARE @LastActionId as int
DECLARE @ReturnValue as bit
select @LastSystemActionId=LastSystemActionId from RentAccount where AccountId=@AccountId
I want to be able to pull back the LastSystemActionId for Accountid = 1
This is obviously wrong but I want to be able to print to screen the result.
Msg 102, Level 15, State 1, Procedure usp_test_Arrears_BrokenArrangement, Line 35
Incorrect syntax near '@AccountId'.
February 22, 2016 at 8:16 am
Hi,
Remove the ALTER , AS and BEGIN in the beginning of the SP and the END in the End.
Then just declare the variables (DECLARE) and you have a T-SQL code ready for testing. Actually I test the stored procedures in this way.
DECLARE @PolicyId INT, @AccountId INT, @PolicyNodeId INT, @output BIT OUT
SET NOCOUNT ON;
DECLARE @CurrentArrangement as bit
DECLARE @LastSystemActionId as int
DECLARE @ActionDate as date
DECLARE @NoOfBrokenArrangements as int
DECLARE @ReturnValue as bit
select @LastSystemActionId=LastSystemActionId from RentAccount where AccountId=@AccountId
--get the date of the action
select top 1 @ActionDate=convert(date, ActionDate) from RentAction
where SystemActionId=@LastSystemActionId and AccountId=@AccountId
order by ActionDate desc
--LastSystemActionId 8 - Repayment Arrangement Broken
if @LastSystemActionId = 8 AND DATEDIFF(d,@ActionDate, GetDate()) = 1
set @ReturnValue = 1
else
set @ReturnValue = 0
set @output = @ReturnValue
Igor Micev,
My blog: www.igormicev.com
February 22, 2016 at 8:18 am
TSQL Tryer (2/22/2016)
ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]@PolicyId INT, @AccountId INT, @PolicyNodeId INT, @output BIT OUT
AS
BEGIN
SET NOCOUNT ON;
SET @AccountId = 1
DECLARE @CurrentArrangement as bit
DECLARE @LastSystemActionId as int
DECLARE @ActionDate as date
DECLARE @NoOfBrokenArrangements as int
DECLARE @LastActionId as int
DECLARE @ReturnValue as bit
select @LastSystemActionId=LastSystemActionId from RentAccount where AccountId=@AccountId
I want to be able to pull back the LastSystemActionId for Accountid = 1
This is obviously wrong but I want to be able to print to screen the result.
Msg 102, Level 15, State 1, Procedure usp_test_Arrears_BrokenArrangement, Line 35
Incorrect syntax near '@AccountId'.
There is no syntax error in this portion of your procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2016 at 8:20 am
I do think your logic could be greatly simplified into a single select statement instead of all these variables though. A simple case expression could make this a lot simpler.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 22, 2016 at 8:57 am
Sean Lange (2/22/2016)
TSQL Tryer (2/22/2016)
ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]@PolicyId INT, @AccountId INT, @PolicyNodeId INT, @output BIT OUT
AS
BEGIN
SET NOCOUNT ON;
SET @AccountId = 1
DECLARE @CurrentArrangement as bit
DECLARE @LastSystemActionId as int
DECLARE @ActionDate as date
DECLARE @NoOfBrokenArrangements as int
DECLARE @LastActionId as int
DECLARE @ReturnValue as bit
select @LastSystemActionId=LastSystemActionId from RentAccount where AccountId=@AccountId
I want to be able to pull back the LastSystemActionId for Accountid = 1
This is obviously wrong but I want to be able to print to screen the result.
Msg 102, Level 15, State 1, Procedure usp_test_Arrears_BrokenArrangement, Line 35
Incorrect syntax near '@AccountId'.
There is no syntax error in this portion of your procedure.
There's a missing END. I'd suggest to remove the BEGIN.
I remember there's a performance penalty for encapsulating procedures using BEGIN...END. Even if there isn't, it's useless.
February 22, 2016 at 9:13 am
Luis Cazares (2/22/2016)
Sean Lange (2/22/2016)
TSQL Tryer (2/22/2016)
ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]@PolicyId INT, @AccountId INT, @PolicyNodeId INT, @output BIT OUT
AS
BEGIN
SET NOCOUNT ON;
SET @AccountId = 1
DECLARE @CurrentArrangement as bit
DECLARE @LastSystemActionId as int
DECLARE @ActionDate as date
DECLARE @NoOfBrokenArrangements as int
DECLARE @LastActionId as int
DECLARE @ReturnValue as bit
select @LastSystemActionId=LastSystemActionId from RentAccount where AccountId=@AccountId
I want to be able to pull back the LastSystemActionId for Accountid = 1
This is obviously wrong but I want to be able to print to screen the result.
Msg 102, Level 15, State 1, Procedure usp_test_Arrears_BrokenArrangement, Line 35
Incorrect syntax near '@AccountId'.
There is no syntax error in this portion of your procedure.
There's a missing END. I'd suggest to remove the BEGIN.
I remember there's a performance penalty for encapsulating procedures using BEGIN...END. Even if there isn't, it's useless.
I am not a fan of using BEGIN/END for every procedure. This one to me is more a matter of preference. There are certainly valid arguments on both sides of this one.
I was really hoping this was just a snippet since it is only a portion of the original query. I am scared as this indicates a lack of understanding the basics of this procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 23, 2016 at 4:04 pm
I think this is the (actual) logic you need, based on analyzing the original code:
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[usp_test_Arrears_BrokenArrangement]
@PolicyId int,
@AccountId int,
@PolicyNodeId int,
@output bit OUTPUT
AS
SET NOCOUNT ON;
--LastSystemActionId 8 - Repayment Arrangement Broken
SELECT @output = CASE WHEN ra.LastSystemActionId = 8 AND DATEDIFF(DAY, ra.ActionDate, GETDATE()) = 1
THEN 1
ELSE 0
END
FROM (
SELECT TOP (1) *
FROM dbo.RentAction
WHERE AccountId = @AccountId
ORDER BY ActionDate DESC
) AS ra
RETURN 0
GO --end of proc
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply