Execute procedure in procedure?
-
Hi all!
I have a procedure with generates me set of data for a specific week.
I need to execute it for few weeks. The idea is to create procedure to pass parameters to second procedure,
sth like that
Create procedure call_procedure@multiple_weeks
for each week in @multiple_weeks
execute test @week
How to do it properly?
-
you want to create a SQL job that calls your procedure.
that job has a schedule, so you can call it once day/hour whatever.
you can leave it to run forever, or have an end date
you cna actually have multiple schedules, if your requrements are not quite as simple as a single regular repeating period
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I dont need a schedule, it only about data.
I have multivalue parameter @multiple_weeks with can be for example '20010101,20010108,20010115'
and i need to execute my procedure for 20010101 and for 20010108 and for 20010115
so it would be sth like that
execute call_procedure '20010101,20010108,20010115'
(
for each week in '20010101,20010108,20010115'
execute test procedure @week
)
-
aleksandra 81219 (8/27/2015)
I dont need a schedule, it only about data.I have multivalue parameter @multiple_weeks with can be for example '20010101,20010108,20010115'
and i need to execute my procedure for 20010101 and for 20010108 and for 20010115
so it would be sth like that
execute call_procedure '20010101,20010108,20010115'
(
for each week in '20010101,20010108,20010115'
execute test procedure @week
)
ahh, misunderstood the request.
you can do it, but it requires two components: you need to split your string '20010101,20010108,20010115' into a table, and then you need a cursor, to loop through each row in your table in order to call your procedure.
the table value function dbo.DelimitedSplit8k is what i would recommend to convert your string into a list.
you can find it, and an awesome explanaiton on it's use here:http://www.sqlservercentral.com/articles/Tally+Table/72993/
this is a rough draft, but the core of your outer procedure would look something like this:
CREATE OuterProcedure(@multiple_weeks varchar(8000)
AS
BEGIN
declare
@val date
declare c1 cursor for
--###############################################################################################
--cursor definition
--###############################################################################################
select CONVERT(date,Item) FROM dbo.DelimitedSplit8k(@multiple_weeks,',') Myfn
--###############################################################################################
open c1
fetch next from c1 into @val
While @@fetch_status <> -1
begin
exec MyInnerProcedure @val
fetch next from c1 into @val
end
close c1
deallocate c1
GO
END
cursors are generally frowned upon, as doing DML commands one at a time is slow, repetitively redundant, and not considered a best practice;
you should consider creating a new procedure, or modifying the existing, to be able to do the work against a set of values instead of by single date.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Of course, you can do it without a cursor and a bit of dynamic sql.
Create procedure call_procedure(
@multiple_weeks varchar(8000)
)
AS
----Sample Data
--DECLARE @multiple_weeks varchar(8000) = '20010101,20010108,20010115';
DECLARE @sql nvarchar(max);
SELECT @sql = ( SELECT 'execute test ' + QUOTENAME( Item, '''') + ';' + CHAR(10)
FROM dbo.DelimitedSplit8k(@multiple_weeks,',')
FOR XML PATH(''),TYPE).value('.', 'nvarchar(max)');
--PRINT @sql
EXECUTE sp_executesql @sql;
GO
The method used in here is explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
With a little of luck, you can even get it simpler:
Create procedure call_procedure(
@multiple_weeks varchar(8000)
)
AS
----Sample Data
--DECLARE @multiple_weeks varchar(8000) = '20010101,20010108,20010115';
DECLARE @sql nvarchar(max);
SELECT @sql = 'execute test ' + REPLACE(@multiple_weeks, ',', ';' + CHAR(10) + 'execute test ')
--PRINT @sql
EXECUTE sp_executesql @sql;
GO
-
Since you literally can't add enough values here to make a cursor all that bad, you might want to use a cursor just to have better control over the processing of the values. For example, as below; note that I have not tested this code:
CREATE PROCEDURE dbo.call_procedure (
@multiple_weeks varchar(8000) --valid date string(s) separated by commas,e.g.,'20010101,20010108,20010115'.
)
AS
--EXEC dbo.call_procedure @multiple_weeks = '20010101,20010108,20010115';
SET NOCOUNT ON;
DECLARE @week varchar(20)
DECLARE @week_number int
DECLARE cursor_weeks CURSOR LOCAL FAST_FORWARD FOR
SELECT ds.Item, ds.ItemNumber
FROM dbo.DelimitedSplit8k(@multiple_weeks, ',') ds
--or, to put out an error msg if invalid date, remove this and use the code below instead
WHERE
ISDATE(ds.Item) = 1
OPEN cursor_weeks
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_weeks INTO @week, @week_number
IF @@FETCH_STATUS <> 0
BREAK;
/*
IF ISDATE(@week) = 1
EXEC dbo.test @week
ELSE
BEGIN
RAISERROR('Week number %i, with value "%s", in the input parameter is not a valid date, skipping that value.', 10, 1,
@week_number, @week)
END --ELSE
*/
EXEC dbo.test @week
END --WHILE
DEALLOCATE cursor_weeks
GO --end of proc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
ScottPletcher (8/27/2015)
Since you literally can't add enough values here to make a cursor all that bad, you might want to use a cursor just to have better control over the processing of the values.That's true, performance won't make a significant difference in this case.
A cursor will give more flexibility on what you're doing, while my code will do the same with fewer lines of code.
-
Oh, so many cases to test, thank you very much
I have one more question.
I started with testing the cursor. I pass there for example 20150101 and at the output im getting 2015-01-
Do you maybe know where it is converted? In DelimitedSplit8K function?
-
Sth is wrong... im getting still only first date hmm
alter procedure [dbo].[test]@multiple_weeks varchar(8)
as
declare
@val date
create table #ps(id varchar(30),week varchar(8)[...])
declare c1 cursor for
select CONVERT(varchar,Item,112) FROM [dbo].[DelimitedSplit8K](@multiple_weeks ,',') Myfn
open c1
fetch next from c1 into @val
While @@fetch_status <> -1
begin
insert into #ps id,,CONVERT(varchar,@val,112), [...]
fetch next from c1 into @val
end
close c1
deallocate c1
select [...]
from
(select[...]) d
Where may I be wrong in such schema?
-
Ok, know what was wrong with executing but data is also not proper :doze:
I put some PRINT commands, look at this
WeeksOnStart
20150406,20150401
val_in_cursor
2015-04-06
Warning: Null value is eliminated by an aggregate or other SET operation.
(1288 row(s) affected)
val_in_cursor
2015-04-01
Warning: Null value is eliminated by an aggregate or other SET operation.
(1253 row(s) affected)
WeeksOnEnd
20150406,20150401
(0 row(s) affected)
When i cut off one join to date to make ending select not filtering on these dates Im getting duplicated data like whis
20150401 36 78976
20150406 36 78976
20150401 37 34562
20150406 37 34562
20150401 38 37655
20150406 38 37655
-
aleksandra 81219 (8/28/2015)
Ok, know what was wrong with executing but data is also not proper :doze:I put some PRINT commands, look at this
WeeksOnStart
20150406,20150401
val_in_cursor
2015-04-06
Warning: Null value is eliminated by an aggregate or other SET operation.
(1288 row(s) affected)
val_in_cursor
2015-04-01
Warning: Null value is eliminated by an aggregate or other SET operation.
(1253 row(s) affected)
WeeksOnEnd
20150406,20150401
(0 row(s) affected)
When i cut off one join to date to make ending select not filtering on these dates Im getting duplicated data like whis
20150401 36 78976
20150406 36 78976
20150401 37 34562
20150406 37 34562
20150401 38 37655
20150406 38 37655
Yeah, varchar(8) could only hold one value (at most) :-).
What "join to date"? You must be talking about code we don't see, making it virtually impossible for us to debug.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
First you were truncating the date because you inserted the string value into a date variable and were expecting an 8 character string, so the variable converted again from date to string in the YYYY-MM-DD format.
Then, your cursor had the wrong condition on the WHILE, so it wasn't doing what was expected (this is a wild guess).
The main problem is that we can't see what you're doing and sharing mere snippets won't help us understand it. We need to be able to run the code that you're running to define what's wrong and the problems you might face.
-
my example i provided made sure the valuewas converted to date type. As Luis pointed out, converting the date to a string in this format:2015-04-01 is now ten characters, with it getting truncated due to your varchar(8) variable.
dates should stay as dates. if your procedure accepts a date or datetime value, send it a datetime value...don't convert to a string that you think looks nice.
keep datetime as datetime.
show us your actual code, and you can get some solid peer review form experienced volunteers here.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
If i only could put here the code, ehhh
alter procedure [dbo].[test]@multiple_weeks varchar(4000)
as
declare
@val date
create table #ps(id varchar(30),week varchar(8)[...])
declare c1 cursor for
select CONVERT(varchar,Item,112) FROM [dbo].[DelimitedSplit8K](@multiple_weeks ,',') Myfn
open c1
fetch next from c1 into @val
While @@fetch_status <> -1
begin
insert into #ps id,CONVERT(varchar,@val,112), [...] -- complicated select with a lot of calculations
fetch next from c1 into @val
end
close c1
deallocate c1
select [week, id, etc] -- select from temp table with calculations
from (select week, id, etc from #ps ) d
where date in @multiple_weeks -- they have here where however data previously are selected only for 2 dates
The schema is like this. When i execute it for one date, its ok, for second, its ok. But when i execute it for 2 dates, i see in PRINT commands parameters in cursor are ok (Date is changing) but i have no data.
I have deleted last where and i got data but data for first date duplicated with second date as below
20150101 10 12
20150106 10 12
20150101 15 20
20150106 15 20
Maybe im ending the cursor in wrong place? But i dont think so. If i end it after select, i would get 2 separate sets of data
-
looks to me like the issue is in the complicated select that you left out of the post...
you not getting the data you expect, so it's gotta be in the joins/assumptions fo the data that you are not showing. implicit conversions due to the date problem, or something we don't know unless you show us.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply