Stored Procedure 3

[vasu2005]

Hello All,

I am fairly new to the SQL development. I need to write the stroed procedure which will check have two parameters passed by VB.NET application. The stored procedure will check if there is any row with both the values, if yes then should exit if not then should insert those values in a table.

Can any one tell me how should I start...

Thanks,

V

 

[checkai]

something like this...

Create Procedure Procedure_Name @param1 as int, @param2 as int
As

Declare @i as int

Select @i = count(*) from tbl where field1=@param1 and field2=@param2

If Isnull(@i,0) = 0
Begin
 Insert Into tbl(field1, field2)
 Select @param1, @param2
End

"...we both know I'm training to become a cagefighter...see what happens if you try 'n hit me..."

 

[evaleah]

I think I am answering what you are asking here. At least this is my best starting guess.

From the .Net app create a connection object to your db. Give it a command type of stored procedure and make the command text the name of the stored procedure you create. Make sure you have included your two parameters. So, the .Net code should look something like this:

Private Function GetDataSet() As DataSet
        Dim sqlCon As New SqlConnection(MyConnectionString)
        Dim cmd As New SqlCommand
        Dim adt As SqlDataAdapter
        Dim Param1 As SqlParameter
        Dim Param2As SqlParameter

        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandText = "spReportFocusGroup"
            .Connection = sqlCon

            objFGID = .Parameters.Add(New System.Data.SqlClient.SqlParameter("@param1", System.Data.SqlDbType.Int))
            objCatID = .Parameters.Add(New System.Data.SqlClient.SqlParameter("@param2", System.Data.SqlDbType.Int))
        End With

        Param1.Value = MyValue1
        Param2.Value = MyValue2

        If sqlCon.State = ConnectionState.Closed Then
            sqlCon.Open()
        End If

        adt = New SqlDataAdapter(cmd)
        adt.Fill(DS)

        If sqlCon.State = ConnectionState.Open Then
            sqlCon.Close()
        End If

        GetDataSet = DS
End Function

The stored procedure would look something like this:

CREATE PROCEDURE spMySproc
    @param1 int,
    @param2 int
AS

declare @id bigint
set @id = 0

select @id = ID from Table1 
where Field1 = @param1 and Field2 = @param2

If @id = 0
begin
    Insert Table1 (Field1, Field2)
        Values (@param1, @param2)
end
else
begin
      'do whatever you wanted to do if you didn't do an insert
end

Obviously replace param1, field1, Table1, etc with the real table and parameter names. I used int as my variable type here but you would want to replace that as appropriate as well.

HTH
Eva

 

[Pattycake245]

Your basic syntax for creating a stored procedure is something like below:

CREATE PROCEDURE <procedure name>
@variable1 varchar(100), @variable2 varchar(100)

AS....

@variable1 and @variable2 would be the names of the two parameters you would be passing from VB.NET.

Some basic syntax to check for the existance of a value in a table and inserting it if it doesn't exist is something like below:

if exists(select * from <tablename> where field1 = @variable1 and field2 = @variable2)
begin
  return
end else
begin
  insert into <tablename>(field1, field2)
  values (@variable1, @variable2)
end

Change and fill in what you need
Tim

 

[vasu2005]

Great this was fast. Thanks a bunch to checkai, Eva and Tim.

All the posts works fine. I just have to change the table and column names..

Thanks again!!!

V.