April 26, 2013 at 2:12 pm
I have 2 Stored Procedures Procedure A and Procedure B.
Both Procedures have @year as parameter and if no parameters passed take the current year as an input parameter.
Procedure B is called within Procedure A
But Procedure B does not work as expected.
********THERE ARE NO ERRORS when I say PROCEDURE B does not work ********.Everything compiles and executes fine.
If the Procedure B is executed as standalone or isolated it works fine and inserts data.But when
used in nested mode it works only for Previous year i.e @year-1 for second run , but does not work for current year.
It work for current year only when run as isolated or stanalone and inserts data into a table.
The syntax for procedure is as below
CREATE PROCEDURE [Procedure A]
@Year int = null
WITH EXECUTE AS 'Domain\user'
AS
IF (@Year is null)
BEGIN
SET @Year = (Select top 1 year from table
where type= 'CurrentYear')
END
@LastYear = @Year -1
EXEC Procedure B @Year = @Year /* DOes Not work with nested ( no data inserted).Works only when executed as standalone ( data gets inserted) */
EXEC Procedure B @Year = @LastYear /* works in nested mode only when the nested SP is ran twice.Also works standalone */
THere are many other SP's within Procedure A and all work fine.
April 26, 2013 at 2:32 pm
Nilesh-788004 (4/26/2013)
I have 2 Stored Procedures Procedure A and Procedure B.Both Procedures have @year as parameter and if no parameters passed take the current year as an input parameter.
Procedure B is called within Procedure A
But Procedure B does not work as expected.
********THERE ARE NO ERRORS when I say PROCEDURE B does not work ********.Everything compiles and executes fine.
If the Procedure B is executed as standalone or isolated it works fine and inserts data.But when
used in nested mode it works only for Previous year i.e @year-1 for second run , but does not work for current year.
It work for current year only when run as isolated or stanalone and inserts data into a table.
The syntax for procedure is as below
CREATE PROCEDURE [Procedure A]
@Year int = null
WITH EXECUTE AS 'Domain\user'
AS
IF (@Year is null)
BEGIN
SET @Year = (Select top 1 year from table
where type= 'CurrentYear')
END
@LastYear = @Year -1
EXEC Procedure B @Year = @Year /* DOes Not work with nested ( no data inserted).Works only when executed as standalone ( data gets inserted) */
EXEC Procedure B @Year = @LastYear /* works in nested mode only when the nested SP is ran twice.Also works standalone */
THere are many other SP's within Procedure A and all work fine.
Going to need a bit more detail here to have a chance at helping. We don't know what the contents of the procB are, we don't know what it should do, we don't know the business logic and we can't see your screen. Please take a few minutes and read the first article in my signature for best practices when posting a question.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply