Assign each row of select results to sp parameters
-
I want to assign each row of select results to sp parameters.
For example i want to assign the value AttachmentNameUnique from the first row of
SELECT POAttachment.AttachmentNameUnique, POAttachment.POID, POAttachment.POAttachmentID
FROM PO INNER JOIN
POAttachment ON PO.POID = POAttachment.POID
WHERE (PO.POID = 7514)
AttachmentNameUnique POID POAttachmentID
Test.txt 7514 3819
Test1.txt 7514 3820
to Att1
and the value AttachmentNameUnique from the second row
to Att2.
-------------------------------------------------------
Any idea?
P.S. Sorry for my bad English.
-
this may help you getting started
SELECT POAttachment.AttachmentNameUnique
, POAttachment.POID
, POAttachment.POAttachmentID
, @Att1 = (case when (select count(*) from POAttachment a
where a.poid = PO.POID
and
a.POAttachmentID <= POAttachment.POAttachmentID ) =1
then AttachmentNameUnique else '' end )
, @Att2 = (case when (select count(*) from POAttachment a
where a.poid = PO.POID
and
a.POAttachmentID <= POAttachment.POAttachmentID ) = 2
then AttachmentNameUnique else '' end )
FROM
PO
INNER JOIN
POAttachment
ON PO.POID = POAttachment.POID
WHERE
(PO.POID = 7514)
hth
* Noel -
Use a cursor. Loop through the results. Assign column values of each row to different set of variables. Use variables as parameters to sp.
[Looks like someone else just gave an example above.]
-
May I ask, what's the purpose of this exercise here?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I think any of these cases returns
the same result. Which is the difference between two cases?
, @Att1 = (case when (select count(*) from POAttachment a
where a.poid = PO.POID
and
a.POAttachmentID <= POAttachment.POAttachmentID ) =1
then AttachmentNameUnique else '' end )
, @Att2 = (case when (select count(*) from POAttachment a
where a.poid = PO.POID
and
a.POAttachmentID <= POAttachment.POAttachmentID ) = 2
then AttachmentNameUnique else '' end )
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply