Best Way write Query for parameter

Question

I have parameter clientid if not 'ALL' then i want to join with temp table otherwise i donot want to join with that table i wrote if else but i think there so be good way to write in single query. Any suggestion

DECLARE @clientid NVRACHAR(20) = '2,3,4' 
IF @clientid <> 'ALL' 
BEGIN
SELECT [AssigneeID] 
FROM [dbo].Assignee INNER JOIN
Temp_Client ON Clientid = Client_ID
ORDER BY [AssigneeID]
END
ELSE
BEGIN
SELECT [AssigneeID] 
FROM [dbo].Assignee 
ORDER BY [AssigneeID]
END

Answer 1

You can write compact code to do this but you need to set the goal for what you want to achieve. (Performance or clean code or ... ?).

Answer 2

Performance 

Answer 3

You should be ok with this long version of code. When check performance, there are many factors involved. Check your index and compare the execution plans between different versions of your query.  There are other code parts you are not showing here but this should get you start.

By the way, you can give SQL Sentry Plan Explorer a try if you haven't.

 

Answer 4

nothing you have done is either polite (please learn netiquette), or correct. Where is the DDL? 

SQL is based on a tiered architecture. This means that the query passes data from the database layer to a presentation layer. The per this presentation layer will order by, format and do other display things with the data.

SQL is based on sets. That means that tables have pleural or collective names. What you have told us as you have only one assignee. I am going to guess, based on too many years of fixing bad SQL, that your temp_client table is really a 1970's scratch tape implemented as a temp table in SQL (so totally wrong in declarative language). 

It is also fun to see how the "client ID" changes form. This is awful programming in any programming language. 

I have been doing this for a few decades. Please trust me when I tell you, you are not ready to begin programming in SQL yet. You have not got enough education and have not done enough research. You are going to come to forums and get nothing but stinking dirty kludges, instead of learning to be a competent programmer.

The general template of what you are trying to do looks like this:

SELECT client_id |
  FROM Something_Assignments 
 WHERE client_id = COALESCE (@in_client_id, client_id);

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

Hi sks_989,

According to your sample code, script below might be what you are looking for.

SELECT [AssigneeID] 
FROM [dbo].Assignee 
WHERE @clientid = 'ALL'
	OR (Clientid in (SELECT Client_ID FROM Temp_Client))
ORDER BY [AssigneeID]

---

Sam Zha
TechNet Community Support