what is the best way to do this search?

what is the best way to do this search

I want a stored procedure which can serach in any of the filled form on my C# windows application. The textBoxes are:

txtFileNo
txtIDNumber
txtContactNumber
txtName
txtBuilding
txtBlock
txtBox
txtEmail

I want SQL Server to search for any fiiled text, so if the user enterred txtName and txtEmail this it will onlt search for txtName and txtEmail.

I know how to do this directly from the form but I want to perform this using stored procedure.

Answer this question

what is the best way to do this search?

GarrettM

it's not showing any error now.

but......

it's not retuning anything to the DataGridView
seamonkeyz

If this is the SQL that was printed in @sql_string, your problem is:

SELECT file_no, id_number, person_name FROM persons WHERE 1 = 1 AND id_number = @string_id_number ORDER BY person_name

This should have been replaced with the actual id_number for the call. Is is possible that your error handling is ignoring errors
nikita D.

The error is in here:

DECLARE @sql_string varchar(4000), @sql_param varchar(4000)

to

DECLARE @sql_string nvarchar(4000), @sql_param varchar(4000)

And it should work...
Euclidez

i have noticed that the SQL returns the correct result. I have searched for id_number = 731004167 and got the correct result and here is the @sql_string

SELECT file_no, id_number, person_name FROM persons WHERE 1 = 1 AND id_number = @string_id_number ORDER BY person_name

but on the form it shows nothing..
LurkinDurk

Sorry, didn't see...

Dynamic SQL is really hard to debug without seeing the query that is being executed.

Add:

select @sql_string

And look at the SQL being returned. Post it here if you cannot figure out the issue.
Mark Jewett - MSFT

still returning nothing.. here is my full code and stored procedure again for your review..

THE CODE:

data_table = new DataTable();

sql_connection = new SqlConnection("Data Source=.\\SQLEXPRESS;initial catalog=shefa;integrated security=true");

sql_command = new SqlCommand("sp_find_patient", sql_connection);

sql_command.CommandType = CommandType.StoredProcedure;

sql_command.Parameters.Add("@file_no", SqlDbType.NVarChar).Value = txtFileNo.Text;

sql_command.Parameters.Add("@id_number", SqlDbType.NVarChar).Value = txtIDNumber.Text;

sql_command.Parameters.Add("@contact_number", SqlDbType.NVarChar).Value = txtContactNumber.Text;

sql_command.Parameters.Add("@person_name", SqlDbType.NVarChar).Value = "%" + txtName.Text + "%";

sql_command.Parameters.Add("@address_building", SqlDbType.NVarChar).Value = txtBuilding.Text;

sql_command.Parameters.Add("@address_block", SqlDbType.NVarChar).Value = txtBlock.Text;

sql_command.Parameters.Add("@address_po_box", SqlDbType.NVarChar).Value = txtBox.Text;

sql_command.Parameters.Add("@email_address", SqlDbType.NVarChar).Value = txtEmail.Text;

sql_adapter = new SqlDataAdapter(sql_command);

sql_adapter.Fill(data_table);

dataResult.DataSource = data_table;

AND THIS IS MY STORED PROCEDURE:

USE shefa

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[sp_find_patient]

@file_no nvarchar(50) = null,

@id_number nvarchar(50) = null,

@contact_number nvarchar(50) =null,

@person_name nvarchar(50) = null,

@address_building nvarchar(50) = null,

@address_block nvarchar(50) = null,

@address_po_box nvarchar(50) = null,

@email_address nvarchar(50) = null

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @sql_string nvarchar(4000), @sql_param nvarchar(4000)

DECLARE @string_file_no nvarchar(4000)

SELECT @sql_string =

'

SELECT file_no, id_number, person_name

FROM persons

WHERE 1 = 1

'

-- IF @file_no IS NOT NULL SELECT @sql_string = @sql_string + ' AND file_no = @string_file_no'

IF @file_no IS NOT NULL SELECT @sql_string = @sql_string + ' AND file_no = ' + @string_file_no

IF @id_number IS NOT NULL SELECT @sql_string = @sql_string + ' AND id_number = @string_id_number'

IF @contact_number IS NOT NULL SELECT @sql_string = @sql_string + ' AND contact_moblle = @string_contact_number'

IF @person_name IS NOT NULL SELECT @sql_string = @sql_string + ' AND person_name LIKE @string_person_name'

IF @address_building IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_building = @string_address_building'

IF @address_block IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_block = @string_address_block'

IF @address_po_box IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_po_box = @string_address_po_box'

IF @email_address IS NOT NULL SELECT @sql_string = @sql_string + ' AND email_address = @string_email_address'

SELECT @sql_string = @sql_string + ' ORDER BY person_name'

SELECT @sql_param =

'

@string_file_no nvarchar(50),

@string_id_number nvarchar(50),

@string_contact_number nvarchar(50),

@string_person_name nvarchar(255),

@string_address_building nvarchar(50),

@string_address_block nvarchar(50),

@string_address_po_box nvarchar(50),

@string_email_address nvarchar(50)

'

EXEC sp_executesql @sql_string, @sql_param,

@file_no, @id_number, @contact_number, @person_name,

@address_building, @address_block, @address_po_box, @email_address

-- SELECT @sql_string

