Solved

Help with stored procedure

Posted on 2009-06-29
6
273 Views
Last Modified: 2013-11-07

Hi Experts,
I am writing following code and stored procedure,but I am getting unexpected result.
If I execute the select statement in the procedure seperately the output is correct.
Is the way I am passing parameters is correct?
Your help is appreciated.
SqlCommand cmd = new SqlCommand("spsearchPersonForResettlement", cnn.connection());

        cmd.CommandType = CommandType.StoredProcedure;
 

        

        if (this.ddlVillage.SelectedValue != "Select Village")

        {

            cmd.Parameters.Add("@vid", SqlDbType.Int).Value = Convert.ToInt32(this.ddlVillage.SelectedValue);

        }

        else

        {

            cmd.Parameters.Add("@vid", SqlDbType.Int).Value = null;

        }
 

       

        cmd.Parameters.Add("@sno", SqlDbType.NVarChar).Value = this.txtSurveyNoAcquired.Text;
 

        if (this.txtPropertyNoAcquired.Text != "")

        {

            cmd.Parameters.Add("@propno", SqlDbType.Int).Value = Convert.ToInt32(this.txtPropertyNoAcquired.Text);

        }

        else

        {

            cmd.Parameters.Add("@propno", SqlDbType.Int).Value = null;

        }
 

        cmd.Parameters.Add("@lastname", SqlDbType.NVarChar).Value = this.txtLastName.Text;

        cmd.Parameters.Add("@firstname", SqlDbType.NVarChar).Value = this.txtFirstName.Text;

        cmd.Parameters.Add("@middlename", SqlDbType.NVarChar).Value = this.txtMiddleName.Text;
 

        cmd.Parameters.Add("@param", SqlDbType.NVarChar).Value = this.ddlOption.SelectedValue;
 
 
 

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);
 

        this.GridView1.DataSource = ds;

        this.GridView1.DataBind();
 
 

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
 

Create PROCEDURE [dbo].[spsearchPersonForResettlement]

	-- Add the parameters for the stored procedure here

	@param nvarchar(5),

	@lastname nvarchar(100) = null,

	@firstname nvarchar(100) = null,

	@middlename nvarchar(100) = null,

	@sno nvarchar(150) = null,

	@propno int = null,

	@vid int = null

AS

BEGIN

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

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	

    -- Insert statements for procedure here

if @param = 'And'

Select distinct p.person_no,p.last_name,p.first_name,p.middle_name,p.address

from tbperson p inner join tbSurveryKhatedar sk on p.person_no = sk.person_no 

inner join tbGaothanPerson gp on p.person_no = gp.person_no 

	Where  	    sk.Village_id = Coalesce(@vid,Village_id)

		And     sk.new_servey_no = Coalesce(@sno,new_servey_no)

		And		gp.property_id = Coalesce(@propno,property_id)

		And		p.last_name = Coalesce(@lastname,last_name)

		And		p.first_name = Coalesce(@firstname,first_name)

		And		p.middle_name = Coalesce(@middlename,middle_name)
 

else
 

		Select distinct p.person_no,p.last_name,p.first_name,p.middle_name,p.address

from tbperson p inner join tbSurveryKhatedar sk on p.person_no = sk.person_no 

inner join tbGaothanPerson gp on p.person_no = gp.person_no 

	Where  	    sk.Village_id = Coalesce(@vid,Village_id)

		And     sk.new_servey_no = Coalesce(@sno,new_servey_no)

		And		gp.property_id = Coalesce(@propno,property_id)

		And		p.last_name = Coalesce(@lastname,last_name)

		And		p.first_name = Coalesce(@firstname,first_name)

		Or		p.middle_name = Coalesce(@middlename,middle_name)

END

Open in new window

0
Comment
Question by:johny_bravo1
  • 4
6 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24742841
Kindly change @param nvarchar(5) to @param varchar(5)

If your database is case sensitive, then make sure that value "And" is passed as input.If you pass some other values for @param it wont work.

Missing few alias names have been included.

Other than that no problem with the syntax
Create PROCEDURE [dbo].[spsearchPersonForResettlement]

        -- Add the parameters for the stored procedure here

        @param varchar(5),

        @lastname nvarchar(100) = null,

        @firstname nvarchar(100) = null,

        @middlename nvarchar(100) = null,

        @sno nvarchar(150) = null,

        @propno int = null,

        @vid int = null

AS

BEGIN

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

        -- interfering with SELECT statements.

        SET NOCOUNT ON;

        

    -- Insert statements for procedure here

if @param = 'And'

