Solved

multiple SELECT statements in a function

Posted on 2016-09-29
4
61 Views
Last Modified: 2016-09-29
Hi,

I was wondering if it is possible to put multiple SELECT statements in a function.  What I want to do is to do something like this:

SELECT F1, F2, Fn('p1'), Fn('p2')
FROM table

Inside the function, different SELECT statements will be executed based on the parameter.  thanks
0
Comment
Question by:mcrmg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41821968
do you mean to create a table valued function?

Table-Valued User-Defined Functions
https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx

or for Fn and Fn you can define them as a normal Function in MS SQL

CREATE FUNCTION (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms186755.aspx
0
 

Author Comment

by:mcrmg
ID: 41821986
I want to see if it is possible to do this

BEGIN  
	DECLARE @LookupValue varchar(20)
	SET @LookupValue = ''
	
	SELECT @LookupValue = 
	CASE
	WHEN @input = 'abc' THEN

			SELECT @LookupValue = 
								case f1
									when '1' then 'yes'
									when '2' then 'no'   end

					FROM table
					where id = 123
	WHEN @input = 'xyz' THEN

			SELECT @LookupValue = 
								case f2
									when '1' then 'open'
									when '2' then 'close'   end

					FROM table
					where id = 123
	END 








     RETURN @LookupValue
end;
go

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41822117
Yes, you can.  But, for efficiency, avoid using a local variable unless absolutely required:

CREATE FUNCTION function_name (
    @input varchar(10)
)
RETURNS varchar
AS
BEGIN
RETURN (
    SELECT CASE @input
        WHEN 'abc' THEN (
            SELECT case id when '1' then 'yes'
                                       when '2' then 'no' end
            FROM table_name
            WHERE id = 123
                  )
            WHEN 'xyz' THEN (
            SELECT case id when '1' then 'yes'
                                       when '2' then 'no' end
            FROM table_name
            WHERE id = 123
                  )
            END AS result
)
END /*FUNCTION*/
GO
0
 

Author Closing Comment

by:mcrmg
ID: 41822130
It works.  Thank you very much.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question