CODEWITHSUNDEEP
sqlstored-procedures
drewwyatt
drewwyatt

Reputation: 6027

How can I always return 3 values in this stored procedure?

I have an association table. The rows look something like: id, objectID, thingID.

I need a stored procedure to perform a select statement that will return 3 values:

item1ID, item2ID, item3ID

So the query will look something like:

SELECT TOP 3 objectID WHERE thingID = 7 -- (or something)

There may not always be three rows returned, however.

What would the stored proc look like that returned the rows as values, but zeroes for the remaining rows if 3 are not returned?

examples:

data

results where thingID = 2

item1ID: 12, item2ID: 13, item3ID: 0

results where thingID = 3

item1ID: 14, item2ID: 15, item3ID: 16

Upvotes: 1

Views: 70

Answers (5)

Tomas Pastircak
Tomas Pastircak

Reputation: 2857

this may be the nicest and most universal, but probably also most difficult solution. The aggregation MAX function is needed, but doesn't affect the result.

SELECT [1] AS Item1ID,[2] AS Item2ID, [3] AS Item3ID FROM
    (SELECT ROW_NUMBER() 
     OVER(order by id) rownum,objectId FROM data where thingId = 3) AS rows
    PIVOT(
    MAX(objectId)
    FOR rownum IN ([1],[2],[3])
    ) as piv

Upvotes: 0

user3277102
user3277102

Reputation: 76

You could do something like this See fiddle

http://sqlfiddle.com/#!3/ed8ca/10

select TOP 3 ID, thing
from
(
select 0 as srt,ID,thing from tbl
where thing = 2
union all
select 1 as srt,0 as ID, 0 as thing
union all
select 2 as srt,0 as ID, 0 as thing
union all
select 3 as srt,0 as ID, 0 as thing
) as t
order by srt,thing

Upvotes: 0

Grax32
Grax32

Reputation: 4059

Similar to other answers but using sql table variable instead of temp table.
SQL table variables are cleaned up when the proc completes.

create proc ReturnTop3
as
begin

    declare @returnTable as table (
        objectId int
    )

    declare @count int


    insert into @returnTable
        SELECT TOP 3 objectID WHERE thingID = 7

    set @count = (select COUNT(*) from @returnTable)

    while (@count < 3)
        begin
            insert into @returnTable select 0
            select @count = @count + 1
        end

    select * from @returnTable
end

Upvotes: 2

rory.ap
rory.ap

Reputation: 35270

Something like this?

DECLARE @InputThingID INT = 2

DECLARE @ID AS INT, @ItemID1 AS INT = 0, @ItemID2 AS INT = 0, @ItemID3 AS Int = 0
SELECT * INTO #Temp FROM MyTable WHERE ThingID=@InputThingID
SELECT TOP 1 @ID=ID, @ItemID1 = ObjectID FROM #Temp
DELETE FROM #Temp WHERE ID=@ID
SELECT TOP 1 @ID=ID, @ItemID2 = ObjectID FROM #Temp
DELETE FROM #Temp WHERE ID=@ID
SELECT TOP 1 @ID=ID, @ItemID3 = ObjectID FROM #Temp
DELETE FROM #Temp WHERE ID=@ID
SELECT @ItemID1 AS ItemID1, @ItemID2 AS ItemID2, @ItemID3 AS ItemID3
DROP TABLE #Temp

Upvotes: 0

Karthik Ganesan
Karthik Ganesan

Reputation: 4222

You can create a temp table and do it this way

    Create table #test (Id int);

    INSERT INTO #test
    SELECT TOP 3 objectID WHERE thingID = 7



    WHILE (SELECT COUNT(1) from #test) < 3
    BEGIN
        INSERT INTO #test 
        VALUES (0)
    END

    SELECT * FROM #test

    DROP TABLE #test

Upvotes: 0

Related Questions