How to Create a Stored Procedure That Will Optionally Search Columns

How do I create a stored procedure that will optionally search columns?

While the COALESCE trick is neat, my preferred method is:

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
    @Cus_Name varchar(30) = NULL
    ,@Cus_City varchar(30) = NULL
    ,@Cus_Country varchar(30) = NULL
    ,@Dept_ID int = NULL
    ,@Dept_ID_partial varchar(10) = NULL
AS
SELECT Cus_Name
       ,Cus_City
       ,Cus_Country
       ,Dept_ID
FROM Customers
WHERE (@Cus_Name IS NULL OR Cus_Name LIKE '%' + @Cus_Name + '%')
      AND (@Cus_City IS NULL OR Cus_City LIKE '%' + @Cus_City + '%')
      AND (@Cus_Country IS NULL OR Cus_Country LIKE '%' + @Cus_Country + '%')
      AND (@Dept_ID IS NULL OR Dept_ID = @DeptID)
      AND (@Dept_ID_partial IS NULL OR CONVERT(varchar, Dept_ID) LIKE '%' + @Dept_ID_partial + '%')

These kind of SPs can easily be code generated (and re-generated for table-changes).

You have a few options for handling numbers - depending if you want exact semantics or search semantics.

Find all stored procedures that reference a specific column in some table

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

Stored Procedure with optional WHERE parameters

One of the easiest ways to accomplish this:

SELECT * FROM table 
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))

etc.
This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.

Adding Column to stored procedure conditionally

There are lots of issues in your code:

use SELECT *
use Cursor
...

