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?
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 RelatedMy existing ASP 1.0 site keeps getting hacked using SQL injections. I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search.
I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck. Getting either errors or double LIKE statements. Need some help.
string[] SqlKWSrch;
SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ','); int AStop = SqlKWSrch.Length; int i = 0; foreach( string a in SqlKWSearch ) { if (i <= AStop) { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND "; } else { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' "; } i++; }
1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure
GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion.
PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1
RETURN
Thank you
I have SQL table (tblUsers) and one of the fields holds the email address. I want to step through each record build a multiple email string to send to a lot of people. It would look like this
Str_email = Me@hotmail.com;Andy@Hotmail.com;Fred@Hotmail.com
I then want to pass Str_email back to an asp.web page
Can this be done in a stored procedure ?
Hi I have a table that has several related keys into other tables with the main table looking like
System ID buildingID roomID ColorID
1 prikey 3 4 5
2 2 1 3
building table looks like
buildingID building name
1 prikey Flower bld
2 Lab bld
3 Crafts bld
4 Eng bld
room table looks like
roomID room name
1 prikey 100
2 101
3 102
Color table looks like
colorID Color name
1prikey red
2 blue
3 grey
4 white
I will need to pull the data from the system table based building name, room name and color. If the parameter is zero I will not filter on that parameter so will only be filtering on one parameter for each time the procedure is called. Any ideas on a simple query, thanks!
OK, to simplify some annual auditing of DB users (not the SQL logins,) I'm trying to craft a stored procedure that the customer on the server (they're the only customer on this particular server) can run to get a listing of all DB users and what roles they have.
I've got a query that returns this for the currently selected DB, so that part's done.I can use SP_MSFOREACHDB to run it against each DB, with the results going into a temp table to make it easier to copy/paste into an Excel file.What I want to do, and can't seem to see how, is wrap the whole thing in yet another SP of my own, with an EXECUTE AS so that the customer doesn't need sysadmin or any special privileges on the server. When I do this, it runs, but only against master.
Now, from digging it looks like you can't have an "insert #temptable exec sp_whatever" inside another SP. I'd like to avoid dynamic SQL, and while I know there are problems with MSFOREACHDB, it'll work for what we need.how to turn a user created SP, into a system SP so it can be run regardless of the DB you've selected, so at least there's that.
Is it possible to force a build to fail when a stored procedure in a project calls another stored procedure with one or more required parameters missing. E.g.:
CREATE PROCEDURE [App].[ServiceUser_Save]
@userID int-- Param #0
,@serviceuserID int-- Param #1
,@version int-- Param #2 etc...
And then in a separate stored procedure we have the following
CREATE PROCEDURE [Admin].[CreateMiscellaneousData]
@customerIDint,
@serviceIDint,
@fullURL nvarchar(255),
@apiUserPwd nvarchar(255)
AS
BEGIN
...
EXEC@return_value = [App].[ServiceUser_Save]
@userID = 1,
@serviceuserID = 0, etc...
Note there is no value passed for the @Version parameter.
What I want is the build to fail because of the missing parameter on the call to ServiceUser_Save in the Create_MiscellaneousData stored procedure.
How can I achieve this?
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
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! Maybe I
ask simple question. But for me it’s not so. The matter’s I plan organize
search at my website. It has catalog of firm’s and good’s in SQL Server. I plan
to build dictionary table with two fields’ search_item and link. But problem is
that I should put all search variant’s in field search_item(name of firm, name
of boss, name of good etc.) Maybe there’s another concept to build search?
Thank’s for any answer.
Andrey.
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 RelatedHello,
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] ....
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 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.
Few users are getting ‘could not find stored procedure error’ for several SPs in SQL 2012 even these SPs exist in DBs. if it’s a bug in SQL 2012 and its suggested workaround.
View 12 Replies View RelatedI required to build the search feature for my application which contains combination of at least 20 search fields e.g firstname, lastname. date of birth, sign up date ,etc... I am just wondering what is the best way to do it ,should I create stored procedure with 20 input parameters or should I build it based on each search fields. I need to provide the search results via web services. Could anyone help me?
Thank you
Hi,
I'm working on a new site with a big number of future concurrent visitors so performance is very important. We're working on a search function with which users can search for multiple keywords in a single table. My .NET application consults a SQL Server 2005 Stored Procedure to lookup the information. The stored procedure builds up a dynamic SQL string with which the table is queried.
An example:
User searches for 'car airco'. Alle records with the words car and/or airco in specified columns should show up. This works. The query would be
SELECT Col1, Col2 FROM Table1 WHERE (Col1 LIKE '%car%' OR Col2 LIKE '%car%')OR (Col1 LIKE '%airco%' OR Col2 LIKE '%airco%')
As I mentioned before performance is a hot issue in this project. The problem with the stored procedure is that it can't be precompiled by SQL Server (dynamic SQL string). Is there a way to search for multiple keywords without losing the precompile behaviour of SQL Server Stored Procedures?
Kind regards,
ThaYoung1!
Hi,
I have a repeater control which I populate with search results from SQL Server.
But I can't figure out how to cope with users who submit multiple search items and still use my stored procedure. Is this possible or do you have to build the query with a StringBuilder and execute it manually?
I'm using a stored procedure with parameters:
input parameters <-- PageSize & CurrentPage
output parameter --> TotalRecords
Am using a temporary table to store all records before Select-ing those required for the particular page.
If I compose the query manually then I can't figure out how to get TotalRecords back as a return parameter. Would appreciate help on this one.
Am hoping that stored procedures can cope with an unknown number of parameters.
Hello
I wonder if someone could suggest a way to obtain the following. Using SQL Server 2005 I want to create some stored procedures. I want to query the DB with various filter arguments and combinations of these. One way would be to create one stored procedure for each function signature. However, as the number of combinations of filter is large, if possible I'd rather have a generic input to the each stored procedure that corresponds to the entire WHERE clause' search condition.
The stereotype behavior I'm looking for is:
SELECT myField
FROM myTable
WHERE @mySearchCondition
Does any one have some good suggestion, code samples and/or links?
Kind regards
Jens Ivar
i have more tables
exmaple:
table1(id,title,content,date)
table2(did,type,title,text1,text2,requestdate)
table3(subid,value1,value2,value3,value4)
.......................15 tables left,some exmaple
now i want use stored procedure do search for asp.net
asp.net send search keyword to sql server,sql server get keywords execute search and return results to web application(asp.net)
i don't konw how what write t-sql,please friends help me.
because table colums is different,so need write t-sql put the temp DATA TO Temp DataTable,last return Temp DataTable to asp.net,asp.net execute split pager display data,eventable colums is different,so on asp.net links is different,need t-sql programming,hope friends can help me complete this effect(i need code,C#/VB.NET +T-SQL).
i at ago like this do.
<%@ Page Language="C#" ValidateRequest="false"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
private void page_init()
{
if (!IsSecure())
{
Response.Redirect("/sitemap/default.mspx?request=error&code=1065");
}
}
private void page_load(object sender, EventArgs e)
{
string CurrentPageValue = "";
string Qu = "";
Double PageValue = 0;
CurrentPageValue = Request.QueryString["PageN"];
Qu = Request.QueryString["QuKey"];
Qu = ReplaceStr(Qu);
if (Qu != null)
{
if (IsNumber(CurrentPageValue))
{
PageValue = Convert.ToDouble(CurrentPageValue.Trim());
Init_Data(PageValue,Qu);
}
else
{
PageValue = 1;
Init_Data(PageValue,Qu);
}
}
else
{
ExecuteError();
}
}
private void Init_Data(Double CurrentPage,string QuKey)
{
try
{
string ConStr = GetConnectionString();
string sql1 = "select id,title,date from table1 where title like '%" + QuKey + "%' order by date desc";
string sql2 = "select sid,type,title,content,date from table2 where type like '%" + QuKey + "%' or title like '%"+QuKey+"%' or content like '%"+QuKey+"%' order by date desc";
string sql3 = "select subid,text1,text2,date from table3 where text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' order by date desc";
string sql4 = "select RequestId,headIntro,HeadInfo,HeadCode,Text1,Text2,Text3,ItemText From table4 where headintro like '%" + QuKey + "%' or HeadInfo like '%" + QuKey + "%' or HeadCode like '%" + QuKey + "%' or text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' or text3 like '%" + QuKey + "%' or itemtext like '%" + QuKey + "%' order by date desc";
//expmale for test,i have 17 tables,so write some table do test
System.Data.DataTable ResultsTable = new System.Data.DataTable();
ResultsTable.Columns.Add("id", System.Type.GetType("Int32"));
ResultsTable.Columns.Add("title", System.Type.GetType("String"));
ResultsTable.Columns.Add("linkurl", System.Type.GetType("String"));
ResultsTable.Columns.Add("someIntro", System.Type.GetType("String"));
ResultsTable.Columns.Add("date", System.Type.GetType("DateTime"));
ResultsTable.Columns[0].Unique = true;
ResultsTable.Columns[0].AutoIncrement = true;
ResultsTable.Columns[0].AutoIncrementStep = 1;
ResultsTable.PrimaryKey = new System.Data.DataColumn[] { ResultsTable.Columns[0] };
System.Data.SqlClient.SqlConnection SearchConnect = new System.Data.SqlClient.SqlConnection(ConStr);
System.Data.SqlClient.SqlDataAdapter ada1 = new System.Data.SqlClient.SqlDataAdapter(sql1,SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada2 = new System.Data.SqlClient.SqlDataAdapter(sql2, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada3 = new System.Data.SqlClient.SqlDataAdapter(sql3, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada4 = new System.Data.SqlClient.SqlDataAdapter(sql4, SearchConnect);
System.Data.DataSet Ds = new System.Data.DataSet();
SearchConnect.Open();
ada1.Fill(Ds, "table1");
ada2.Fill(Ds, "table2");
ada3.Fill(Ds, "table3");
ada4.Fill(Ds, "table4");
string ReTitle = "";
string ReUrl = "";
string ReIntro = "";
DateTime ReDate = System.DateTime.Now;
if (Ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[0].Rows[1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url1";
ReIntro = Ds.Tables[0].Rows[1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
if (Ds.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[1].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[1].Rows[1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url2";
ReIntro = Ds.Tables[1].Rows[1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
//........loop to table end
//................................
Ds.Dispose();
ada1.Dispose();
ada2.Dispose();
ada3.Dispose();
ada4.Dispose();
SearchConnect.Dispose();
if (SearchResults.Rows.Count > 1)
{
DisplayData(CurrentPage, QuKey, SearchResults);
}
else
{
SearchResults.Dispose();
ExecuteError();
}
}
catch
{
ExecuteError();
}
}
private void DisplayData(Double PageNumber, string Keywords, System.Data.DataTable CurrentData)
{
Double TotalPage = 0;
Double TotalRow = 0;
Double TempPage1 = 0;
decimal TempPage2 = 0;
int StartValue = 0;
int EndValue = 0;
int PageSize = 30;
TotalRow = CurrentData.Rows.Count;
TempPage1 = TotalRow/PageSize;
TempPage2 = Convert.ToDecimal(TotalRow / PageSize);
if (Convert.ToDouble(TempPage2 - TempPage1) > 0)
{
TotalPage = TotalRow / PageSize + 1;
}
else
{
TotalPage = TotalRow / PageSize;
}
if (TotalPage < 1) { TotalPage = 1; }
if (PageNumber > TotalPage) { PageNumber = TotalPage; }
if (PageNumber == TotalPage)
{
EndValue = TotalRow;
}
else
{
EndValue = PageNumber * PageSize;
}
StartValue = PageNumber * PageSize - PageNumber;
for (int j = StartValue; j < EndValue; j++)
{
System.Web.UI.WebControls.TableRow Tr = new TableRow();
System.Web.UI.WebControls.TableCell Td1 = new TableCell();
System.Web.UI.WebControls.TableCell Td2 = new TableCell();
System.Web.UI.WebControls.TableCell Td3 = new TableCell();
Td1.Controls.Add(new LiteralControl(CurrentData.Rows[j][1].ToString()));
Td2.Controls.Add(new LiteralControl(CurrentData.Rows[j][2].ToString()));
Td3.Controls.Add(new LiteralControl(CurrentData.Rows[j][3].ToString()));
Tr.Cells.Add(Td1);
Tr.Cells.Add(Td2);
Tr.Cells.Add(Td3);
SearchResults.Rows.Add(Tr);
}
System.Web.UI.WebControls.TableRow StateTr = new TableRow();
System.Web.UI.WebControls.TableCell StateTd = new TableCell();
StateTd.ColumnSpan = 3;
if (PageNumber - 1 > 0)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber - 1) + "&qu="+Keywords+">prev</a>"));
}
if (PageNumber !=TotalPage)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber + 1) + "&qu="+Keywords+">Next</a>"));
}
StateTr.Cells.Add(StateTd);
SearchResults.Rows.Add(StateTr);
CurrentData.Dispose();
SearchResults.Dispose();
//rows count > 200,runtime at 1.6 seconds left,
//rows count > 1000,runtime at 8 seconds left,
//rows count >10000,runtime at 12 seconds left
//execute speed is very bad,so i want use sql stored procedure search,but i don't write t-sql,hope friends hope me,thanks
}
private void ExecuteError()
{
SearchResults.Controls.Add(new LiteralControl("we're sorry,we unable find even contain your key data,please try other keywords.suppot to ...."));
SearchResults.Dispose();
}
private bool IsSecure()
{
if (!Page.Request.IsSecureConnection)
{
return true;
}
else
{
return false;
}
}
private bool IsNumber(string Restr)
{
try
{
if (Restr != null)
{
string TempStr = Restr.Trim();
if (TempStr != "")
{
System.Text.RegularExpressions.Regex MyTry = new Regex("@[0-9]*$");
if (MyTry.IsMatch(TempStr))
{
Double valueR;
Double = Convert.ToDouble(TempStr);
if (valueR >= 1 && valueR <= 10000)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
catch
{
return false;
}
}
private string GetConnectionString()
{
try
{
string ConnectionString = "datasource=......,initial catalog=databasename,trusted_connection=yes;integrate security info=true;";
return ConnectionString;
}
catch
{
return "!";
}
}
private string ReplaceStr(string k)
{
try
{
if (k != null)
{
string r = k.Trim();
if (r != "")
{
r = r.Replace("~", "");
r = r.Replace("!", "");
r = r.Replace("@", "");
r = r.Replace("#", "");
r = r.Replace("$", "");
r = r.Replace("%", "");
r = r.Replace("^", "");
r = r.Replace("&", "");
r = r.Replace("*", "");
r = r.Replace("(", "");
r = r.Replace(")", "");
r = r.Replace("\", "");
r = r.Replace("|", "");
r = r.Replace("[", "");
r = r.Replace("]", "");
r = r.Replace("{", "");
r = r.Replace("}", "");
r = r.Replace(":", "");
r = r.Replace(";", "");
r = r.Replace("'", "");
r = r.Replace("", "");
r = r.Replace("<", "");
r = r.Replace(">", "");
r = r.Replace("!", "");
r = r.Replace(",", "");
r = r.Replace(".", "");
r = r.Replace("?", "");
r = r.Replace("/", "");
r = r.Trim();
return r;
}
}
else
{
return "";
}
}
catch
{
return "";
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Do Search SQL Server Page</title>
</head>
<body>
<asp:Table runat="server" ID="SearchResults">
</asp:Table>
</body>
</html>
MyTest:
SQL SERVER 2005+WINDOWS SERVER 2003+.NET 2.0