Stored Procedure Help

[doctor2001]

Hi -

If anybody could help me with this stored procedure it would be greatly appreciated…

ALTER PROCEDURE dbo.GetMy_show_Images_ImageID_GetInfo
@galleryid int,
@imageId int

AS

SELECT myZoom, imageName
FROM My_show_Images
WHERE [imageid] = @imageId


SELECT top 1 imageId as nextId

FROM My_show_Images
WHERE
[galleryid] = @galleryId
AND

[imageid] > @imageId

ORDER by imageId


SELECT top 1 imageId as prevId

FROM My_show_Images
WHERE
[galleryid] = @galleryId
AND

[imageid] < @imageId

ORDER by imageId DESC


I would like to have the results returned to one row instead of 3…Is this possible?

Thanks In Advance

 

[DrSql]

First insert all three selects into 3 temp tables and last you do a select from all three where imege1d=imageid in all tables..

[tt]
SELECT imageid, myZoom, imageName
into ##My_show_Images1
FROM My_show_Images
WHERE [imageid] = @imageId



SELECT top 1 imageId as nextId
into ##My_show_Images2
FROM My_show_Images
WHERE
[galleryid] = @galleryId
AND

[imageid] > @imageId

ORDER by imageId



SELECT top 1 imageId as prevId
into My_show_Images
FROM My_show_Images
WHERE
[galleryid] = @galleryId
AND
[imageid] < @imageId
ORDER by imageId DESC



select a.imageid, a.myZoom, a.imageName,b.nextId, c.prevId from ##My_show_Images1 a, ##My_show_Images2 b,##My_show_Images3 c
where a.imageid=b.nextId
and b.nextId=c.prevId
[/tt]

Tyr this you might need to tweek it.

Dr.Sql
Good Luck.