October 29, 2011 at 6:36 am
Hi, I have a question regarding SQL Server stored procedures.
Imagining that there is procedure to wich i only have access to the header:
procFoo (par1 int, par2 int);
In an application I have a table on which I run this procedure with every row. I Intended to optimize creating a new procedure with the following body:
create type tbType the table (col1 int, col2 int)
create newProc (@ tbPar tbType)
as
--What to do here!? (Without using cursor or while)
go
October 29, 2011 at 6:49 am
Welll #1 you'll need the whole procedure definition to create the 2nd procedure.
#2 With the tvp you'll have to edit every step in the query and chage where Col = @Par1 to a join to the tvp. You might need to have a few goup bys to keep the logic equivalent but that's about it.
#3 And something I've never tested is the performance of tvp inside a procedure. If they still estimate to 1 single row then there migth be a few more things you need to do to keep the procedure performing well.
October 29, 2011 at 6:54 am
Tks for the reply, but my problem is that i dont have the acess to the 1 sp definition, i can only call/execute it.
In this scenario is cursor/while the only option?
October 29, 2011 at 6:58 am
I'd try calling whoever is in control of it and ask for a copy.
Whatever else you can do here is going to be really slow (perf wise).
October 29, 2011 at 7:01 am
can't...
i tryed this:
create type tbType the table (col1 int, col2 int)
create newProc (@ tbPar tbType)
as
declare @col1Par int,@col2Par int
exec procFoo (select @par1=col1,@par2=col2 from @tbPar)
go
but i get this:
Procedure or function 'procFoo' expects parameter '@par1', which was not supplied.
October 29, 2011 at 7:04 am
No your only other option here is a cursor.
October 29, 2011 at 8:30 am
damm...tks anyway
October 29, 2011 at 9:15 am
Why can't you have access to it?
3rd party?
October 29, 2011 at 9:25 am
kind of..."insourcing" in a pretty sigilous project...
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply