June 12, 2004 at 9:20 am
Hi,
I hav a SP called with 4 params, then the SP executes another SP using EXEC sp_executesql as follows :
EXEC sp_executesql @SQLString, @ParmDefinition,
@CompanyCode, @RefClient, @sCurrency, @RefArticle, @sTableName, @CustomerGroup, @CustomerType
This SP returns 10 fields, what IO, need to do is to put it results in a table or another record set inside the procedure in order to return 3 fields.
Thanks in advance
Salam
June 12, 2004 at 9:32 am
Not sure from your description of the problem but I think an INSERT statement would probably do... since you didn't mention which 3 parameters you wanted to save nor which table name, I'll make some up.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2004 at 10:26 am
Hi, thanks for your response. The insert is done inside the the SP2 as foolows
SP1 is called with ther 4 params
SP1 'C', 'TEST', 'fdfdfd', 'myTableName',
As you can see the 4th one is a table name.
Inside this SP, the call to SP2 is done through the dynamic SQL which it's 4th param is a table. Inside this procedure 2 # are appended to this name and the insert operation is done inside this one. Then there is a "select * from ##myTableName" which returns a recordset in the calling SP which is SP1.
It is this recordset I need to reduce from 10 to 3 fields. So my question in clear is it possible to assign the result of my dynamic call to a table then manipulate this table in SP1
Thanks again
June 13, 2004 at 5:40 am
Let me be more clear and accurate so you get the image, here is the problem :
Sp1 :
Create SP1
@V1,
@V2,
@V3,
@V4 = 'tempTable' (assigned dynami cally, each call this is changed)
As
Declare @V5
Declare @V6
--do somerthings and then calls SP as follows
exec SP2 @V1, @V2, @V3,@V4,@V5, @V6
Return
SP2 :
takes the six paramas do some queries and populates à temporary table (part of his name is @V4),
so if the name I gave was equal to tempTable
In the sql operation the tablename used is ##tempTable which contains 10 fileds
--it returns back the content of the temporary table as follows
Select * from ####tempTable
So SP1 will have a recordset with 10 fileds which I need to manipulate and choose 3 out of those 10 fields. I don't want to send the whole bunch in the recordset
Maybe you will ask why don't you populate only 3 fields, the answer is I am obliged to use a central SP which I cannot modify. so the constraint is I should have this 10 fields in the recordset which I don't fuly need
I hope I was clear
June 15, 2004 at 5:02 am
does this help you ?
create proc sp1
as
begin
CREATE TABLE #who (
spid int primary key,
ecid int,
status varchar(30),
loginame varchar(128),
hostname varchar(128),
blk int,
dbname varchar(128),
cmd varchar(16)
)
insert into #who
exec sp_who
select spid, cmd from #who
end
go
exec sp1
June 18, 2004 at 9:49 am
Hi and thanks the idea is exactly what I want to do, however, the sp you use is called without parameters. The one I should call is omething like that
create proc sp2
Create table ##testCopy (nCount integer,
ManfSKU varchar(35),
Desc varchar(50),
SL integer,
Priceb decimal(10, 2),
cePromo decimal(10, 2),
Web decimal(10, 2),
ce decimal(10, 2),
tockDelExp datetime,
odSKU varchar(35),
odDesc1 varchar(180),
endDesc varchar(50),
ExtDesc varchar(180),
fC_StockDelQty integer,
odType varchar(2), PGMajor_Price varchar(8),
PROD_PGMinor_Price varchar(3),
tDesc varchar(50),
tt char(2),
Obso bit,
Sens bit,
lium char(6),
it bit,
cIsET bit,
fcomo char(1) )
Insert Into ##testCopy
exec mySP 'C ', @Var1, 'EUR', 1, ' AND ITEM = ''ML0058'' ', @var2, ' ORDER BY PROD_DESC1 ASC , STOC_QUANTITY_AVAILABLE DESC ', '0', '11', ' ', 'P1', ' ORDER BY ockLevel Desc , fc_CatDesc ASC '
So I need, if I not mistaken to do a dynamic sql in order to execute mySP with variables.
Of course when executed with hard coded values it works fine
Thanks for any idea
June 19, 2004 at 5:40 am
Problem solved, thansk so much. I might write an article about it
June 21, 2004 at 8:48 am
I might write an article about it
You might consider reading one first
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 22, 2004 at 1:51 pm
Hi, wonderfull articl, however, I read haif of it. I will let you know my opinion about it as soon as I finish
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply