March 12, 2009 at 10:59 am
Hi,
I am trying to have search functionality in my web application. So, am trying do that using stored peocedure..
Here is the SP that i have but i am not getting values bak it is giving error value as non zero.
Please take a look at it and let me know what is wrong in that procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CUSTOM_SearchApplicant]
@AppFirstName nvarchar(20) = null,
@AppLastName nvarchar(40) = null,
@AppPhone nvarchar(17) = null,
@AppEmail nvarchar(50) = null
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM trecr_Applicant where
(@AppFirstName IS NULL or ApplicantFirstName = @AppFirstName)
and (@AppLastName IS NULL or ApplicantLastName = @AppLastName)
and (@AppPhone IS NULL or ApplicantPhone = @AppPhone)
and (@AppEmail IS NULL or ApplicantEmail = @AppEmail)
-- UPPER(ISNULL(ApplicantFirstName,'') LIKE UPPER(ISNULL(@AppFirstName,'%')) AND
-- UPPER(ISNULL(ApplicantLastName , '') LIKE UPPER(ISNULL(@AppLastName,'%')) AND
-- UPPER(ISNULL(ApplicantPhone,'') LIKE UPPER(ISNULL(@AppPhone,'%')) AND
-- UPPER(ISNULL(ApplicantEmail,'') LIKE UPPER(ISNULL(@AppEmail,'%'))
if @@ERROR <> 0 goto ErrHandler
END
ErrHandler:
RAISERROR('Error in sp_RECR_findApplicants procedure',16,1)
RETURN(1)[/b][/b]
March 12, 2009 at 11:27 am
Hi Anjaja
Which error message do you get?
Greets
Flo
March 12, 2009 at 11:34 am
Won't this always fall through to the error handling routine, where you will always return a non-zero value.
You probably need an 'ELSE' to go with your IF that says RETURN (0).
March 12, 2009 at 11:39 am
Another tip:
For procedures like this you should use the "WITH RECOMPILE" option.
Greets
Flo
March 12, 2009 at 11:57 am
The follwong is the error message i get when execute it by passing '1111111111' as phone number and rest of them as nulls
But it was ok when i checked syantax
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_CUSTOM_SearchApplicant]
@AppPhone = N'1111111111'
SELECT'Return Value' = @return_value
GO
----------------------------------------------------------------------------
RESULT IS...
---------------
Msg 50000, Level 16, State 1, Procedure sp_CUSTOM_SearchApplicant, Line 36
Error in sp_RECR_findApplicants procedure
(1 row(s) affected)
March 12, 2009 at 12:04 pm
Hi its executing fine after i added else for return value (0). Thank you for that.
But my problem is.. it is not giving any result when i execute that from code behind page in .net
--------------------------------------------------------------------------------------------
Dim myCommand As New SqlCommand("sp_CUSTOM_SearchApplicant", dbConnection)
myCommand.Parameters.Add("@AppFirstName", Data.SqlDbType.NVarChar).Value = txtFirstName.Text
myCommand.Parameters.Add("@AppLastName", Data.SqlDbType.NVarChar).Value = txtLastName.Text
myCommand.Parameters.Add("@AppPhone", Data.SqlDbType.NVarChar).Value = txtPhone.Text.Trim
myCommand.Parameters.Add("@AppEmail", Data.SqlDbType.NVarChar).Value = txtEmail.Text
myCommand.CommandType() = Data.CommandType.StoredProcedure
dbConnection.Open()
--------------------------------------------------------------------------------------------------
Did i do anything wrong in code behind?
March 12, 2009 at 12:06 pm
I don't think this is going to do what you are expecting it to do. If you pass in '1111111111' as the phone number, but the name on the row is 'John' the row will never be returned because the name is not NULL. For this to work as written, you'd have to pass in valid values for every argument.
You may want to look up 'Dynamic SQL' in Books Online and think about the problem a little differently.
March 12, 2009 at 12:12 pm
User does not enter every value for searching a record. When user enters any value , it may be all at once or only one value still the result should be shown to him based on a value.
So thats y i tried to have a check like "@AppFirstName IS NULL or ApplicantFirstName = @AppFirstName" in the procedure.
As i said it returned a record when i executed it in sql enterprise manager. But it is not doing that when i do that from code.
March 12, 2009 at 12:19 pm
Hi Anjana
I think the problem is within your client application. Snippet of your code:
Dim myCommand As New SqlCommand("sp_CUSTOM_SearchApplicant", dbConnection)
myCommand.Parameters.Add("@AppFirstName", Data.SqlDbType.NVarChar).Value = txtFirstName.Text
myCommand.Parameters.Add("@AppLastName", Data.SqlDbType.NVarChar).Value = txtLastName.Text
myCommand.Parameters.Add("@AppPhone", Data.SqlDbType.NVarChar).Value = txtPhone.Text.Trim
myCommand.Parameters.Add("@AppEmail", Data.SqlDbType.NVarChar).Value = txtEmail.Text
myCommand.CommandType() = Data.CommandType.StoredProcedure
dbConnection.Open()
You always specify all the parameters with the values of the text-boxes. If the user did not specify any value this will be an empty string; not DBNull.Value. You should use a check for string.IsNullOrEmpty to determine if the text-boxes are empty or not. If they are empty set DBNull.Value to the parameter.
Greets
Flo
March 12, 2009 at 12:19 pm
Sorry, I miss read the SELECT.
Can you run a profiler trace to make sure you're sending the proc what you think you're sending?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply