How Can I Creating A Stored Procedure For Select Using @Flag In This Sp?

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

HELP PLEAS!!!!!
I created a SP for bulk insertion.single bulk insertion is working then am alterd SP with flag and select command.Here how can I pass the insert flag?

in single bulk insertion(with out flag)I calld sp &datatable dt1 like this

C#

public void bulk_insert()
       {
           using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\MyPractice\browse_fie\browse_fie\browse.mdf;Integrated Security=True"))
           {
               using (SqlCommand cmd = new SqlCommand("SP_PERDRAWT"))
               {
                   cmd.CommandType = CommandType.StoredProcedure;
                   cmd.Connection = con;
                   cmd.Parameters.AddWithValue("@tblperdraw", dt1);
                   con.Open();
                   cmd.ExecuteNonQuery();

                   con.Close();
               }
           }
       }

alterd sp is..how can I call insert n select? have to pass table as parameter in bulk insert

SQL

CREATE PROCEDURE SP_PERDRAWT(@name varchar(20), address varchar(20),age int,@flag varchar(20))
   if(@flag='insert')
   BEGIN
      @tblperdraw createtype_perdraw READONLY
      SET NOCOUNT ON
      INSERT INTO mytable(name,address ,age)
      SELECT * FROM @tblperdraw
   END
   if(@flag='select')
   BEGIN
      select * from mytable
    END
END

Posted 15-Jul-15 19:10pm

Manidas Payyoli

Updated 15-Jul-15 19:32pm

Wendelius

v2

Add a Solution

Comments

Abhishek Pant 16-Jul-15 1:41am

just pass the parameter for the flag below your 1st parmater of dt1 passed.
cmd.Parameters.AddWithValue("@flag", flag); //pass flag value here

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Godhaniketan · Answer 1 · 2015-07-15T19:55:00

I think you issue with your SP please verify it.

CREATE PROCEDURE SP_PERDRAWT(
  @name varchar(20),
  @address varchar(20), //this decalration must with start with @
  @age int, //this decalration must with start with @
  @flag varchar(20)
)
AS     // In your SP there no such a key words AS and BEGIN here
BEGIN  //.. 
	   IF(@flag='insert')  //if condition
	   BEGIN
		  @tblperdraw createtype_perdraw READONLY
		  SET NOCOUNT ON
		  INSERT INTO mytable(name,address ,age)
		  SELECT * FROM @tblperdraw
	   END
	   ELSE IF(@flag='select')  //else if condition
	   BEGIN
		  select * from mytable
		END
END

C#

please try it out with this correction
if i'm going wrong to understand your issue then let me inform

Wendelius · Answer 2 · 2015-07-15T19:36:00

You probably have already created the type for table so just define it as a parameter. Something like

SQL

CREATE PROCEDURE SP_PERDRAWT(@name varchar(20), address varchar(20),age int,@flag varchar(20), @tblperdraw createtype_perdraw )
   if(@flag='insert')
   BEGIN
      SET NOCOUNT ON
      INSERT INTO mytable(name,address ,age)
      SELECT * FROM @tblperdraw
   END
   if(@flag='select')
   BEGIN
      select * from mytable
    END
END

For an example you can have a look at for example How to pass multiple records to a Stored Procedure[^]

How Can I Creating A Stored Procedure For Select Using @Flag In This Sp?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0