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
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 Relatedhi!
I'm just wondering does anyone know how to create an sql command that can search word by word?
what i mean is like I have a product with name 'harry potter broom'.
I want an sql command where if i type only 'harry broom' this 'harry potter broom' product will show up.
Does anyone have any idea?
Here's my sql comand: (I'm using asp.net vb script do develop this system)
-------------------------------------------------------------------------
query = "select distinct * from product where " & _
"(pname like '%" & keyword & "%' or " & _
"pdesc like '%" & keyword & "%' ) and " & _
"(price >= " & price1 & " and price <= " & price2 & _
") and status <> 'out of stock' order by price asc"
-------------------------------------------------------------------------
Thank you.
Hi,
I am new to sql...
can anyone help me by giving the code to write a stored procedure to seperate a word.
For example:
If there is a string - Hi, how are you doing.
I want to seperate "Hi," and "How are you doing." using a stored procedure.
Thanks,
Avi.
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View 5 Replies View RelatedThis 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
I need to be able to search article in several tables in a MSSQL DB. What is the best way to do a word search? Are there and freebies out there or code examples?
Dave
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
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...
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
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..
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.
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.
Hi ,I would like to whether its possible or not.I have stored word document(Resume) in SQL server. Now i want to search for particular key word.for example . I want to list down all details of candidate which have ASP.Net mentioned in their resume. So please provide me any link / or article .
View 1 Replies View RelatedHi I was wondering if anyone can help me out here. I want to search a word in the database which has more than 80 tables is it possible to search all the tables at once for that word.
I am using SQL2005.
Thanks in advance.
hi guys....how do i search a word in a table using MS Access query? it should search all the columns in the table. thanks.
Regards,
Rohit
Hi,
I am trying to search a multi words with “like� function using the following statement in a S.P.:
@ITEM_desc nvarchar(10),
@QUOTFIL nvarchar (200),
@mfil nvarchar (500) = ''
SET @mfil = QUOTENAME('%' + @ITEM_desc + '%','''')
exec ('select * from ims.item where adesc like' + @mfil + @QUOTFIL)
Nrormaly one word can be send to SP for searching, is there any way to send multi word for the like function to search Like follows:
'%LOCAL%,%LIGHTS%'
Best regards
Hi All,
Please help, it is urgent
I am having a colume as image datatype, in which candidates resume is stored in binary format.
I have to search for particular words for eg. "ASP.Net, 2 Years" into that column
I have converted that column as varchar and then searching above two words(after spliting them by comma) into that string using CHARINDEX
DECLARE @i int, @j int
SET @i = select MIN(ResumeId) from table_name
SET @j = select MAX(ResumeId) from table_name
WHILE (@i <= @j)
BEGIN
DECLARE @Resume varchar(max)
SET @Resume = CAST((select Resume from table_name where ResumeId=100) as varbinary(max))
--code to search the particular word from @Resume
END
It is working fine, but it is very slow, taking 2+ minutes to scan 15000 records
How can I make it faster or is there any other way to search?
Thanks in advance
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 RelatedI 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?
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
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] ....
I'm designing a Job Recruitment Website, in which the admin person searches for the right candidate for the job using certain keywords . Each jobseeker will be uploading his CV (ms word doc) during registration .How can i search for keywords in the word documents. I just want the candidate reference once i found keyword match in the word docs.I heard about the indexing and blobs in sql server? But dont know much about it Are these the only solutions ?Is there any better approach for this ?Any help will be greatly appreciated
View 4 Replies View RelatedI 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 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
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
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
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
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 Relatedsp_MSforeachdb 'Select ''?'' as Dbname, SPECIFIC_SCHEMA, ROUTINE_NAME
From ?.INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME like ''%YourSPName%'''
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 RelatedHi 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.