CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
TlonXP
TlonXP

Reputation: 3485

A simple query in stored procedure

Need to make a stored procedure that will do a simple search on a table, plus have 3 parameters, the first two are a date range, the third is if different from NULL will be included in the WHERE, if equal to NULL then not seek that value, as I can do this search?

Upvotes: 4

Views: 958

Answers (8)

mehdi lotfi
mehdi lotfi

Reputation: 11601

Where @parameter1 = ColA 
AND @parameter2 = ColB 
and ISNULL(@parameter3, ColC) = ColC

Upvotes: 1

gngolakia
gngolakia

Reputation: 2216

Imagine that You're having a transaction table where you store transaction Number And Transaction Date as shown Below.

CREATE TABLE [dbo].[TrnMast](
    [TrnNo] [numeric](10, 0) NOT NULL,
    [AcYear] [int] NOT NULL,
    [Comp_Code] [varchar](5) NOT NULL,
    [InvNo] [varchar](20) NULL,
    [TrnDate] [datetime] NULL,
    [P_Code] [varchar](5) NULL,
    [Amount] [money] NULL,
    [Remark] [varchar](50) NULL
 CONSTRAINT [PK_TrnMast_1] PRIMARY KEY CLUSTERED 
(
    [TrnNo] ASC,
    [Comp_Code] ASC,
    [AcYear] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Now Look at stored procedure with 3 parameters having two date range paramaters and TrnNo Below.

CREATE PROCEDURE [dbo].[SP_TrnMast] 
    @FTrnDate SmallDateTime = Null,
    @TTrnDate SmallDateTime = Null,
    @AcYear Int
AS
Begin   
        Select * From TrnMast           
        Where (@FTrnDate Is Null Or TrnMast.TrnDate >= @FTrnDate) 
          And (@TTrnDate Is Null Or TrnMast.TrnDate <= @TTrnDate) 
          And TrnMast.AcYear = @AcYear 

End

Upvotes: 0

Ankit
Ankit

Reputation: 690

try this..
Create Prucedure test

@p1 int,@p2 int,@p3 nvarchar(50)

As

Begin

If @p3 is null

Select * from table where field1=@p1 and field2=@p2

Else
Select * from table where field1=@p1 and field2=@p2 and field3=@p3
End

Sorry for improper formatting this is because i am sending the answer from a cell phone

Upvotes: 1

Kip Real
Kip Real

Reputation: 3469

I am using the adventure works database.

 CREATE PROCEDURE upContactDetails
        @FirstName  [nvarchar](50),
        @LastName   [nvarchar](50),
        @MiddleName [nvarchar](50)
    AS
    BEGIN
    SET NOCOUNT ON;
    SELECT     Title
             , FirstName
             , MiddleName
             , LastName
             , EmailAddress
    FROM       Person.Contact
    WHERE (@MiddleName IS NULL or MiddleName = @MiddleName) 
          AND FirstName = @FirstName
          AND LastName  = @LastName 
    END
    GO

Upvotes: 0

billinkc
billinkc

Reputation: 61269

The general form of what you are attempting to perform is a dynamic query. See the solution in this problem update statement not executing

Upvotes: 0

ravenous
ravenous

Reputation: 1

Try the following. Note that if Column3 could be null then you need to use the isnull() function on the column name as well as you can't compare null values. So if the data type of Column3 is int then you could use: and isnull(Column3, 0) = isnull(@param3, isnull(Column3, 0)).

create procedure Test
    @param1 varchar(50),
    @param2 varchar(50),
    @param3 varchar(50) = null
as
begin
    select *
    from Table1
    where Column1 = @param1
    and Column2 = @param2
    and Column3 = isnull(@param3, Column3)
end

Upvotes: 0

Raja Reddy
Raja Reddy

Reputation: 782

Yes! you can do that by passing the three variables to the host program and write a customized CURSOR SELECT using them. Then open the cursor and throw the resultset. Your search results are now ready to use. If needed syntactical help, revert with the host language and specifications you are using. Do post the challenges and solution you got for the same, may help others.

Upvotes: 0

Phil Sandler
Phil Sandler

Reputation: 28046

WHERE @parameter IS NULL or field = @parameter

Upvotes: 0

Related Questions