Sql-server – Can’t find stored procedure for updating after creating it

sql serverstored-procedures

I'm trying to create a procedure to update my table

create proc UPDATE_PRODUCT
@ID_CAT INT,
@ID_PRODUCT VARCHAR(30),
@LABEL NVARCHAR(30),
@QTE INT,
@PRICE VARCHAR(50),
@IMG IMAGE
AS
UPDATE [Product_DB].[dbo].[PRODUCTS]
   SET [ID_CAT] = @ID_CAT
      ,[LABEL_PRODUCT] = @LABEL
      ,[QTE_IN_STOCK] = @QTE
      ,[PRICE] = @PRICE
      ,[IMAGE_PRODUCT] = @IMG
 WHERE ID_PRODUCT = @ID_PRODUCT

When I run this code the first time, it says "completed successfully"
However, I can't find it in Stored Procedure folder. I tried to do multiple refreshes and restarts but still the same.

When I try to create a procedure for other purpose like selecting or deleting, it works with no problems.

What's the solution?

Best Answer

The problem here is typically because you were in the wrong database context (in this sense, "wrong" means really "unexpected".

In other words, you were expecting to create the stored procedure in Database1, but you were really in the context of Database2.

This is the exactly reason why I'm always explicit, especially with DDL. At the top of your script/query window/etc., always put:

use YourDatabase;
go

This will ensure you don't run into this snag again. I tend to not rely on GUI drop-down lists and my memory to double-check which database is selected before running management SQL queries.

Related Solutions

SQL Server – Troubleshooting Slow Stored Procedure After Weekend

The perf problem likely comes from IO that is cached under the week. The weekend work evicts those pages.

RAM is ~100x faster than sequential disk IO which again is ~100x faster than random IO. That explains why performance is falling off a cliff.

Rewrite your queries so that the working set of touched pages is smaller. This might involve adding indexes. Or, change the work that is being done over the weekend to touch less data. Or, add RAM. Or, add a cache warming job on 04:00 AM Monday that pulls the data into cache that you rely on by executing representative dummy queries.

SQL Server – Can’t Cast a Stored Procedure Parameter

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind.

You can say:

EXEC dbo.myprocedure @foo = 'bar';

But you can't say:

EXEC dbo.myprocedure @foo = 'b' + 'ar';

You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a date parameter, parameterized safely (you may want to read up on "SQL injection"), and not have to worry about ugly string concatenation. Here is an example:

CREATE PROCEDURE dbo.foo
  @dt DATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'SELECT DATEPART(DAY, @dt);';
  EXEC sys.sp_executesql @sql, N'@dt DATE', @dt;
END
GO

DECLARE @fromDay DATETIME = '20010914 03:45';
DECLARE @dt DATE = @fromDay; -- or just declare @fromDay as DATE in the first place;
EXEC dbo.foo;

Results:

----
14

Best Answer

Related Solutions

SQL Server – Troubleshooting Slow Stored Procedure After Weekend

SQL Server – Can’t Cast a Stored Procedure Parameter

Related Question