How to deal with Stored Procedure?

Question

Hello I am new in creating stored procedure can you help me how to do this. Error: Incorrect syntax near the keyword 'AS'. Must declare scalar variable @Serial.

CREATE PROCEDURE sp_SIU
    -- Add the parameters for the stored procedure here
    @Serial varchar(50),
    @Part varchar(50),
    @Status varchar(50),
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    /*SET NOCOUNT ON;*/

    -- Insert statements for procedure here
    --where in my form if i enter serial number it will show select values  
    Select SerialNumber,PartNumber,Status from Table1 where SerialNUmber = @Serial

    --Then if is correct it will Update Status on combobox

       Update Table1 SET
               Status=@Status
                 where SerialNumber=@SerialNumber

    --then Insert Serial Number,Parnumber to Table 2

        DECLARE @Count int
    select @Count = Count(SerialNumber) from Table1 WHERE SerialNumber = @Serial

    IF @Count = 0 
        BEGIN
                INSERT INTO Table2 (SerialNumber,PArtNumber) 
                VALUES 
                (@Serial, @Part)
        END 
        RETURN @Count

    RETURN

Edit: Moved Updated info posted as an answer into Question

Oops my post is not that kind a miss. It is possible to join this 3 sql string in one stored procedure?

Scenario: { What i have to do in my form is that i will enter serial number to txtserial.text by using the select sql it will show serialnumber,partnumber and status on lblserial.text,lblpartnumber.text and lblstatus.text. And i will compare:

txtserial.text == lblserial.text
txtpartnumber.text == lblpartnumber.text

for my error handler.

{
Select SerialNumber,PartNumber,Status from Table1 where SerialNUmber = @Serial
}

Then if they are equal then: I will update my Status from cbostatus.text if serial and part is correct then use sql upate.

{
Update Table1 SET
               Status=@Status,
               Modifiedby=@username,
               DateModified=@Date

                 where SerialNumber=@Serial
}


Then insert serialnumber, using sql insert to another table.
{
 INSERT INTO Table2 (SerialNumber,DateCreated,Createdby) 
                VALUES 
                (@Serial,@date,@username)
}

something likethis. ")

Answer 1

You have a rogue comma here

    @Status varchar(50),
AS

and the name lurches between @Serial and @SerialNumber are these intended to be 2 different parameters?

Also what is the purpose of this line?

  Select SerialNumber,PartNumber,Status from Table1 where SerialNUmber = @Serial

Currently it will just send back a 3 column result set to the calling application. Is that what it is intended to do (it doesn't seem to match the following comment which seems to imply it is meant to be some kind of check)?

Answer 2

Oops my post is not that kind a miss.

It is possible to join this 3 sql string in one stored procedure?

Scenario:

What I have to do in my form is that I will enter serial number to txtserial.text by using the select sql it will show serialnumber,partnumber and status on lblserial.text,lblpartnumber.text and lblstatus.text.

AndI will compare:

• txtserial.text == lblserial.text
• txtpartnumber.text == lblpartnumber.text

for my error handler.

{
Select SerialNumber,PartNumber,Status from Table1 where SerialNUmber = @Serial
}

Then if they are equal then:

I will update my Status from cbostatus.text if serial and part is correct then use sql update.

{
    Update Table1 
    SET Status = @Status,
        Modifiedby = @username,
        DateModified = @Date
    where SerialNumber = @Serial
}

Then insert serialnumber, using sql insert to another table.

{
    INSERT INTO Table2(SerialNumber, DateCreated, Createdby) 
                VALUES(@Serial, @date, @username)
}

something like this.

Answer 3

Yes, you can execute 3 SQL statements inside one stored procedure. You probably want to declare some local variables inside your sproc to hold the intermediate results, i.e.

CREATE PROCEDURE BLAHBLAH
@SerialNumber VarChar(50)
AS
BEGIN
DECLARE @partnumber varchar(50);
SELECT @partnumber = partnumber FROM Table WHERE serialnumber = @SerialNumber;
...
SELECT @partnumber; --- return as recordset
RETURN @partnumber; --- return as return value
END

Then you can later insert @partnumber, test @partnumber, return @partnumber etc. I don't quite understand what you want to do; seems like you mostly want to look up a partnumber based on a serial number, but you want to do some uniqueness tests also. It would help if you could clarify the goal a bit more.

I recommend you ignore the user interface stuff for the moment. Write yourself some nice clean stored procedures that encapsulate the transaction and will do the right thing even if fired off at the same time from two different connections. Get everything working to your satisfaction in your SQL environment. Then go back to the user interface.