MS SQL Server :: This Stored Procedure Can Be Used To Search And Replace Substring In The Char, Nchar,

Using REPLACE With NCHAR In A Stored Procedure

Jul 20, 2005

I'm using replace in a stored procedure to eliminate carriage returnsa user might have entered in an Access field.The problem is that even though it seems to strip out the carriagereturn I can't get rid of the "□" character.This is my code:DECLARE @myReturn nvarchar(1)SELECT @myReturn = NCHAR(10)REPLACE(UserAddress,@myReturn,' ') AS myTEXTFor example, the user has entered:123 Oak StreetSuite 101What I get after running the replace is: 123 Oak Street □ Suite101lq

View 2 Replies View Related

Search And Replace Only Replaces One Char Per Field

Sep 22, 2006

I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script

DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)

SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'

SELECT @patfind = '%' + @find + '%'

UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind

View 1 Replies View Related

Search And Replace Only Replaces One Char Per Field

Sep 26, 2006

I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script

DECLARE @find varchar(8000),
@replace varchar(8000),
@patfind varchar(8000)

SELECT @find = '"',
@replace = '--THIS-WAS-QUOTES--'

SELECT @patfind = '%' + @find + '%'

UPDATE Incident
SET description = STUFF(convert( varchar(8000), description ),
PATINDEX( @patfind, description ),
DATALENGTH( @find ),
@replace )
WHERE description LIKE @patfind

View 4 Replies View Related

Nchar, Varchar,char?

Jan 11, 2008

What is the difference between the above data types in SQL? Which datatype should I use if I wanted both numbers and characters?

View 1 Replies View Related

Conversion From Char To Nchar

Apr 16, 2004

Hello,

I am trying to convert a single code page MS Server database into a unicode database, using the unicode data types,NCHAR, NVARCHAR, NTEXT. The problem is that in the original database, indexes and constraints have been defined on the tables whose configurations need to be changed. As a result, the ALTER TABLE command fails. Are there any other alternative solutions?
Also, data from the old database needs to be preserved. The objective is to create a unicode database which keeps the old data intact as well as accepts the new data in unicode.
It would be great if you could help!
Thanks,
Sheetal.

View 11 Replies View Related

Char Nchar Vchar Nvchar !?

Feb 14, 2006

Hello
in MS SQL 2000 for text it is possible ti use
char
nchar
varchar
nvarchar

what must be used and for what ?
I want to store normal text with occidental char (accents)

thank you

View 4 Replies View Related

Nchar Or Char Or Nvarchar Or Varchar???

Apr 19, 2004

Hi,

Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ???

Also, what really is the benefit of one over the over :confused:

Thanks

View 7 Replies View Related

Datatypes Nchar/nvarchar Vs Char/varchar

Dec 2, 2005

can anybody please explain me why microsoft using nvarchar/nchar instead of varchar/char in northwind database and pubs database. I know if a column holds unicode data you should use nvarchar or nchar but for me all those tables in northwind/pubs are not holding unicode data. but still why microsoft settled for nchar/nvarchar.

View 2 Replies View Related

How To Substring From 12 Char To 8 Char Itemid

Jun 19, 2008

Hi,

alter PROCEDURE [dbo].[PPUpdateIWDetails]

(
@CompanyID NVARCHAR(36),
@DivisionID NVARCHAR(36),
@DepartmentID NVARCHAR(36),
@ItemID NVARCHAR(36),
@OrderNo NVARCHAR(36),
@LineNo NVARCHAR(36),
@TAllotedQty Numeric,
@EmployeeID NVARCHAR(36),
@Trndate datetime
)
AS
BEGIN
By default iam passing 12 char itemid as parameter...

Here iam selecting the itemid from InventoryLedger -if it is 8 char than this query should be executed
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

Here iam selecting the itemid from InventoryLedger -if it is 12 char than this query should be executed(both queries are same)
IF EXISTS(SELECT ItemID FROM InventoryLedger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID)

BEGIN
DECLARE @Qty INT

select @Qty =QUANTITY from inventoryledger WHERE TransDate=@Trndate AND ItemID=@ItemID AND ILLineNumber =@LineNo AND TransNumber=@OrderNo AND TransactionType='Production' AND CompanyID=@CompanyID AND DivisionID= @DivisionID AND DepartmentID=@DepartmentID
select qtyonhand=qtyonhand+@Qty from InventoryByWareHouse where ItemID=@ItemID
END

View 11 Replies View Related

Data Types (char, Varchar, Nchar, Nvarchar, ...)

Jul 27, 2007

