October 20, 2014 at 8:05 am
Apologies in advance if this question has already been (comprehensively/repeatedly) answered in the forum archives and i had not found it with my searches.
I've got a number of stored procedures that I have for reporting
All are of a similar starting format
For easier maintenance and to take away the need to change all of them if the methodology changes I want to split out shared code.
What I want to do is to take out the part that populates the @ID1 table into a separate stored proc which will be called from the report procs. The values from the shared proc will then be parsed back to the reporting proc.
I thought about using a function but I don't think it will be flexible enough as in certain cases I want to parse 2 or more IDs back into the final output.
I also don't want to make the code too complex so that it is relatively easy to read
CREATE PROC dbo.ReportM1 @ID INT AS
DECLARE
@ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID))
IF @ID = 0
INSERT INTO @ID1
SELECT ID
FROM dbo.ID
ELSE
IF @ID IN (140, 144)
INSERT INTO @ID1
VALUES (140), (144)
ELSE
INSERT INTO @ID1
SELECT @ID
-- Main Code
--
-- Select .....
--
-- where ID in (select ID from @ID1)
The first question I have is: can i do it with a table variable when going between procs or do i need to build a real table if i want it to maintain the logic in 1 place.
May be worth bearing in mind that the end user who will be executing the proc will only have read + execute stored proc access permissions so dropping, updating or creating real tables is not an option. #Temp tables are possible but since am using table variables throughout would prefer to stick with them.
The stored proc method would be preferable in my mind but before i spend any more time on it thought I would see if i could get some advice from here.
Many Thanks
October 20, 2014 at 8:17 am
I thought about using a function but I don't think it will be flexible enough as in certain cases I want to parse 2 or more IDs back into the final output.
Your best bet may be to use a table-valued function. It's difficult to suggest since you've left a lot of your stored procedure definition out.
John
October 20, 2014 at 8:21 am
Thanks for taking a look at it John Mitchell-245523
Managed to get it to work just now
Turned out to be very simple - i had just missed off the select line at the end of the shared proc when trying to do it before
For info if anyone is trying something similar this worked
Shared Proc:
create proc dbo.FormatIDs @ID int as
DECLARE
@ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID))
IF @ID = 0
INSERT INTO @ID1
SELECT ID
FROM dbo.ID
ELSE
IF @ID IN (140, 144)
INSERT INTO @ID1
VALUES (140), (144)
ELSE
INSERT INTO @ID1
SELECT @ID
select ID from @ID1
Reporting Proc:
CREATE PROC dbo.ReportM1 @ID INT AS
DECLARE
@ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID))
insert into @ID1 exec dbo.FormatIDs @ID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply