Stored Procedure - Search By Two Or Any One Of Criteria
Apr 7, 2015
I have a store procedure that search by Firstname and Lastname. I want it search by either both (Firstname and Lastname) or any of them. For example if only FirstName passes to it shows all the record with that Fistname. Currently I have to pass both Firstname and Lastname to my store proc to get the result.
This is my stor proc:
USE [CustomerPortal]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CSA_Search_Customer_By_Name]
[Code] ....
View 4 Replies
ADVERTISEMENT
Mar 29, 2008
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies
View Related
Dec 13, 2006
Hello All,
I was hoping that someone had some wise words of wisdom/experience on this. Any assistance appreciated... feel free to direct me to a more efficient way... but I'd prefer to keep using a stored proc.
I'm trying to pass the selected value of a dropdownlist as a stored procedure parameter but keep running into format conversion errors but I am able to test the query successfully in the SQLDatasource. What I would like to do is this: select * from tblPerson where lastnames Like "A%" . Where I pass the criteria after Like. I have the values of the drop down list as "%", "A%", "B%", ....
I've been successfully configuring all of the other params, which includes another dropdown list (values bound to a lookup table also)... but am stuck on the above...
Thank you for any assistance,
View 3 Replies
View Related
May 18, 2007
This is the Stored Procedure below ->
SET QUOTED_IDENTIFIER ON GOSET 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
ELSEBEGIN 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 GOSET 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
View 2 Replies
View Related
Dec 4, 2013
I am very new to sql programming. I have a database with 15 columns. At this moment I do this to get all rows containing the year 2013:
WHERE DATEPART (yyyy, DownloadDate) = 2013 .
But I also want to add a criteria so that I can have all rows where DownloadDate contains 2013 AND WHERE IsRead(BIT) contains NULL. I tried this:
WHERE DATEPART(yyyy, DownloadDate) = 2013 AND IsRead = null .
But this gave me nothing!
View 3 Replies
View Related
Jul 30, 2007
I have four criterias in my .aspx page. They are "First Name, Last Name, Title, Year". I have a Book and an Author table. The Author table would contain all the author's information and the Book table contains all the books information such as title, publisher, subject, and so on. So here's what I'm trying to do.
I want to write a transaction statement that will query the four criterias above if the criteria textbox is not black. So for example, when the user click the submit button, all the four criteria fields are filled except Year. That means the query would search the Author and Book tables for "First Name, Last Name, Title" but not "Year" for any potential matches. I also wanted to use "Like" instead of "=" for a wider search.
Actually I'm trying to create a store procedure that will accept those four criterias and search the tables based on those criterias.
Any help is appreciated.
View 11 Replies
View Related
Feb 3, 2007
i am working for a Library Project. in that project i want to search Book by putting book content. in book content there could be single quatation
e.g William's. when ever i put single quatation my string type is terminated and my query is not executed successfully.
select Book_ID,Title,Auther from book_details where Contents like '%xyz%'
here xyz is my object name.
if i put William's in xyz it does not work.
plz tell me how to solve this problem.
Jasim...
New Delhi, INDIA
View 4 Replies
View Related
May 31, 1999
dear friend,
the problem i am finding is moreover a concatenation problem
what i did is that have stored procedure which is accepting any character and i have to show all the records where this character is existing
it is like this only
********************************
name1 like '% @str %'
but then it is not treating the str as a variable and treating it as a string so if you have any solution please let me know early
please give the answer with example
waiting for reply
ashish bhatnagar
View 1 Replies
View Related
Mar 20, 2012
I am working on SQL Server in VB 2008. I have a table 'Records' having 8 columns. I have a search page where I can choose 5 different parameters to search as 'Category' , 'Name' , 'Date' etc.
I can successfully search with a single criteria selected either Category Name Or Date. But I want to create a single SQL command that can search my 'Records' table for either two or all the parameters depending on the selections made by the user.
View 5 Replies
View Related
May 26, 2004
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
View 9 Replies
View Related
Jun 20, 2014
Say I have a query like
DECLARE @ID UNIQUEIDENTIFIER, @SOMEDATE DATE
SELECT * FROM myTable WHERE ID = @ID AND DATEFIELD=@SOMEDATE
I want to pass values to @ID and @SOMEDATE, such that it meets the WHERE criteria for all values in the respective fields.
What parameter value should I pass such that all values are selected? In the actual SP, I have uniqueidentifier, varchar and date parameters.
View 2 Replies
View Related
Oct 21, 2014
My selection criteria is as follows:
where content like '%EditLiveJava%'
or content like '% Sys__%' ESCAPE '_'
or content like '%<div class="row"/>%'
or content like '%<a href="" title=""%'
or content like '%cmsprod%'
or content like '%Error processing inline link%'
or content like '%see log for stack trace%'
I output the content field if the search is true but would like to also output which specific 'like' has been found.
Can I do this in the one pass or do I have to read the database separately for each condition?
View 3 Replies
View Related
Jul 23, 2005
I am trying to create a query that will show me who is phoning who in anorganisation from available Telephone Billing information. I am creating aMSAccess 2000 database with a few few tables, two of which are:TableMembers: (containg fields Refs, DateCreated, MembershipNo,OfficeLocation ...NB: Refs has a Primary Key - No Duplicates)TablePeople: (containing fields: Refs, Name, Addr, TelHome, TelWork,TelMobile & TelFax)TableTelBills: (containing fields: Refs, TelNo, DateCalled, Duration,TelType)I am trying to create a query that will use a simple searching criteria eg.,Like "*" [Enter the Tel No or part Tel No to search:] & "*"to search all the Tel fields in the TablePeople and TableTelBills (TelHome,TelWork, TelMobile, TelFax and TelNo) but am running in difficulties.I start by creating a query and adding the tables TablePeople andTableTelBills and TableMembers.I use the Refs from the Table Members as a base criteria but do not know howto create criteria that will search all Tel fields at once!I would appreciate any and all help people!Jan
View 2 Replies
View Related
Dec 14, 2007
Hi..I am working With Asp.net using Vb for a Music Project.i have the requirment for serach songs according to catagory wise(Singer,Actor,Music Director, etc)
i have code like this...
If Not Page.IsPostBack Then searchword.Text = Request.QueryString("SearchWord") Response.Write(Request.QueryString("SearchWord")) Response.Write(Request.QueryString("Language")) Response.Write(Request.QueryString("SelectedCategory")) 'Response.Write(Request.QueryString("Query"))
Dim str As String = "select * from Music_SongDetails where Music_Clip_Id>0 and Music_Clip_Lang='" & Request.QueryString("Language") & "'"
If Request.QueryString("SelectedCategory") = "Song" Then str = str & " and Music_Clip_Name like '%" & Request.QueryString("SearchWord") & "%'" ElseIf Request.QueryString("SelectedCategory") = "Movie" Then str = str & " and Music_Folder_Name='" & Request.QueryString("SearchWord") & "'" ElseIf Request.QueryString("SelectedCategory") = "Actor" Then str = str & " and Music_Clip_Actor='" & Request.QueryString("SearchWord") & "'" ElseIf Request.QueryString("SelectedCategory") = "Actress" Then str = str & " and Music_Clip_Actress='" & Request.QueryString("SearchWord") & "'" ElseIf Request.QueryString("SelectedCategory") = "Music Director" Then str = str & " and Music_Clip_MusicDir='" & Request.QueryString("SearchWord") & "'" ElseIf Request.QueryString("SelectedCategory") = "Singer" Then str = str & " and Music_Clip_Singer='" & Request.QueryString("SearchWord") & "'" ElseIf Request.QueryString("SelectedCategory") = "All" Then str = str End If...........
I need to write this code using Store Procedure....
Kindly Help me out
Thanks in Advance
View 3 Replies
View Related
Mar 29, 2008
hi iam working with search for the first time,in the GUI i have 3 fields Audit Name,Year,Audit ID.After enetering any or all these details and pressing submit i must show the gridview with complete details.
I have problem with the procedure for searching depending on the details given,here is the procedure:
Select Aud.Ad_ID_PK,Aud.Audit_Name,Ind.Industry_Name,Cmp.Company_Name,Pla.Plant_Name,Reg.Login_Uname,Aud.Audit_Started_On,Aud.Audit_Scheduledto,Aud.Audit_Created_On from
Industry Ind,
Company Cmp,
Plant Pla,
RegistrationDetails Reg,
Audits Audwhere Ind.Ind_Id_PK =Aud.Audit_Industry and
Cmp.Cmp_ID_PK =Aud.Audit_Company and
Pla.Pl_ID_PK =Aud.Audit_Plant and
Reg.UID_PK =Aud.Audit_Engineer and
Ad_ID_PK in (select Ad_ID_PK from Pcra_Audits) and
year(Audit_Created_On)=year(@YrofAudit)
order by Audit_Created_On DESC
iam getting the data when the user enters year but i want the procedure where i can check for the three fields(Audit Name,Year,Audit ID) which user is entering.If he enters only one field it must check which field is enetered and must get the data.if more than one field is entered then all the conditions must be checked and must get the details.please help me..........
Its very urgent..Plz...
View 2 Replies
View Related
Aug 27, 2002
I am an inexperienced SQL programmer and need to write a SP which will be used to search a Call table within a Call Logging System used to log support calls for my company. The search criteria are fields like Call Reference No, Logged By, Call Status etc
The problem I have is that individual or a combination of these criteria may be used to search on -can anyone advise how I can write a SP which will take account of the possible different combinations of parameters which may be passed to the Stored Procedure
i.e. if 2 fields are populated during the search and 4 are empty
Thanks,
Stephen
View 1 Replies
View Related
Nov 27, 2007
Hi to All,
I am new to Prpgramming, I need to create a Stored Procedure for my requirement here is my requirement,I have two tables from those I need to get data. Table_One consists UserID,Name,Address,ContactInfo,EmailID, and Table_two consists UserID,CitizenShip,HieghestEducation,ExpectedJob
But I need get data search report Name,EmailID,HiehestEducation,ExpectedJob. User should able to wile card search. Pls help me in this regards.
Thanks in Advance..
View 1 Replies
View Related
Oct 9, 2006
Hi,
Could anybody please tell me how I can search for a stored procedure in SQL Server 2005? I know the name of the stored procedure and I want to find in which database that stored proc is located/stored and I want to see the code of it. (I have all the necessaary previleges.) Please tell me how I can I do this.
Thanks in advance.
Regards,
Ram.
View 7 Replies
View Related
Apr 30, 2008
Hello
I have a text box on my web form where the user can enter multiple comma delimited search words. The value from this text box goes to my search stored procedure in the form of a search string.
I am able to parse this comma delimited search string and get the words into a temp table.
If there are 2 words in the temp table then this is the sql that I want
select * from Items
where (description like word1 or tagnumber like word1 or user like word1)
and (description like word2 or tagnumber like word2 or user like word2)
description,tagnumber, user or the fields of the Items table.There could be any number of words in the search string.
Any ideas of how to get this done with any number of search words being matched against number of column/s.
Any help regarding this is appreciated.
Thank you.
View 4 Replies
View Related
Nov 5, 1999
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric
or non-printable characters. For instance, if I have a table with a 20 character name column with names with
beginning ranges from A-Z, why doesn't the following return any rows:
select * from table where name < CHAR(126).
In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and
A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does
not include characters outside the alphanumeric range. Any ideas ?
View 4 Replies
View Related
Oct 9, 2006
HIi want to create a procedure, basically i'll have 4 input parametres (Title, Category, ReleaseClass and BuyPrice)title will come from a textbox, the rest from dropdownlistsif a user enters a title, selects a ReleaseClass and BuyPrice, But doesnt select a category, all categories should be returned - You know what i meanhow do i go about this - Any Ideas??Cheers!!!
View 7 Replies
View Related
Feb 21, 2007
I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.
I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?
View 5 Replies
View Related
Mar 7, 2008
Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you
View 4 Replies
View Related
May 5, 2008
hi
can i search a word in my all stored procedure
not in system stored procedure
only in my stored procedure
like i need to search ' Getdate() ' in my all stored procedure
TNX
View 8 Replies
View Related
May 28, 2015
Say I want to search for a range of account numbers but only which are active. After I set my field for A (active) this field shows in my results, I dont want it to.
In Access you can easily just uncheck that field in design view, but how do I do it in sql?
View 4 Replies
View Related
Sep 8, 2006
Hi Guys, I hope someone here can help me. I am writing a stored procedure that simply searches for a given value across multiple databases on the same server. So far all well and good.Now, the problem is if the user types in more than one word into the search field.I have put a partial section of code here, there is obviously more, but most of it you wouldn't need to see. SELECT @sql = N'SELECT @count = COUNT('+ @dbname +'.dbo.orders.order_id) FROM '+ @dbname +'.dbo.orders '+
N' INNER JOIN '+ @dbname +'.dbo.customer ON '+ @dbname +'.dbo.orders.cust_id = '+ @dbname +'.dbo.customer.cust_id '+
N' WHERE '+ @dbname +'.dbo.customer.forename LIKE ''%'+ @SearchStr + '%'' OR '+ @dbname +'.dbo.customer.Surname LIKE ''%'+ @SearchStr + '%'''
EXEC sp_executesql @sql, N'@count int OUTPUT', @count = @results OUTPUT Now this code works perfectly well if the user only enters one word, however i need to make sure that the Stored procedure will function if the user enters 2 words, such as John Smith. I need the procedure to search the forename for 'john' & 'Smith' and the same for the surname. It should also work if the user type 'John Michael Smith' - if you understand.I am really struggling with this one.Thanks in advance.Darren
View 2 Replies
View Related
Jun 7, 2007
I am trying to inject dynamically generated text into a Sql2000 stored procedure. What am I doing wrong?A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1. The routine splits the entry and creates the below string.Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%'
I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @VisitorKeywords.
PROCEDURE dbo.KWsearchAS SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( @visitorKeywords ) AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name
I am wanting the resulting WHERE portion to be:
WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1)
Thank you
View 10 Replies
View Related
May 18, 2008
Hi I want to give the user the ability to search based on a number of criteria, however i do not know how to build my statement properly this is what i have so far;
ALTER PROCEDURE [dbo].[stream_StoreFind]
-- Add the parameters for the stored procedure here
@StoreName varchar(250),@subCategoryID INT
AS
SELECT Stores.StoreName ,StoreCategories.storeIDFROM Stores INNER JOIN
StoreCategoriesON
Stores.storeID = StoreCategories.storeID INNER JOIN
SubCategories ON
StoreCategories.subCategoryID = SubCategories.subCategoryID WHERE
My problem is how will i pass the parameters into the statement, taking into fact that sometimes they may be optional. Thank you
View 12 Replies
View Related
Apr 21, 2005
Hi,
I have a MSDE datatable which containes Documents and a link to thet document.
Is there any way i can search the doument name colunm using a search box from my web site and display the results. I have seen all these "stored procedure" discussions and just wonder whether it would be easy to search using the stored procedure?
I haven't got much experience using MSDE and it seems really a big task for me.
Any help/suggestions would be highly appreciated.
Thanks, RAJESH
View 4 Replies
View Related
Oct 6, 2005
My scenario is I have a web form with a textbox and a button.Once I enter a string and hit submit button, my stored procedure will have to return the result set.So if my search string is "text book title", then I have to execute the query like :select * from tab1 where col1 like '%text%" or col1 like '%book%" or col1 like '%title%"The problem here is I will never know how many words will be entered to search. So I have to make the statement dynamic.How can I do this in a stored procedure? Any help will be appreciated.Thanks.
View 6 Replies
View Related
Nov 6, 2012
sp_MSforeachdb 'Select ''?'' as Dbname, SPECIFIC_SCHEMA, ROUTINE_NAME
From ?.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME like ''%YourSPName%'''
View 1 Replies
View Related
Jun 25, 2015
I would like to search for a particular stored procedure written by a developer. I know the name of the procedure but in which db is it residing in. There are 40 databases in this SQL 2008 instance. I search on the name column in sys.all_objects table and it does not return anything. I end up querying sys.procedures on each database to locate the procedure. Is there a system table/view that I can query to look for a procedure, instead of querying sys.procedures on each database one by one?
View 1 Replies
View Related
Jun 14, 2006
Hi I am new to this forum. I have a stored proc that conducts a search based on a number of parameters entered by the user. The way I am currently building the procedure is the following, this is one segment of the if/else structure:------------------------------------------------------------------------------------------------------------------------------
--If latitude, longitude or distance are null and ProjectID and AnalysisTypeID are not NULL
ELSE IF (@i_Latitude IS NULL OR @i_Longitude IS NULL OR @i_distance IS NULL) AND @i_ProjectID IS NOT NULL AND @i_AnalysisTypeID IS NOT NULL
BEGIN
SELECT Distinct Projects.ProjectName,
Projects.ProjectNumber,
Projects.ProjectID,
Tasks.TaskID,
Tasks.TaskDesc,
Tasks.TaskName,
Locations.LocationID,
Locations.LocationName,
Locations.LocationLatitude,
Locations.LocationLongitude,
Locations.LocationComments,
AnalysisType.AnalysisTypeName
FROM Projects INNER JOIN
Tasks ON Projects.ProjectID = Tasks.ProjectID INNER JOIN
Locations ON Tasks.TaskID = Locations.TaskID INNER JOIN
Analysis ON Tasks.TaskID = Analysis.TaskID INNER JOIN
AnalysisType ON Analysis.AnalysisTypeID = AnalysisType.AnalysisTypeID
WHERE Analysis.AnalysisTypeID=@i_AnalysisTypeID AND Analysis.AnalysisIsDeleted='False'
AND Tasks.TaskIsDeleted='False' AND Locations.LocationIsDeleted='False' AND Projects.ProjectID=@i_ProjectID AND Tasks.TaskIsComplete='True'
ORDER BY Locations.LocationID
END
-------------------------------------------------------------------------------------------
So basically I have the parameters being passed in as having a value or null. I have an if/else structured that determines which code to execute based on the parameters value. As you can imagine the stored procedure is getting very large and hard to maintain because every new parameter doubles the if else structure, but I am not sure how to redesign it to be more manageable. Any help with this would be extremely appreciated.
Thanks,
Tony.
View 6 Replies
View Related