Select distinct p.person_no,p.last_name,p.first_name,p.middle_name,p.address

from tbperson p inner join tbSurveryKhatedar sk on p.person_no = sk.person_no 

inner join tbGaothanPerson gp on p.person_no = gp.person_no 

        Where       sk.Village_id = Coalesce(@vid,sk.Village_id)

                And     sk.new_servey_no = Coalesce(@sno,sk.new_servey_no)

                And             gp.property_id = Coalesce(@propno,gp.property_id)

                And             p.last_name = Coalesce(@lastname,p.last_name)

                And             p.first_name = Coalesce(@firstname,p.first_name)

                And             p.middle_name = Coalesce(@middlename,p.middle_name)

 

else

 

                Select distinct p.person_no,p.last_name,p.first_name,p.middle_name,p.address

from tbperson p inner join tbSurveryKhatedar sk on p.person_no = sk.person_no 

inner join tbGaothanPerson gp on p.person_no = gp.person_no 

        Where       sk.Village_id = Coalesce(@vid,sk.Village_id)

                And     sk.new_servey_no = Coalesce(@sno,sk.new_servey_no)

                And             gp.property_id = Coalesce(@propno,gp.property_id)

                And             p.last_name = Coalesce(@lastname,p.last_name)

                And             p.first_name = Coalesce(@firstname,p.first_name)

                Or              p.middle_name = Coalesce(@middlename,p.middle_name)

END

Open in new window

0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24742916
Thanks for your comment.

exec spsearchPersonForResettlement @vid=1,@sno=null,@propno=null,@lastname=null,@firstname=null,@middlename=null,@param=N'Or'

exec spsearchPersonForResettlement @vid=default,@sno=default,@propno=default,@lastname=default,@firstname=default,@middlename=default,@param=N'And'

I am getting same result for both the lines.
There is one improvement in asbove procedure.
If I execute the select statement with 'Or' and other values null.I am getting 440 records.But not with procedure.
And for 'And' I am getting 3 records for select statement.

But with procedure it is always showing me 3 records.
The stored proc is as follows.
Alter PROCEDURE [dbo].[spsearchPersonForResettlement]

	-- Add the parameters for the stored procedure here

		@param varchar(5),

        @lastname nvarchar(100) = null,

        @firstname nvarchar(100) = null,

        @middlename nvarchar(100) = null,

        @sno nvarchar(150) = null,

        @propno int = null,

        @vid int = null

AS

BEGIN

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

	-- interfering with SELECT statements.

	SET NOCOUNT ON;

	

    -- Insert statements for procedure here

if @param = 'And'

Select distinct p.person_no,p.last_name,p.first_name,p.middle_name,p.address

from tbperson p inner join tbSurveryKhatedar sk on p.person_no = sk.person_no 

inner join tbGaothanPerson gp on p.person_no = gp.person_no 

	Where  	    sk.Village_id = Coalesce(@vid,Village_id)

		And     sk.new_servey_no = Coalesce(@sno,new_servey_no)

		And		gp.property_id = Coalesce(@propno,property_id)

		And		p.last_name = Coalesce(@lastname,last_name)

		And		p.first_name = Coalesce(@firstname,first_name)

		And		p.middle_name = Coalesce(@middlename,middle_name)
 

else if @param = 'Or'
 

		Select distinct p.person_no,p.last_name,p.first_name,p.middle_name,p.address

from tbperson p inner join tbSurveryKhatedar sk on p.person_no = sk.person_no 

inner join tbGaothanPerson gp on p.person_no = gp.person_no 

	 Where  	sk.Village_id = Coalesce(@vid,Village_id)

             And		p.first_name = Coalesce(@firstname,first_name)

             And		p.middle_name = Coalesce(@middlename,middle_name)

             And     sk.new_servey_no = Coalesce(@sno,new_servey_no)

             And		p.last_name = Coalesce(@lastname,last_name)

             Or		gp.property_id = Coalesce(@propno,property_id)

END

Open in new window

0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24742935
the first thing I notice is that maybe this line should use DBNull.Value instead

cmd.Parameters.Add("@propno", SqlDbType.Int).Value = null;

cmd.Parameters.Add("@propno", SqlDbType.Int).Value = DBNull.Value;
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 8

Author Comment

by:johny_bravo1
ID: 24742967
Tried with it also.But same thing is happening. :(
0
 
LVL 8

Author Comment

by:johny_bravo1
ID: 24743000
Sorry experts,that is my mistake.I was using inner join instead of left outer.
Thanks for your help.
0
 
LVL 8

Author Closing Comment

by:johny_bravo1
ID: 31598240
Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now