I want to write Stored Procedure to get the following output when I pass the parameter 1,
column_name
-----------
option 1
option 2
option 3
and when I pass the parameter 2 the output should be,
column_name
-----------
option 5
option 6
Is it possible to create procedure to get this output without using any tables?
I use SELECT FROM VALUES for such tasks. Possible solution for your case:
SELECT column_name
FROM (VALUES
( 'option 1' )
,( 'option 2' )
,( 'option 3' )
) result (column_name)
WHERE @param = 1
UNION
FROM (VALUES
( 'option 5' )
,( 'option 6' )
) result (column_name)
WHERE @param = 2
By using a combination of SELECTs and UNIONs you can produce hand-constructed tables like so:
SELECT 'Option 1' as Option
UNION
SELECT 'Option 2' as Option
UNION
SELECT 'Option 3' as Option
You can combine this with a control/flow statement to determine which SELECT/UNION combo should be used. Unfortunately you have MySQL and sql server tags, so I can't make the syntax for the stored proc more specific, but hopefully that should be the bit you're comfortable with!
CREATE PROCEDURE sp_ArbitraryOutput (@parm Int)
AS
CASE
WHEN COALESCE(@parm, 1) = 1 THEN
SELECT "option 1" AS column_name
UNION
SELECT "option 2" AS column_name
UNION
SELECT "option 3" AS column_name
WHEN @parm = 2 THEN
SELECT "option 4" AS column_name
UNION
SELECT "option 5" AS column_name
UNION
SELECT "option 6" AS column_name
END
GO
no need to create any physical table in database for storing these information . you can use temporary table or table variable in store procedure to store these data . you can create store procedure like :
create PROCEDURE temp
@id int
AS
BEGIN
declare @temptable table (column_name varchar(max))
if @id = 1
begin
insert into @temptable values('option 1');
insert into @temptable values('option 2');
insert into @temptable values('option 3');
end
if @id = 2
begin
insert into @temptable values('option 4');
insert into @temptable values('option 5');
end
select * from @temptable
END
GO
CREATE PROCEDURE uspTest
(
@param INT
)
AS
BEGIN
IF @param = 1
SELECT 'Option 1' column_name
UNION ALL
SELECT 'Option 2'
UNION ALL
SELECT 'Option 3'
ELSE
SELECT 'Option 5' column_name
UNION ALL
SELECT 'Option 6'
END
