SQL Stored Procedure Issue - Search Stored Procedure

May 18, 2007

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

 

View 2 Replies


ADVERTISEMENT

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

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

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

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 this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Stored Procedure For Search

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

Stored Procedure For Search

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

Search Stored Procedure

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

Stored Procedure For Search

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

How To Search For A Stored Procedure?

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

Search Stored Procedure

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

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Stored Procedure - Advanced Search

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

Need To Build A Search Stored Procedure

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

How To Search In 2 Tables By Using SQL Stored Procedure??

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

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 View Related

Can I Search A Word Only In My All Stored Procedure

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

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Multiple Keyword Search In A Stored Procedure

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

Can't Pass Search Text Into Stored Procedure

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

Stored Procedure Optional Search Parameters

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

Search MSDE Database Using Stored Procedure

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

Multiple Words To Search In Stored Procedure

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

Search For A Stored Procedure On Entire Server?

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

SQL Server 2008 :: Search For A Stored Procedure

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

Stored Procedure That Performs A Search Is Too Cumbersome

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

SQL Search :: Could Not Find Stored Procedure Error

Jun 10, 2015

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 Related

Search Multiple Keywords Stored Procedure With Precompile

Jun 8, 2007

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!

View 11 Replies View Related

Stored Procedure - Variable Number Of Parameters For Search

Dec 4, 2003

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.

View 3 Replies View Related

WHERE Clause' Search Condition From Argument In A Stored Procedure.

Nov 7, 2007

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

View 2 Replies View Related

More Tables Do Search,please Help Me,sql Server Stored Procedure Problem

Oct 12, 2006

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

View 3 Replies View Related

Stored Procedure For Full-Text Search With Filters

Sep 7, 2007

I need to create a stored procedure that allows a full text search with multiple filters. The FTS is a three variable proximity (x near y near z) drawing from three textboxes which works fine in my VB application:

WHERE CONTAINS(SectionText, ' """ & SearchTerm1 & """ NEAR """ & SearchTerm2 & """ NEAR """ & SearchTerm3 & """ ')

The filters consist of 4 comboboxes and 2 textboxes. I am trying to use the dynamic SQL approach found here:

http://www.sommarskog.se/dyn-search.html

The dynamic SQL in the stored procedure that I have created based on this model works fine for filtering, but I have not been able to get my FTS query integrated with it. I have tried various ways of declaring the SearchTerms as parameters, etc. but no luck.

Any help in getting this to work (or advice for using a different approach that is more appropriate) would be greatly appreciated.





Code Snippet

CREATE PROCEDURE ECR_Advanced_Search2

@fulldocno nvarchar(10) = NULL,
@doctype nvarchar(10) = NULL,
@year nvarchar(6) = NULL,
@sex nvarchar(7) = NULL,
@category nvarchar(10) = NULL,
@agenum smallint = NULL,
@agecat nvarchar(10) = NULL,
@debug bit = 0 AS


DECLARE @sql nvarchar(4000),

@paramlist nvarchar(4000),
@searchterm1 nvarchar(100),
@searchterm2 nvarchar(100),
@searchterm3 nvarchar(100)


SELECT @sql =

'SELECT FullDocuments.FullDocNo, FullDocuments.DocType, Details.Year

FROM FullDocuments
INNER JOIN Details ON FullDocuments.FullDocNo = Details.FullDocNo
WHERE 1 = 1 AND CONTAINS(SectionText, @searchterm1 NEAR @searchterm2 NEAR @searchterm3)'

IF @fulldocno IS NOT NULL
SELECT @sql = @sql + ' AND FullDocuments.fulldocno = @xfulldocno'

IF @DocType IS NOT NULL
SELECT @sql = @sql + ' AND FullDocuments.DocType = @xDocType'

IF @year IS NOT NULL
SELECT @sql = @sql + ' AND Details.year = @xyear'

IF @sex IS NOT NULL
SELECT @sql = @sql + ' AND Details.sex = @xsex'

IF @category IS NOT NULL
SELECT @sql = @sql + ' AND Details.category = @xcategory'

IF @agenum IS NOT NULL
SELECT @sql = @sql + ' AND Details.agenum = @xagenum'


SELECT @sql = @sql + ' ORDER BY FullDocuments.FullDocumentID'


IF @debug = 1
PRINT @sql

SELECT @paramlist =

'@xfulldocno nvarchar(10),

@xdoctype nvarchar(10),
@xyear smallint,
@xsex nvarchar(7),
@xcategory nvarchar(10),
@xagenum smallint,
@xagecat nvarchar(10)'


EXEC sp_executesql @sql, @paramlist, @doctype,

@fulldocno, @year, @sex,
@category, @agenum, @agecat

View 7 Replies View Related

SQL Search :: Catching Batch ID From Running Stored Procedure

May 5, 2015

how to catch batch id from a running stored procedure. My intention is that when we run store procedures in batch we are running a lot of procedures and I would like to log each run and if the same procedure is running several times per day I need to separate the runs by a "batch id" for the specific run. I have created a logtable and a logprocedure that logs the start and end of a procedure run and also some values for the run. So I'm trying to find a way of fetching the "batch id" that the sp is running so I can separate the runs when analyzing the logtable. I have looked at metadata tables and also in the table sys.sysprocesses but I cannot find BATCH ID.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved