SQL Stored Procedure Issue - Search Stored Procedure

This is the Stored Procedure below -> 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BPI_SearchArchivedBatches]
GO


/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/

/****** Object:  Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/2/2007 4:52:19 PM ******/

 

CREATE  PROCEDURE BPI_SearchArchivedBatches
(
 @V_BatchStatus Varchar(30)= NULL,
 @V_BatchType VARCHAR(50) = NULL,
 @V_BatchID NUMERIC(9) = NULL,
 @V_UserID CHAR(8) = NULL,
 @V_FromDateTime DATETIME = '01/01/1900',
 @V_ToDateTime DATETIME = '01/01/3000',
 @SSS varchar(500) = null,
 @i_WildCardFlag INT
)

AS

DECLARE @SQLString NVARCHAR(4000)
DECLARE @ParmDefinition NVARCHAR (4000)

 

IF (@i_WildCardFlag=0)
BEGIN

 SET @SQLString='SELECT    
  Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,
  Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,
  Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,
  Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,
  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,
  BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType
 
 FROM        
  Batch
 INNER JOIN
   BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID
 WHERE
  ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID ))
 AND
  ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID ))
 AND
  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime ))
 AND
  Batch.Archived_Status = 1 '

 if (@V_BatchStatus IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (Batch.Status_Code in ('+@V_BatchStatus+'))'
 end

 if (@V_BatchType IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))'
 end
 
END

