Geek Answers Handbook

Simple request in stored procedure

It is necessary to create a stored procedure that will perform a simple search in the table, plus have 3 parameters, the first two are a date range, the third if it is different from NULL, will be included in WHERE, if it is NULL then do not look for this value, as I can do this search?

+4
source share
8 answers
Where @parameter1 = ColA AND @parameter2 = ColB and ISNULL(@parameter3, ColC) = ColC
+1
source

try it.
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 the incorrect formatting, because I am sending a response from a mobile phone

+1
source 
 WHERE @parameter IS NULL or field = @parameter
0
source

Yes! you can do this by passing 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 for use. If necessary, syntax help, return with the host language and specifications that you use. Provide the problems and solutions you received for them that can help others.

0
source

Try the following. Note that if Column3 can be empty, you need to use the isnull () function for the column name, and you cannot compare null values ​​either. Therefore, if the data type of Column3 is int, you can 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
0
source

The general view of what you are trying to execute is a dynamic query. See the solution to this problem update instructions.

0
source

I am using an adventure 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
0
source

Imagine you have a transaction table in which you store the 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 the stored procedure with three parameters that have two date range parameters 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
0
source

More articles:


All Articles