Could someone please help me by explaining which one is best to use and when? For example, storing the word "Corona Del Mar" - which Data Type would be suggested?
Thanks.

View 3 Replies View Related

Changing Char/NChar Column From NOTNULL To NULL

Apr 19, 2008

Hi I have a table, which contains Char and NChar NOT NULL columns
Now I need to change it to NULL, when I use the following command, it fails for the following error,

The command I used,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> CHAR NULL
ALTER TABLE <TableName>
ALTER COLUMN <ColName> NCHAR NULL

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

But for the same table, the below command executes fine,
ALTER TABLE <TableName>
ALTER COLUMN <ColName> SMALLINT NULL

Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.

I need a script to accomplish this task.

Any help would be greatly appreciated.

-Senthil

View 1 Replies View Related

Converting CHAR/VARCHAR/TEXT Into NCHAR/NVARCHAR/NTEXT!!!

Jan 28, 2005

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz Ansari

View 2 Replies View Related

What's Difference About Datatype Char,VarChar,NChar,NVarChar In Sql 2000 ?

Jan 9, 2006

Hi All:
         I am new to Sql 2000 database,Now I'm planing to create a table in my databse,my table included below fields like this :
       PoNo(the length is 15 characters) ,Supplier Name(the length is 50 characters).etc
      but I don't how to select the datatype for them. should I select Char or VarChar ?
      which one is the best slection ?
thans in advanced!

View 5 Replies View Related

Search & Replace In Multiple Stored Procedures

Aug 31, 2007

Hi all,

Does anyone know how to do the search and replace in multiple stored procedures using SQL 2005 Management Studio?
Any help will be appreciated.

Thanks!

View 3 Replies View Related

Full Text Search- Substring Search Not Working

Jul 6, 2007

I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.

If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;

But it works if i do
select * from testtable where contains(description,'"Exting*"') ;

The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.

View 7 Replies View Related

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

How Do I Replace A Substring In Sqlserver ?

Feb 6, 2006

HiI have a character field, varchar(60), and I want to replace only a partof it, a set substring which is always in the same set positions.Does anyone have an Sql statement or algorithm/sp that could do thatwith one call or run ?ThanksDavid

View 3 Replies View Related

Using A (substring, Replace, Len) Functions In A Report Textbox

May 25, 2007

Hi

I want to use the following sql code in a report textbox:

len(replace(substring(fc.effectivitycalendar,(pos_follow,pos_modified - Pos_follow)+1),'-',' '))

How can I do this?

Greetings

Avecinna

View 3 Replies View Related

Stored Procedure - REPLACE

Sep 19, 2005

I have this small snippet from a stored procedure...--Let @course = XY-101SET @newUserRole =@course SELECT REPLACE(@newUserRole,'-','') SET @newUserRole =@newUserRole + 'STUD' Problem is, it doesn't seem to be replacing the dash (-) with a blank/nothing/delete.The STUD does come out attached to the string, but the dash remains...It comes back as XY-101STUDAnyone see a problem with it?Thanks,Zath

View 2 Replies View Related

Replace() && Upper () In Stored Procedure

May 13, 2004

Hi;

I have a stored procedure :

<code>

Create Procedure ControlDept
(

@DeptID nvarchar(10)
)
As
If Exists
(
Select DeptName From Departments Where
DeptID LIKE @DeptID
)
Return 1
Else
Return 0

Now I want to apply replace and upper functions to DeptID in database before saying
"DeptID LIKE @DeptID".

for example the parameter is :"D&V"
DeptID in database is:"d & v" //there are spaces

if I say DeptID LIKE @DeptID nothing is found because of character nonmatching
So I have to apply replace & upper functions to the column DeptID in database

but how?
can you help me please??

View 1 Replies View Related

Replace Char At End Of String

Mar 2, 2000

Hi,

I have a table where this one column(varchar) has rows of data with a period at the end of the string. Is there any function I could use to remove all the periods?

For eg:
bhjio.
shtpl.

should become:
bhjio
shtpl

Thanks

View 1 Replies View Related

Stored Procedure - Replace Null With Text Msg

Jan 27, 2006

Hi

I'm trying to create a stored procedure using the northwind db which will do the following:

SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock
FROM Products

However, where UnitsInStocks = 0 I would like the words "Sorry, out of stock" to appear. I will then call this from an ASP page.

Can anyone help please?

Cheers

Woolly

View 7 Replies View Related

How To Replace Char In Ntext Field

Jun 16, 2006

I need help on replace char in ntext data type

