CODEWITHSUNDEEP
sql-server
Akhatarali Ansari
Akhatarali Ansari

Reputation: 70

Reuse Where Condition throughout Stored Procedure?

I have created one stored procedure, which returns records from multiple select queries.

See the below query:

DECLARE @UserId UNIQUEIDENTIFIER = '96105876-AB55-4D28-A8DD-6BFEC9D38EF8'

SELECT 
    CD.[Name],
    CD.[ASIN],
    CD.[Category],
    CD.[Quantity],
    CD.[Total],
    CD.[Weight]
FROM CartDetails CD
INNER JOIN Cart C ON C.Id = CD.CartId 
INNER JOIN Users U ON U.Id = C.UserId
WHERE U.Id = @UserId AND U.Active = 1 AND U.Deleted = 0

SELECT  
    DCC.DiscountAmount, 
    DCC.DiscountCouponId
FROM DiscountCouponConsumed DCC
INNER JOIN Users U ON U.Id = DCC.UserId
WHERE U.Id = @UserId AND U.Active = 1 AND U.Deleted = 0

Here, I have written the same query (which is always remains the same) in where condition. Now my question is, can we write where query once and will used for each select statement?

Upvotes: 0

Views: 367

Answers (4)

Dale K
Dale K

Reputation: 27225

You could store the valid User ID's in a temp table which is populated according to your common where clause. You can extend this method to as many queries as you like.

DECLARE @UserId UNIQUEIDENTIFIER = '96105876-AB55-4D28-A8DD-6BFEC9D38EF8';

declare @User table (id uniqueidentifier);

insert into @User (id)
  select id
  from Users U
  where U.Id = @UserId AND U.Active = 1 AND U.Deleted = 0;

SELECT 
    CD.[Name],
    CD.[ASIN],
    CD.[Category],
    CD.[Quantity],
    CD.[Total],
    CD.[Weight]
FROM CartDetails CD
INNER JOIN Cart C ON C.Id = CD.CartId 
INNER JOIN Users U ON U.Id = C.UserId
--WHERE U.Id = @UserId AND U.Active = 1 AND U.Deleted = 0
where U.id in (select id from @User);

SELECT  
  DCC.DiscountAmount,
  DCC.DiscountCouponId
FROM DiscountCouponConsumed DCC
INNER JOIN Users U ON U.Id = DCC.UserId
--WHERE U.Id = @UserId AND U.Active = 1 AND U.Deleted = 0
where U.id in (select id from @User);

Upvotes: 2

Dale K
Dale K

Reputation: 27225

@MarcinJ provided the simplest answer in a comment which I think is worthy of being an answer.

If you're always filtering on Users record this way, just run a query to check if the user is Active and is not Deleted at the beginning of your stored procedure, and if it doesn't meet your conditions, reset @UserId to NULL so that no other query will yield results

i.e. have the following code at the top of the proc:

select @UserId = case when exists (
    select 1
    from Users U
    where U.Id = @UserId AND U.Active = 1 AND U.Deleted = 0
  ) then @UserId else null end;

Upvotes: 0

Mahesh.K
Mahesh.K

Reputation: 901

If you have individual select statements with no relation between them then this might help you . Prepare your select queries and Where queries in separate variables and concatenate them together before you execute it .

DECLARE @UserId UNIQUEIDENTIFIER = '96105876-AB55-4D28-A8DD-6BFEC9D38EF8'

DECLARE @Query NVARCHAR(MAX) , @SelectQuery NVARCHAR(MAX), @WhereQuery NVARCHAR(MAX)

SET @WhereQuery = 'WHERE U.Id = @UserId AND U.Active = 1 AND U.Deleted = 0'

 -- Query 1 

SET @SelectQuery =
'SELECT 
    CD.[Name],
    CD.[ASIN],
    CD.[Category],
    CD.[Quantity],
    CD.[Total],
    CD.[Weight]
FROM CartDetails CD
INNER JOIN Cart C ON C.Id = CD.CartId 
INNER JOIN Users U ON U.Id = C.UserId'

SET @Query = @SelectQuery + @WhereQuery

EXEC (@Query)


--- Query 2

SET @Query = NULL 

SET @SelectQuery = NULL

SET @SelectQuery ='
SELECT  
    DCC.DiscountAmount, 
    DCC.DiscountCouponId
FROM DiscountCouponConsumed DCC
INNER JOIN Users U ON U.Id = DCC.UserId '

SET @Query = @SelectQuery + @WhereQuery

EXEC (@Query)

Upvotes: 1

Stix
Stix

Reputation: 475

Close to Dales answer, but joining @Users.

DECLARE @UserId UNIQUEIDENTIFIER = '96105876-AB55-4D28-A8DD-6BFEC9D38EF8';

DECLARE @Users TABLE
(
    Id INT,
    Active BIT,
    Deleted BIT
);

INSERT INTO @Users
(
    Id,
    Active,
    Deleted
)
SELECT Id,
       Active,
       Deleted
FROM Users
WHERE U.Id = @UserId
      AND U.Active = 1
      AND U.Deleted = 0;

SELECT CD.Name,
       CD.ASIN,
       CD.Category,
       CD.Quantity,
       CD.Total,
       CD.Weight
FROM CartDetails CD
    INNER JOIN Cart C
        ON C.Id = CD.CartId
    INNER JOIN @Users U
        ON U.Id = C.UserId;

SELECT DCC.DiscountAmount,
       DCC.DiscountCouponId
FROM DiscountCouponConsumed DCC
    INNER JOIN @Users U
        ON U.Id = DCC.UserId;

Upvotes: 0

Related Questions