Procedure for a stored procedure function - C# desktop app

Question

Hello,
General question
Procedure for a stored procedure function.
If I want to create a new one, do I need or can I use a wizard or is it better to write code?

I have received an existing database and would like to create a new procedure.
Maybe you have or know good and short instructions how I can create this.
(Number 2 inside Screenshot)

SqlTransaction transaction;  
            using (SqlConnection sqlConnection = new SqlConnection(m_strConnectionString))  
            {  
                sqlConnection.Open();  
                transaction = sqlConnection.BeginTransaction();  
  
                try  
                {  
                    SqlCommand insertPanel = new SqlCommand("InsertPanel", sqlConnection, transaction);  
                    insertPanel.CommandType = CommandType.StoredProcedure;  
  
                    insertPanel.Parameters.AddWithValue("@ProductionOrderNumber", OrderNumber ?? (object)DBNull.Value);  
                    insertPanel.Parameters.AddWithValue("@MaterialNumber", MaterialNumber ?? (object)DBNull.Value);  
                    insertPanel.Parameters.AddWithValue("@MachineName", MachineName ?? (object)DBNull.Value);  

(Number 1 inside Screenshot) When I click on database diagrams to open them, the Management Studio closes. Why? What can I do?

Answer 1

I use Microsoft SQL Server.
I rarely need it, just need to add some data to an existing database. After that I may not need anything with SQL for 6 months.

DECLARE @length INT -Clear! it the type
SET @length = 3 -Clear! is the assignment.
SELECT @length AS [Length] -not clear, what is [Length]

SELECT @length = 5 -Clear!
SELECT @length AS [Length] -not clear!

Which variant would be for the SQL Server?

Clearly you did not try the code before posting. The "AS [Length]" is simply the column alias - sets the column name. The "AS" in SQL is a very common construct and often found in JOINs.
Remove "AS [Length]" and leave "SELECT @length". The output will show "no column name" as the column name.

DECLARE @length INT
SET @length = 3
SELECT @length

In all seriousness, how do you expect to understand any solution provided on this forum if you do not understand basic syntax?

Against my better judgement, I created a sample that illustrates a basic pattern to fetch a strongly named result set and return value from a stored procedure using ADO.NET.

Stored Procedure

IF OBJECT_ID('dbo.GetUserById', 'P') IS NOT NULL  
   DROP PROCEDURE dbo.GetUserById;  
GO  

CREATE PROCEDURE dbo.GetUserById (@id INT) 
AS    
 SET NOCOUNT ON

 SELECT [UserId], [FirstName] ,[LastName], [Email] 
 FROM [dbo].[User]  
 WHERE [UserID] = @id

 RETURN 10;
GO

ADO.NET / C#

static void Main(string[] args)
{
    string ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

    List<User> results = new List<User>();
    int returnValue = 0;          

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        SqlCommand command = new SqlCommand("dbo.GetUserById", connection);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@id", 1);
        command.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

        command.Connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            results.Add(new User()
            {
                UserID = (int)reader[0],
                FirstName = (string)reader[1],
                LastName = (string)reader[2],
                Email = (string)reader[3]
            });
        }
        reader.Close();
        returnValue = (int)command.Parameters["@returnValue"].Value;
    }


    foreach (var item in results)
    {
        Console.WriteLine($"{returnValue}\t{item.UserID}\t{item.FirstName}\t{item.LastName}\t{item.Email}");
    }
}

Model

    public class User
    {
        public int UserID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
    }

Answer 2

How can I set a variable?
e.g. @length=3 or @micro soft =3,4€

Your question is difficult to understand. If this is a T-SQL question then use SET or SELECT to set a variable.

DECLARE @length INT  
SET @length = 3  
SELECT @length AS [Length]  
  
SELECT @length = 5  
SELECT @length AS [Length]  

If you are asking how to pass a parameter to a stored procedure then the parameters are simply a comma separated list of values or variables. This is very similar to the C# method.

EXEC dbo.MyProc 1, 2  

When is the return value not 0? How I can set that?

Are you asking how to return a single (scalar) value from a stored procedure? If so, use a SELECT or configure an output variable. I think it is important to mention that returning data from a stored procedure is openly published in the official documentation. The documentation also covers standard coding patterns.

Return data from a stored procedure

If you are asking about the RETURN statement, RETURN is used to exit execution and return an integer that indicate if the stored procedure was successful. There is no indication in your code (screenshot) that you are executing a RETURN. Anyway, zero is the default return value.

Please set aside time to read the documentation so you are able to ask questions without assumptions. Currently, you're forcing the community to guess what fundamental concepts you do not understand.