ELSE
BEGIN
 SET @SQLString='SELECT    
  Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,
  Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,
  Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,
  Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,
  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,
  BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType
 
 FROM        
  Batch
 INNER JOIN
  BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID
 WHERE
  ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS ))
 AND
  ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID ))
 AND
  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <=  @V_ToDateTime ))
 AND
  Batch.Archived_Status = 1 '

 if (@V_BatchStatus IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (Batch.Status_Code in ('+@V_BatchStatus+'))'
 end

 if (@V_BatchType IS not null)
 begin
  set @SQLString=@SQLString + ' AND
  (BatchTemplate.Batch_Type  in ('+@V_BatchType+'))'
 end

END

PRINT @SQLString

SET @ParmDefinition = N'
 @V_BatchStatus Varchar(30),
 @V_BatchType VARCHAR(50),
 @V_BatchID NUMERIC(9),
 @V_UserID CHAR(8),
 @V_FromDateTime DATETIME ,
 @V_ToDateTime DATETIME,
 @SSS varchar(500)'

EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType ,
 @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

 

The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.

REQUIREMENT:

The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.

 

Please help me regarding the same.

 

Thanks in advance.

 

Sandeep Kumar

 

0
sandeep_kumar08
5/18/2007 2:16:33 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
1145 Views

Similar Articles

[PageSpeed] 47

So what problem exactly you are facing.... though i would recommend that you should build a dynamic query engine which will use the values ( criteria values ) filled by user and creates a where clause and so on.

 

Satya


Satya
------------------------------
Mark as answer if you think
this helped you Yes
0
satya_tanwar
5/20/2007 5:22:00 PM

This is a similar technique that I have blogged about before:

http://sqladvice.com/blogs/dpenton/archive/2004/09/08/4233.aspx


David L. Penton
"Mathematics is Music for the mind, and Music is Mathematics for the Soul. - J.S. Bach"
0
davidpenton
5/21/2007 12:26:52 AM
Reply:

Similar Artilces:

SQL Stored Procedure to MySQL Stored Procedure Conversion
Hi, I am trying to convert a stored procedure written for sql to one that will work in mysql. I understand that I have to set the variables as IN , but I don't know what to do with the rest of the code. The following is the sql stored procedure that I am trying to convert to msql stored procedure: CREATE PROCEDURE Register_User (@userName Varchar(50), (@PassWord Varchar(50), (@FirstName Varchar(50), (@LastName Varchar(50) ) AS IF EXISTS(SELECT u_ID FROM User_ID Where u_UserName=@UserName) RETURN -1 ELSE INSERT User_ID( u_User...

How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you.    Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...

MS SQL stored procedures inside another stored procedure
Hi,  Do you know how to write stored procedures inside another stored procedure in MS SQL.   Create procedure spMyProc inputData varchar(50) AS  ----- some logical    procedure spMyProc inputInsideData varchar(10) AS    --- some logical   ---  go ------- What exactly are tou trying to do? ***********************Dinakar NethiLife is short. Enjoy it.*********************** Like Function, you can have one function inside another another function. Function1 () {    Function2() } How about store procedure ? spProc1 { &n...

sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

How to store XML into SQL with store procedure?
Hi all, I got this xml and I want to store it into SQL server (with SQLXML), I have a table with all the fields so I want to map the data in XML to all the fields in DB. - Is store procedure the best way to do it? Are there any better way to do it? - Do you have any similiar examples? - Any good articles on the net that related to this topic? Thanks a lot and happy new year! Edwin I am a newbie ----------- <quote symbolstring="QMCI" datatype="equity" delaymin="15" time="2003-09-12T14:43:05-0400"> <key> ...

Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')  but I also have a second stored procedure that needs to make reference to&n...

Copy stored procedures from SQL to SQL DE?
Is there an easy way to copy all stored procedures from an SQL server to en SQL Desktop Engine or do I have build all stored procedures by hand?Johan TheunissenMCPD, MCSE, MCTS BizTalk 2006==============================Please mark the most helpful reply/replies as "Answer". You can use the SQL Server import/export wizard, or you can generate a script of them using SQL Server Management Studio. Thanx, In the mean time I copied them manually (copy-paste). So I think a seperate script would be usefull when you have 100+ stored procedures. Or have the script available.Johan Theu...

Linq to SQL and stored procedure with dynamic sql
I have a stored procedure like create proc test as ..... set @sql = 'select ' + .... + ' from ....' exec(@sql)The select list is dynamically (in fact, pivot table). However, after drag the stored procedure to dbml design view and called, the stored procedure only return an integer.How to get the result set of the stored procedure when using dbml?Thanks,  Is this the only SELECT statement that @sql gets?  I created a simple example and it performed as I would expect returning a complete recordset....I'm wondering if your stored proc is returning 2 record se...

LINQ in a SQL CLR Stored Procedure (SQL 2008)
Hey all, i'd like to use LINQ in a CLR Stored Procedure in SQL 2008. The problem is, i can't add a reference to System.Xml.Linq. How do i make that happen? my SQL project is targeting .net 3.5, and i have SP1 installed.I saw a blog about this somewhere recently but now i can't find it. so if you can point me to something, that would be awesome.thanks,-b I googled and found this blog http://oakleafblog.blogspot.com/2007/09/problems-using-stored-procedures-for_08.html  Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth) That does...

How to run a sql stored in a variable inside a stored procedure ?
Hi, I am having a situation that needs a sql to be run in a stored procedure, stored in a local variable. Basically the where clause is constructed based on certain criteria. If anybody knows a workaround for this please let me know. Thanks in advance Ganesh Salem After adding your servername to sysservers database and creating a remote account, you can use remote procedure calls to the same server itself. For more information on creating a remote user account and adding your servername to sysservers system table please see your software documentation. Gokhan DEMIR ...

Sql Query String verses SQL Stored Procedure
I'm working on the performace of my pages. I want to allow large datasets and get the best performance I can. I've read that Stored Proceedures(SP) are faster than passing a Query String(QS). The questions I have are: Given the code of this page, will I see an improvement in performance using a SP? If yes, how do I allow for just SOME of the variables being passed? Users are nor required to enter all parameters. As far as I know, if you create a SP in SQL Server and don't pass ALL of the parameters it expects, you'll get an error. Also, someone mentioned that string concatinati...

Problem with SQL DataSource and Stored Procedure
I'm trying to call a Stored procedure as my SQLDataSource update command but I'm getting the following error: "Procedure or function ProcessTravelPolicy has too many arguments specified." The Update Command looks like this:                     UpdateCommand="ProcessTravelPolicy" UpdateCommandType="StoredProcedure">                     <UpdateParameters>        &n...

Stored Procedure vs. SQL As Datasource
Hi all. Just a quick question about some behaviours I am seeing with a datawindow I've developed. I was handed an existing report for an application and asked to make it more efficient and run faster. The existing report is an external that derives its data from a SQL Server 7 stored procedure and it ran like a pig. The stored procedure creates a temp table, selects data into it, and then supplies that data to the external. Not being a huge fan of stored procedures to begin with, I rewrote the SQL for the report's data just as a SQL Select in a freeform dw and now the ...

Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...

Web resources about - SQL Stored Procedure Issue - Search Stored Procedure - asp.net.sql-datasource

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

Are patients charged drastically different prices for the same medical procedure? - Videos - CBS News ...
Patients are often charged drastically different prices for the same medical procedures and research may link later bedtimes with gaining weight. ...

Vin Scully To Miss Dodgers Postseason After Medical Procedure
... , who has been calling Dodgers games since the Truman administration, will miss the team’s playoff run as he recovers from a medical procedure. ...

FDA finds flaws in Theranos' nanotainers and quality control procedures
... to Theranos' blood testing technology comes from the US Food and Drug Administration, which found flaws in the company's quality control procedures. ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Hall Of Fame Broadcaster Vin Scully Has Medical Procedure, Will Miss All Of 2015 Postseason
While the Dodgers will be in the 2015 MLB postseason, their Hall of Fame broadcaster, won't. Here's details on Vin Scully.

US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor ...
US AIRPORT SECURITY CONCERNS DHS to heighten procedures in light of Russian jet bombing theory, poor test performances by TSA screeners

Concession: Ryan promises Freedom Caucus he’ll delay discussion of reforming procedure for removing the ...
Compromise. Like I said this morning, it sounds like they did a little horse-trading at their summit. Ryan wants them to give up their right ...

Resources last updated: 11/26/2015 1:04:46 PM