SQLTeam.com | Weblogs | Forums

Stored Procedure Trouble

sql2014
sql2008

#1

Good Morning All,

as i am new to the SP, may any one please help me what is the possible error in my USP.

Whenever i tried to execute the above SP, sometime it gives correct result, but sometimes its showing error as table dbo.surface_mount_MONTHLY is already exists
and some times it is working fine for surface_mount but for solder its not inserting any record but stating Command executed successfully.

CREATE PROCEDURE [extract_data_surface_mount_solder]
(
@Action VARCHAR(10)

)
AS
BEGIN

IF (@Action = 'surface_mount')

BEGIN

DROP TABLE [dbo].[surface_mount_MONTHLY]

SELECT Year, Month, MANAGER_CODE, GROUP, SUM(sales) AS Sales, SUM(AverageProfit) AS AverageCostBasedProfit, SUM(CostProfit) AS CostProfit
INTO dbo.surface_mount_MONTHLY
FROM dbo.surface_mount
GROUP BY Year, Month, MANAGER_CODE, GROUP
END

ELSE IF (@Action = 'solder')

BEGIN
DROP TABLE [dbo].[solder_MONTHLY]

SELECT SELECT Year, Month, MANAGER_CODE, GROUP, SUM(saales) AS Sales, SUM(AverageProfit) AS AverageCostBasedProfit, SUM(CostProfit) AS CostProfit

INTO dbo.solder_MONTHLY
FROM dbo.solder
GROUP BY Year, Month, MANAGER_CODE, GROUP

END

END

using sql-server-2014 and 2008
Thank You


#2

If the tables you are trying to drop do not exists, the statement will generate an error. So you should do check for the existence of the table and drop it only if it exists like shown below

IF (OBJECT_ID('dbo.solder_MONTHLY') IS NOT NULL ) DROP TABLE [dbo].[solder_MONTHLY]

As a general rule, you don't want to be creating and dropping tables in a database in response to user actions for inserting or updating data. So instead what you might do is to truncate the table and then use INSERT INTO rather than SELECT ... INTO.

If there are multiple users using your database, the way you are doing it now can cause problems. If two users simultaneously call the stored procedure what would/should happen?


#3

Thanks James. you are right.

please suggest how can i prevent error when two users simultaneously call the stored procedure.

Regards


#4

What do you want it to do if two users call it simultaneously? If this is a calculation that is done once, so you can store it for use by your clients, then perhaps the stored procedure should only be called by a process/internally.