vendredi 31 mars 2017

IF END IF IN SQL STORED PROCEDURE

I am trying to create a advanced search query in which user's name,status and role are filtered at the same time (one or more can be selected). Given below is the code for the SQL Stored Procedure I want a way to have IF and END IF, so that the where condition in all the cases are mutually exclusive Currently if the first name is null, then the rest of the conditions fail

USE [qcsl]
GO
/***** Object:  StoredProcedure [dbo].[UserAdvancedSearch]    Script Date: 3/31/2017 3:25:28 PM *****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        Mayank
-- Create date: 2017-03-29
-- Description:   
-- =============================================
ALTER PROCEDURE [dbo].[UserAdvancedSearch]
    -- Add the parameters for the stored procedure here
    (
    @user_name varchar(50) = NULL,
 @user_last_name varchar(50) = NULL,
    @role_id varchar(50) = NULL,
    @user_status varchar(50) = NULL,
 @query varchar(2000) = NULL output
 
   

    )
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

SET @query = 'select * from user_main
where 
'IF (@user_name is not null ) SET @query = @query + '
(user_first_name like '''+@user_name+''' OR user_first_name IS NULL)
'IF (@user_last_name is not null ) SET @query = @query + 'AND
(user_last_name like '''+@user_last_name+''' OR user_last_name IS NULL)
'IF (@user_status is not null ) SET @query = @query + 'AND
(user_status = '+@user_status+') 'IF (@role_id is not null) SET @query = @query + '
AND
(user_role_id_ref = '+@role_id+')'
EXEC(@query);
END

Aucun commentaire:

Enregistrer un commentaire