is there any way to reduce the code or is it possible to write it in a single select query

Question

2.67/5 (2 votes)

See more:

SQL

CREATE FUNCTION [dbo].[getEquipmentName]
 (@EquipmentId varchar(500))
RETURNS varchar(500)
AS
begin
declare @EquipmentName varchar(500)

select @EquipmentName = EquipmentName
FROM   PMS_TBL_EQUIPMENT_MASTER where convert(varchar(60),UniqueId)=@EquipmentId

if @EquipmentName is null

select @EquipmentName = SubEquipmentName
FROM   PMS_TBL_SUB_EQUIPMENT_MASTER where convert(varchar(60),UniqueId)=@EquipmentId
if @EquipmentName is null
  select @EquipmentName = SubFunctionalBlockName
FROM   PMS_TBL_SUB_FUNCTIONAL_BLOCK_MASTER where convert(varchar(60),UniqueId)=@EquipmentId
if @EquipmentName is null
 select @EquipmentName=''''

return  @EquipmentName
end

can any one suggest me to reduce the above code into a single select statement?

Posted 27-Oct-15 3:53am

Bala Chandra C

Add a Solution

Comments

Sreekanth Mothukuru 27-Oct-15 10:01am

I think we can make use of "Case when then" or "IIF" statement in SQL Server to get it done in a single line..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-10-27T10:26:00

I'm afraid you can't use single SELECT statement, because you're searching for single value in many tables. So, the best way is to merge data using UNION (ALL)[^] statement.

SQL

SELECT @EquipmentName = COALESCE(EquipmentName, '')
FROM (
    SELECT EquipmentName
    FROM PMS_TBL_EQUIPMENT_MASTER
    UNION ALL
    SELECT SubEquipmentName
    FROM PMS_TBL_SUB_EQUIPMENT_MASTER
    UNION ALL
    SELECT SubFunctionalBlockName
    FROM PMS_TBL_SUB_FUNCTIONAL_BLOCK_MASTER
) AS T
WHERE CONVERT(VARCHAR(60),UniqueId)=@EquipmentId

Sreekanth Mothukuru · Answer 2 · 2015-10-27T04:22:00

Your set of queries can be written in either Case when then or "IIF" or using "COALESCE".

The best approach would be using "COALESCE" like this:

SQL

SELECT COALESCE(<first select statement>, <second select statement>, <third select statement>);

For more information follow this link:
https://msdn.microsoft.com/en-IN/library/ms190349.aspx[^]

is there any way to reduce the code or is it possible to write it in a single select query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0