Here is the example data
<qMultipleChoice><qText>The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off
20on.</qText><qChoice>Strongly20Disagree</qChoice><qChoice>Disagree
</qChoice><qChoice>Agree</qChoice><qChoice>Strongly%20Agree</qCh

I want result look like this
First Column:The AE Understands what conditions the Account Manager is allowed to sign-off.
Second Column: Strongly Disagree Disagree Agree Strongly Agree

This is what i had so far
Select (SUBSTRING(QuestionText, (PATINDEX(N'%<qText>%', QuestionText) + 7),(PATINDEX(N'%</qText>%', QuestionText) - (PATINDEX(N'%<qText>%', QuestionText) + 7)))) From tblQuestion

my result:
The%20AE%20understands%20what%20conditions%20the%2 0Account%20Manager%20is%20allowed%20to%20sign-off%20on.

I have problem with replace '%20' and how to make the second column.
Any Help?
Thanks
Shan

View 1 Replies View Related

Replace Instance Of Multiple Char(32)

May 19, 2004

I have several fileds in a sql table which holds some text and a hell of a lot of white spaces (char(32)) which is causing some problems when I show the data on the front-end

What I want to do is use sql replace function to replace the char(32). However, because there is text I can't do a simple replace as the text will become just one word !

Can anyone tell me how to loop through the field to find the char(32), where the char(32) is greater than say 5 sapces and then replace this with just one single space

E.g. a field in a table contains the following :

"my text field and loads of spaces then some more text with loads of spaces "

I want to replace the char(32) with just one space so it looks like this :
"my text filed and loads of spaces then some more text with loads of spaces"

Thanks for your help

View 3 Replies View Related

SQL 2012 :: Can SSIS Package Replace Stored Procedure

Jun 25, 2014

We require to convert a list of SPs in to SSIS packages. Most of the SPs do the below steps:

mainly our store procedure r to have compare the present date to past date , and comparing emp id between the files and also some joins. updating table r take place.

View 6 Replies View Related

SQL Server 2008 :: Stored Procedure Text String Replace

Apr 16, 2015

We have a legacy database that have hundreds of stored procedures.

The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.

I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.

I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?

View 2 Replies View Related

SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.

Oct 26, 2006

I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve

the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?

Any workaround?

Here is the pseudo code:

create Procedure foo(@output varchar(max))

{

set @foo = 'string that has more than 4000 characters...';

return;

}

Java code:

CallableStatement cs = connection.prepareCall("{call foo ?}");

cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.

cs.execute();

String result = cs.getString(1); // The result is truncated to 4000 char.

-- Also tried

CLOB clob = cs.getClob(1);

long len = clob.length(); // The result is 4000.

Thanks,

Eric Wang

View 3 Replies View Related

SQL Stored Procedure Issue - Search Stored Procedure

May 18, 2007

This is the Stored Procedure below ->
SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 5/18/2007 11:28:41 AM ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BPI_SearchArchivedBatches]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[BPI_SearchArchivedBatches]GO
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/3/2007 4:50:23 PM ******/
/****** Object: Stored Procedure dbo.BPI_SearchArchivedBatches    Script Date: 4/2/2007 4:52:19 PM ******/

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

IF (@i_WildCardFlag=0)BEGIN
SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch INNER JOIN   BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND  ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END
ELSEBEGIN SET @SQLString='SELECT       Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count,   Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By,   Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status,   Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status,  Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails,   BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType  FROM           Batch INNER JOIN  BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE  ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND  ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND  ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND  Batch.Archived_Status = 1 '
if (@V_BatchStatus IS not null) begin  set @SQLString=@SQLString + ' AND   (Batch.Status_Code in ('+@V_BatchStatus+'))' end
if (@V_BatchType IS not null) begin  set @SQLString=@SQLString + ' AND   (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end
END
PRINT @SQLString
SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)'
EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS
GO
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric.
REQUIREMENT:
The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.

Please help me regarding the same.

Thanks in advance.

Sandeep Kumar

View 2 Replies View Related

Full Text Substring Search Not Working

Jul 12, 2007

I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.

If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;

But it works if i do
select * from testtable where contains(description,'"Exting*"') ;

The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.

View 2 Replies View Related

Search The Table Based On A Substring (was: Newbie Question)

Jul 30, 2006

Hello everyone,

Just getting started with MS SQL. I have the following question:

I have a table with one field being a string (it is also an index field). I want to search the table based on a substring. For instance:

if a Field in any record has a value of '1.2.3.4.5.6'
and I search for '1.2.3.'
I want to get that record

How?

Thanks,

Avi.

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