CODEWITHSUNDEEP
c#sql-serverstored-procedures
makhan
makhan

Reputation: 11

how to write stored procedure with two different queries

Actually i am a beginner in database .. I have written a stored procedure in which I want to get results in c# winform app from two different tables using if else , for instance I have two tables with one column in common that is 'comp_number' .. now I have written a stored procedure which executes on a button click event 

ALTER procedure [dbo].[complainVehicle_sp]
as
DECLARE @compno int


if @compno is not null  

begin

    select compno,compdate,regno,engineno,mcode from dbo.complainVehicle 
    where compno =  @compno 

end

else 

begin

   select compno,recoverydt,recoverytime,statuscode from dbo.complainRecovery
   where compno =  @compno   

end

Now I want that if Compno matches table complainVehicle it shows me the result against this , and if it matches with table complainRecovery it shows me the result against that record else it will display no record ..

here is my c# code

  string str = @"Data Source=.;Initial Catalog=----;Integrated Security=False;User ID=sa;Password=----;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False;";
            SqlConnection cnn = null;


            try
            {
                cnn = new SqlConnection(str);
                cnn.Open(); //open the connection

            }


            catch (SqlException err) 
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                if (cnn != null)
                {
                    cnn.Close(); 
                }
            } 


            if (textBox1.Text.Trim().Length == 0)

            {MessageBox.Show("No Record");}

            else if (textBox1.Text.Trim().Length  > 0)  
            {

                cnn.Open();
                SqlCommand cmd2 = new SqlCommand();
                cmd2.Connection = cnn;
                cmd2.CommandType = CommandType.StoredProcedure;
                cmd2.CommandText = "complainVehicle_sp";
                cmd2.Parameters.Add("@compno", System.Data.SqlDbType.NVarChar).Value = textBox1.Text.ToString();
                cmd2.ExecuteNonQuery();
                SqlDataAdapter da = new SqlDataAdapter(cmd2);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                cnn.Close();

As I write compno in textbox and click sumbit it shows an error `An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Procedure complainVehicle_sp has no parameters and arguments were supplied.`

... i would appreciate you all for this help .. thanks in advance guys

Upvotes: 0

Views: 66

Answers (2)

stackh34p
stackh34p

Reputation: 8999

In your code you are using DECLARE @compno int, which creates a local variable within the procedure body. The @compno variable is not accessible from outside of the stored procedure context, and it means nothing to the C# code that is invoking the procedure: 

cmd2.Parameters.Add(
    "@compno", 
    System.Data.SqlDbType.NVarChar).Value = textBox1.Text.ToString();

So, to address your issue, first, change the stored procedure to accept parameters.

ALTER PROCEDURE [dbo].[complainVehicle_sp]
-- declare a parameter @compono to the procedure
    @compno INT
as ...
BEGIN
    IF @compno IS NOT NULL
    BEGIN
        SELECT compno,compdate,regno,engineno,mcode 
        FROM dbo.complainVehicle 
        WHERE compno = @compno 
    END
    ELSE
    BEGIN
        SELECT compno,recoverydt,recoverytime,statuscode 
        FROM dbo.complainRecovery
        WHERE compno =  @compno   
    END
END

Second, you must add the appropriate parameter type in your C# code:

cmd2.Parameters.Add(
    "@compno", 
    System.Data.SqlDbType.Int).Value = int.Parse(textBox1.Text);

Since the parameter is declared as INT in the stored procedure definition, you need to use System.Data.SqlDbType.Int and provide a valid integer value by calling int.Parse(textBox1.Text).

Refer to T-SQL Stored Procedure Syntax for more information on creating stored procedures and parameter options

Upvotes: 1

Rhys Jones
Rhys Jones

Reputation: 5498

Firstly, you have to declare your procedure with a parameter, then you probably want to use EXISTS to check each table, something like this;

alter procedure [dbo].[complainVehicle_sp] (@compno int)
as

if (exists (select 1 from dbo.complainVehicle where compno = @compno ) )
begin
    select compno,compdate,regno,engineno,mcode from dbo.complainVehicle 
    where compno =  @compno 
end
else
if (exists (select 1 from dbo.complainRecovery where compno = @compno ) )
begin
   select compno,recoverydt,recoverytime,statuscode from dbo.complainRecovery
   where compno =  @compno   
end

You also need to specify the type of the parameter correctly;

cmd2.Parameters.Add("@compno", System.Data.SqlDbType.Int).Value = textBox1.Text.ToString();

Upvotes: 0

Related Questions