In the end, it is overly complicated and looks at lot like Application code (C#, java, ...)

What you want to do should be done with a single Select (ie. on a set of data). Columns names should also be listed between SELECT and FROM.

One option is:

CREATE FUNCTION [dbo].[UDF_FulfilmentBatch](@FulfilmentID INT) RETURNS TABLE
AS
RETURN (
    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery
    FROM VW_FulfilmentExtract_HH
    WHERE @FulfilmentID = 4

    UNION ALL

    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery = NULL
    FROM VW_FulfilmentExtract_ID
    WHERE @FulfilmentID = 3

    UNION ALL

    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery = NULL
    FROM VW_FulfilmentExtract_Art
    WHERE @FulfilmentID = 2

    UNION ALL

    SELECT sequence = ROW_NUMBER() Over(Order By (Select 1))
        , _membershipid, membershipNo, delivery = NULL
    FROM VW_FulfilmentExtract_Tha
    WHERE @FulfilmentID = not in (2, 3, 4)
);

Here I use ROW_NUMBER to generate your sequence number.

I added the Delivery column and set it to NULL when it is not needed.

You must update columns name. I just guess them.

By the way, this is not a Stored Procedure but a Inline User-Defined Functions

How can I use optional parameters in a T-SQL stored procedure?

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

Add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (@LastName IS NULL OR LastName= @LastName) before the query plan is created based on the runtime values of the local variables, and an index can be used.

This will work for any SQL Server version (return proper results), but only include the OPTION(RECOMPILE) if you are on SQL 2008 SP1 CU5 (10.0.2746) and later. The OPTION(RECOMPILE) will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance. If not on that version of SQL Server 2008, just leave that line off.

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
    END

Show a few columns in a stored procedure based on a parameter

No, CASE is a function, and can only return a single value.

And According to your comment:-

The issue with 2 select statements are that it's a major complicated
select statement and I really don't want to have to have the whole
select statement twice.

so you can use the next approach for avoid duplicate code:-

Create procedure proc_name (@internal char(3), .... others)
as
BEGIN
    declare @AddationalColumns varchar(100)
    set @AddationalColumns = ''

    if @internal = 'Yes'
        set @AddationalColumns = ',addtionalCol1 , addtionalCol2'

        exec ('Select 
                col1, 
                col2,
                col3' 
                + @AddationalColumns +
                'From
                    tableName
                Where ....
                ' )
END

Optional Order By in stored procedure?

Building the ORDER BY dynamically is pretty straightforward. I assume you are passing parameters like:

@OrderByCol1 NVARCHAR(255),
@OrderByCol2 NVARCHAR(255),
...etc...

These may or may not include direction too, e.g. N'MyColumn DESC'. So then you can build this together as follows:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'SELECT ... 
  FROM ...  
  WHERE ... 
  ORDER BY NULL'
  + COALESCE(',' + @OrderByCol1, '')
  + COALESCE(',' + @OrderByCol2, '')
  ...etc...;

PRINT @sql;
--EXEC sp_executesql @sql;

Since we apparently need to recap the entire SQL injection conversation every time an answer even mentions dynamic SQL, I will add some examples.

If they can only ever sort ascending, then you can prevent SQL injection by simply wrapping the parameter values in QUOTENAME().

  + COALESCE(',' + QUOTENAME(@OrderByCol1), '')
  + COALESCE(',' + QUOTENAME(@OrderByCol2), '')

Otherwise, you could also split the parameters apart by space (assuming your column names don't contain spaces, which they shouldn't!), and validate that the left side is always present in sys.columns.

IF @OrderByCol1 IS NOT NULL AND EXISTS
(
   SELECT 1 FROM sys.columns 
     WHERE [object_id] = OBJECT_ID('dbo.MyTable')
     AND name = LTRIM(LEFT(@OrderByCol1, CHARINDEX(' ', @OrderByCol1)))
)
BEGIN
  SET @sql += ',' + @OrderByCol1;
END

You may also want to have checks there in case they pass nothing into any of the parameters, or only pass a value into parameter #4, etc. The above does that.

It might be better to pass these in using a TVP, then you don't have to place arbitrary and artificial limits on the number of columns they can choose. Here is one example of a three-column TVP which allows you to pass in a set of order by columns, dictate the order they're applied, and indicate the sort order for each. This also makes it slightly easier to check that each column is really a column (and hey, if you name a column [1;truncate table dbo.something], you deserve what you get...).

First, create the following user-defined table type in your database:

CREATE TYPE dbo.OrderByColumns AS TABLE
(
  [Sequence] TINYINT PRIMARY KEY,
  ColumnName SYSNAME NOT NULL,
  Direction VARCHAR(4) NOT NULL DEFAULT 'ASC'
);

Then:

DECLARE @x dbo.OrderByColumns;

INSERT @x SELECT 1, N'name', 'ASC';
INSERT @x SELECT 2, N'ID', 'DESC';
INSERT @x SELECT 3, N'1;truncate table dbo.whatever', 'DESC';

-- the above could be a parameter to your stored procedure
-- and could be populated in a DataTable in your application

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ...
  WHERE ... ORDER BY NULL';

SELECT @sql += ',' + QUOTENAME(x.ColumnName) + ' ' + x.Direction
FROM sys.columns AS c
INNER JOIN @x AS x
ON c.name = x.ColumnName
AND c.[object_id] = OBJECT_ID('dbo.MyTable')
ORDER BY x.[Sequence] OPTION (MAXDOP 1);

PRINT @sql;

While you can do this using CASE, generating the ORDER BY dynamically - particularly when it influences the plan choice - can actually be better for performance. With a static query, you get a plan for whatever @order_column was first, then it gets reused even if a different ordering column might have led to a different, more efficient plan. Different plans are likely with different ORDER BY clauses because these require different SORT operators. You can get around this problem somewhat using OPTION (RECOMPILE), which ensures you get a new plan generated every time, but now you pay for a compile cost every single time, even if the same order by is always, or almost always, used.

When you use dynamic SQL, each version of the query is optimized separately. Plan cache bloat is a concern that is somewhat offset by the optimize for ad hoc workloads server setting. This prevents SQL Server from caching the whole plan for a specific variation of a query until that specific variation has been used twice.