Conditional Stored Procedure With/Without Passing Parameter

conditional stored procedure with/without passing parameter

You're trying to test for null using =, a comparison operator. If you're using ANSI nulls, any comparison against null is false.

Where @studentId is any value (or null) the following expressions are all false:

@studentId = null  -- false
@studentId > null  -- false
@studentId >= null  -- false
@studentId < null  -- false
@studentId <= null  -- false
@studentId <> null -- false

So, in order to test for null you must use a special predicate, is null, i.e.:

@studentId is null

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.

SQL Conditional Join (with/without parameter) in Stored Procedure

ALTER PROCEDURE [dbo].[SPSample]
@gender varchar(20) = null
@username varchar(20) = null
AS

SELECT
per.firstName,
per.lastName,
per.gender

FROM person per
LEFT JOIN account ac 
ON     @username is not null 
   AND @username = ac.username 
   AND ac.idPerson = per.idPerson

WHERE --(
gender = @gender
--AND (@username is null or (@username is not null and @username = acc.username))

although not the cleanest, but it works

Parameterized stored procedure skip if when zero is passed

I think you want:

AND ([WarehouseId] = @WarehouseId OR @WarehouseId = 0)

Stored procedure with conditional Where clause in SQL Server

This can be done like:

SELECT intID, strQuestion, strAnswer, intCategory, intOrder
FROM tblA 
WHERE flgInd = @flgInd OR @flgInd IS NULL;

You can also use a CASE expression:

SELECT intID, strQuestion, strAnswer, intCategory, intOrder
FROM tblA 
WHERE CASE 
          WHEN flgInd = @flgInd THEN 1
          WHEN @flgInd IS NULL THEN 1 
          ELSE 0
       END = 1;

Stored Procedure: Update column with conditional condition

It looks like you don't need parameters at all if you don't plan to use them.
you already created variables. Parameters are for passing values in during exec

Exec dbo.SomeProc @param1 = 'testvalue'

Also your procedure relies on tbl1 only having 1 row in it.

SET @HealthStatus = (SELECT [Health Status] FROM [tbl1])

If the table has more than 1 row then this will just get you the value in the last row.
Your update statements could affect many rows

I think what you need is something like this..

ALTER PROCEDURE dbo.UpdateStatus 
AS
BEGIN

    UPDATE dbo.tbl1
    SET [Overall Status] = CASE WHEN [Health Status] = 'Y' AND ([Risk Level] = 'No Risk' OR [Risk Level] = 'Not Applicable') THEN 'Pass and No Risk' 
                                WHEN ([Health Status] = 'U' OR [Health Status] = '') AND [Risk Level] 'High Risk' THEN 'Pass and No Risk' 
                                ...
                            END
                            
END
GO

you could even abandon the Overall Status column and just have it as computed

CREATE VIEW dbo.tbl1WithStatus 
AS
BEGIN

    SELECT *,  -- don't really use select *,  use column names
            CASE WHEN [Health Status] = 'Y' AND ([Risk Level] = 'No Risk' OR [Risk Level] = 'Not Applicable') THEN 'Pass and No Risk' 
                 WHEN ([Health Status] = 'U' OR [Health Status] = '') AND [Risk Level] 'High Risk' THEN 'Pass and No Risk' 
                 ...
                 END AS [Overall Status]
    FROM dbo.tbl1
                            
END
GO

Parameterized stored procedure conditional join with search in joined table by 3 parameters

LIKE '%@BillingEmail%' should be LIKE '%' + @BillingEmail + '%' otherwise you are looking for an email address that contain the string '@BillingEmail' instead of the contents of the variable.

Unfortunately when you attempt a "contains" search e.g. like '%somesearchterm% SQL Server cannot make use of indexes to narrow down the search and therefore has to read and check every record in the table being searched. Therefore it will be fairly slow.

This kind of generalised search can be quite difficult to get to perform well. However while having an index doesn't narrow down the rows that need to be searched, having an index on say BillingEmail does reduce the amount of data that SQL Server has to load in order to perform the search and can make quite a dramatic improvement in performance. It also means the table itself isn't locked while you are searching.

So you could consider adding 3 indexes, BillingEmail, BillingFirstName, BillingLastName. You need to customise this to the way your system works, but if for example people normally search on FirstName and LastName you can use a single index on both columns.

This isn't very scalable if you want to search on many columns. You can extend the idea by creating a combined field e.g. FullName which is updated on a trigger to FirstName + ' ' + LastName and then you can index FullName.

When you actually carry out the search it can often perform better to do a first search on one search term and store the results (or just the IDs) in a temp table and then perform further filter actions against the temp table. This is especially useful if you can get your users to in some way narrow down the search with an initial selection.

Full-Text Indexes are also an option.

Optional parameters in SQL Server stored procedure

You can declare it like this:

CREATE PROCEDURE MyProcName
    @Parameter1 INT = 1,
    @Parameter2 VARCHAR (100) = 'StringValue',
    @Parameter3 VARCHAR (100) = NULL
AS

/* Check for the NULL / default value (indicating nothing was passed) */
if (@Parameter3 IS NULL)
BEGIN
    /* Whatever code you desire for a missing parameter */
    INSERT INTO ........
END

/* And use it in the query as so */
SELECT *
FROM Table
WHERE Column = @Parameter

---

Related Topics