SQL Server Stored Procedure Parameters

How to get stored procedure parameters details?

select  
   'Parameter_name' = name,  
   'Type'   = type_name(user_type_id),  
   'Length'   = max_length,  
   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' 
              then precision  
              else OdbcPrec(system_type_id, max_length, precision) end,  
   'Scale'   = OdbcScale(system_type_id, scale),  
   'Param_order'  = parameter_id,  
   'Collation'   = convert(sysname, 
                   case when system_type_id in (35, 99, 167, 175, 231, 239)  
                   then ServerProperty('collation') end)  

  from sys.parameters where object_id = object_id('MySchema.MyProcedure')

Can't pass parameters to stored procedure: parameters not supplied

The default value of CommandType is Text. So SqlCommand object treat the provided SP name as command Text, until, you don't specify it to StoredProcedure . So set the CommandType as cmd.CommandType = CommandType.StoredProcedure;

public static int InsertCarico(string _marca, string _modello, string _causale, int _qta)
{
    int insert = 0;
    try
    {
       if (connection.State == ConnectionState.Closed) connection.Open();
       using (SqlCommand cmd = new SqlCommand("spINSERTCARICO", connection))
       {
           cmd.CommandType = CommandType.StoredProcedure
           cmd.Parameters.Add("@MARCA", SqlDbType.VarChar).Value = _marca;
           cmd.Parameters.Add("@MODELLO", SqlDbType.VarChar).Value = _modello;
           cmd.Parameters.Add("@QTA", SqlDbType.Int).Value = _qta;
           cmd.Parameters.Add("@OPERATORE", SqlDbType.VarChar).Value = System.Environment.UserName;
           cmd.Parameters.Add("@CAUSALE", SqlDbType.VarChar).Value = _causale;
           insert = cmd.ExecuteScalar();
       }

       return insert;
   }
   catch (Exception ex)
   {
       Console.WriteLine(ex.Message);
       return 0;
   }
}

Stored procedure with default parameters

I wrote with parameters that are predefined

They are not "predefined" logically, somewhere inside your code. But as arguments of SP they have no default values and are required. To avoid passing those params explicitly you have to define default values in SP definition:

Alter Procedure [Test]
    @StartDate AS varchar(6) = NULL, 
    @EndDate AS varchar(6) = NULL
AS
...

NULLs or empty strings or something more sensible - up to you. It does not matter since you are overwriting values of those arguments in the first lines of SP.

Now you can call it without passing any arguments e.g.
exec dbo.TEST

C# : how to use parameters for SQL Server stored procedure?

SqlCommand command = new SqlCommand("SP_GetCityByID ", conn);

You don't put a where condition when you call a stored procedure. where condition needs to be inside the body of stored procedure which should compare the id column of your city table with @ID parameter you are passing to stored procedure. Secondly, ExecuteNonQuery function which you have written at the end will not serve your purpose. Use ExecuteScalar function instead as given below:

String cityName= command.ExecuteScalar();

I am assuming your stored procedure accepts parameter @ID and returns matching city name in the form of table.

How to set multiple output parameters in stored procedures

Finally, I was able to achieve the desired result with the below code :

    --Creating a table variable
    declare @TempTable table(ticketnum varchar(50))

    --Inserting values in table variable using procedure
    insert @TempTable
    select TICKETNUMBER 
        FROM TicketHistory th 
    WHERE TICKETTIME IN 
    (
        SELECT MAX(S2.TICKETTIME) 
        FROM TicketHistory S2 
        WHERE th.TICKETNUMBER = S2.TICKETNUMBER
    )  
        AND th.CURRENTSTATUS_ANALYST = 'Resolved'
        AND th.TICKETTIME < dateadd(day, -5, GETDATE())

    --Selecting values from table variable
    SELECT * from @TempTable
            
    DECLARE @ticket_number varchar(100)
    DECLARE cur CURSOR FOR SELECT ticketnum FROM @TempTable
    OPEN cur

    FETCH NEXT FROM cur INTO @ticket_number

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            insert into TicketHistory (CURRENTSTATUS_ANALYST,TICKETNUMBER,PREVIOUSSTATUS_ANALYST,TICKETTIME,FIELD,CREATEDBY)
              values('Closed', @ticket_number, 'Resolved', CURRENT_TIMESTAMP, 'Status', 'User.Auto')
            FETCH NEXT FROM cur INTO @ticket_number
        END

    CLOSE cur    
    DEALLOCATE cur
        
    END  

---

Related Topics