1

I created the stored procedure for inserting records in 'order' table. I am trying to fetching the max of id of Order table from another database in variable @Id.

The schema is as follows:

CREATE PROCEDURE order_add
        @orderstatus nvarchar(1000),
        @paymentstatus nvarchar(1000),
        @Customer nvarchar(1000),
        @createdon nvarchar(1000),
        @ordertotal nvarchar(1000)
AS
    DECLARE @Id int;
BEGIN
    SELECT MAX(Id) 
    INTO @Id 
    FROM [Database2].dbo."Order";

    INSERT INTO Customer 
    VALUES(@Id, @orderstatus, @paymentstatus, @Customer, @createdon, @ordertotal);
END;

When I am executing this stored procedure I get this error:

Msg 102, Level 15, State 1, Procedure order_add, Line 13 [Batch Start Line 0]
Incorrect syntax near '@Id'.

What should I do to solve this?

st4hoo
  • 2,196
  • 17
  • 25
Tejas
  • 381
  • 1
  • 7
  • 25

2 Answers2

1

Your procedure has vogue syntax of SELECT statement :

select @Id = max(Id) 
from [Database2].dbo.Order

insert into Customer (id, orderstatus, paymentstatus, Customer, createdon, ordertotal)
       values(@Id, @orderstatus, @paymentstatus, @Customer, @createdon, @ordertotal
             );

Note :

  • Always qualify all column name explicitly while using insert into statement.
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

You have an answer that will "solve" this problem. But stop and think about your code. Think about the hierarchy of objects within a database server. You create a procedure within a particular database. It is dangerous to qualify table names with a database name - even if that is the same name in which your procedure is created. Why? Because someone will eventually want to create a copy of this database. Perhaps for testing, for disaster recovery, etc. Now your procedure will either fail or will not produce the correct results.

In addition, you have a need for guidance in your code. Presumably you work within a group that has multiple members and at least one of those members has significant experience. Your group should be doing code reviews to improve the quality of code writing generally, to raise the level of business knowledge within the group, and to identify coding errors. This process should catch many issues, some of which I'll mention.

You should assign scalar variables using SET, not SELECT. Why? Because there is a subtle and significant difference when the query finds no rows. In such a case, SET will assign a value of NULL to the variable while SELECT will leave it unchanged. More here.

You also need to think about datatypes. Use the correct ones, don't just define everything as a string - especially very large strings. I will guess that most of your parameters should not be nvarchar - and certainly not containing 1000 characters.

Why do you use double quotes in your first select statement? If you follow the rules for object names (and you should), you don't need to delimit them (which is what the double quotes do). Be consistent and follow the rules. And again - don't qualify table names with the database name unless you absolutely intend to do this for good reasons. And if you do intend this, then use a synonym. That will make someone's life much easier and give you good karma.

Lastly, always specify the list of columns you populate in an insert statement. Do not - repeat NOT - develop the lazy habit of omitting that. Why? The order of columns might change - as unlikely as that might seem. An additional column with a default might be added. Assuming the default value should be used in this situation, you now must change your statement. By specifying the column list, your code will continue to work correctly after the column is added.

SMor
  • 2,830
  • 4
  • 11
  • 14