END
brian_tsim
You'll have to fix all of your IF statements in your Stored Proc. It looks like you only fixed the first one.

Chirs
squeezy99

i tried what is available on the site and came to the appended code but getting this error:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

that's why i changed all varchar to Nvarchar but still getting the same error..

the C# code is :

this.Cursor = Cursors.WaitCursor;

data_table = new DataTable();

sql_connection = new SqlConnection("Data Source=.\\SQLEXPRESS;initial catalog=shefa;integrated security=true");

sql_command = new SqlCommand("sp_find_patient", sql_connection);

sql_command.CommandType = CommandType.StoredProcedure;

sql_command.Parameters.Add("@file_no", SqlDbType.NVarChar).Value = txtFileNo.Text;

sql_command.Parameters.Add("@id_number", SqlDbType.NVarChar).Value = txtIDNumber.Text;

sql_command.Parameters.Add("@contact_number", SqlDbType.NVarChar).Value = txtContactNumber.Text;

sql_command.Parameters.Add("@person_name", SqlDbType.NVarChar).Value = txtName.Text;

sql_command.Parameters.Add("@address_building", SqlDbType.NVarChar).Value = txtBuilding.Text;

sql_command.Parameters.Add("@address_block", SqlDbType.NVarChar).Value = txtBlock.Text;

sql_command.Parameters.Add("@address_po_box", SqlDbType.NVarChar).Value = txtBox.Text;

sql_command.Parameters.Add("@email_address", SqlDbType.NVarChar).Value = txtEmail.Text;

sql_adapter = new SqlDataAdapter(sql_command);

sql_adapter.Fill(data_table);

dataResult.DataSource = data_table;

and the stored procedure is:

USE shefa

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[sp_find_patient]

@file_no nvarchar(50) = null,

@id_number nvarchar(50) = null,

@contact_number nvarchar(50) =null,

@person_name nvarchar(50) = null,

@address_building nvarchar(50) = null,

@address_block nvarchar(50) = null,

@address_po_box nvarchar(50) = null,

@email_address nvarchar(50) = null

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @sql_string varchar(4000), @sql_param varchar(4000)

SELECT @sql_string =

'

SELECT file_no, id_number, person_name

FROM persons

WHERE 1 = 1

'

IF @file_no IS NOT NULL SELECT @sql_string = @sql_string + ' AND file_no = @string_file_no'

IF @id_number IS NOT NULL SELECT @sql_string = @sql_string + ' AND id_number = @string_id_number'

IF @contact_number IS NOT NULL SELECT @sql_string = @sql_string + ' AND contact_moblle = @string_contact_number'

IF @address_building IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_building = @string_address_building'

IF @address_block IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_block = @string_address_block'

IF @address_po_box IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_po_box = @string_address_po_box'

IF @email_address IS NOT NULL SELECT @sql_string = @sql_string + ' AND email_address = @string_email_address'

SELECT @sql_string = @sql_string + ' ORDER BY person_name'

SELECT @sql_param =

'

@string_file_no nvarchar(50),

@string_id_number nvarchar(50),

@string_contact_number nvarchar(50),

@string_person_name nvarchar(50),

@string_address_building nvarchar(50),

@string_address_block nvarchar(50),

@string_address_po_box nvarchar(50),

@string_email_address nvarchar(50)

'

EXEC sp_executesql @sql_string, @sql_param,

@file_no, @id_number, @contact_number, @person_name,

@address_building, @address_block, @address_po_box, @email_address

END
Jassim Rahma
If you haven't already you'll have to change your IF statements that build your dynamic for it to work.

IF @file_no IS NOT NULL SELECT @sql_string = @sql_string + ' AND file_no = ' + @string_file_no

IF @id_number IS NOT NULL SELECT @sql_string = @sql_string + ' AND id_number = ' + @string_id_number

IF @contact_number IS NOT NULL SELECT @sql_string = @sql_string + ' AND contact_moblle = ' + @string_contact_number

IF @address_building IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_building = ' + @string_address_building

IF @address_block IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_block = ' + @string_address_block

IF @address_po_box IS NOT NULL SELECT @sql_string = @sql_string + ' AND address_po_box = ' + @string_address_po_box

IF @email_address IS NOT NULL SELECT @sql_string = @sql_string + ' AND email_address = ' + @string_email_address

Chris.
Shippa

i tried it again and this is the SELECT @sql_string

SELECT file_no, id_number, person_name FROM persons WHERE 1 = 1 AND file_no = @string_file_no ORDER BY person_name

but what i don't understand, why it showing the correct result on the SQL Server Management Studio but not showing it on the form
matthew lyden

The definitive documentation on this is in Erland's paper:

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

The basics of which is that you can try something like:

create procedure yourTable
(
@fileNo varchar(30) = '%',
@name varchar(30) = '%',
@email varchar(30) = '%'
) as
begin

select columns
from yourTable
where fileNo like @fileNo
or name like @name
or email like @email
end
go

If that doesn't perform well enough, you might change to using dynamic SQL to allow a more dynamic plan.
ericc59

plz help
Steveinbeloit
One place to look for information is at this page:

http://www.sommarskog.se/dyn-search.html
raja786
{ I guess I agree with Louis' suggestion. :-) }
what is the best way to do this search?

Answer this question