Solved

Help with stored procedure

Posted on 2009-06-29
6
270 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

708 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

16 Experts available now in Live!

Get 1:1 Help Now