Dynamic sql to be put in a table

Question

Dynamic sql to be put in a table

eliassal

Default port

Points: 1467
More actions
June 12, 2004 at 9:20 am

#108386

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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.

INSERT INTO tablename (fieldname1,fieldname2,fieldname3)
VALUES (@variable1,@variable2,@variable3)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eliassal Default port Points: 1467 More actions · Answer 2

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

eliassal Default port Points: 1467 More actions · Answer 3

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

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 4

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

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.

eliassal Default port Points: 1467 More actions · Answer 5

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

eliassal Default port Points: 1467 More actions · Answer 6

Problem solved, thansk so much. I might write an article about it

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

eliassal Default port Points: 1467 More actions · Answer 8

Hi, wonderfull articl, however, I read haif of it. I will let you know my opinion about it as soon as I finish