Droplet table in stored procedure not working as expected?
I have a stored procedure that drops a table if it exists, then it recreates the table and populates it with the appropriate data, my friend has the same code, the only real difference is the column headers for the table.
By way of illustration, here's what my view looks like (not really, just a representation).
+----+-----+-----+--------+
| ID | Foo | Bar | Number |
+----+-----+-----+--------+
| 1 | x | x | 0 |
| 2 | x | x | 1 |
+----+-----+-----+--------+
And this is how it might look like
+----+--------+--------+-----+--------+
| ID | BarFoo | FooBar | Num | Suffix |
+----+--------+--------+-----+--------+
| 1 | x | x | 0 | a |
| 2 | x | x | 1 | b |
+----+--------+--------+-----+--------+
Again, these are just representations of the situation.
Since this is supposed to be a school assignment, the teacher will create and execute both SPs, however, when creating an SP after using the other one, I get this error:
Msg 207, Level 16, State 1, Procedure XYZ, Line 59
Invalid column name "Foo".Msg 213 Level 16 State 1 Procedure XYZ Line 61
Column name or number of specified values does not match table definition.
However, at the beginning of both stored procedures, we have the following:
CREATE PROCEDURE XYZ
AS
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'TABLENAME'
AND xtype = 'u')
DROP TABLE TABLENAME;
As I understand it, this should delete the entire table? Including table / column definitions and data?
The only fix I've found so far is to either do it DROP TABLE
separately before creating the stored procedure, which won't work for us as it really needs to be inside the stored procedure.
Help would be greatly appreciated :)
EDIT: Here's my ACTUAL code, besides the comments, it just looks like in my script (except for other code).
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'BerekenStatistiek'
AND xtype = 'p')
DROP PROCEDURE BerekenStatistiek;
GO
CREATE PROCEDURE BerekenStatistiek
@jaar INT=0
AS
BEGIN
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'Statistiek'
AND xtype = 'u')
DROP TABLE Statistiek;
DECLARE @year AS NVARCHAR (4);
SET @year = CONVERT (NVARCHAR (4), @jaar);
SELECT *,
CAST (Kost - Korting + Freight AS MONEY) AS Netto,
'' AS Richting
INTO Statistiek
FROM (SELECT O.Kwartaal,
CAST (SUM(O.Kost) AS MONEY) AS Kost,
CAST (SUM(O.Korting) AS MONEY) AS Korting,
CAST (SUM(O.Freight) AS MONEY) AS Freight
FROM (SELECT CASE
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '0101' AND @year + '0331' THEN 1
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '0401' AND @year + '0630' THEN 2
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '0701' AND @year + '0930' THEN 3
WHEN CONVERT (NVARCHAR (8), OrderDate, 112) BETWEEN @year + '1001' AND @year + '1231' THEN 4
END AS 'Kwartaal',
ROUND(UnitPrice * Quantity, 2) AS Kost,
Round((UnitPrice * Quantity) * Discount, 2) AS Korting,
Freight
FROM Orders AS O
INNER JOIN
OrderDetails AS Od
ON O.OrderID = Od.OrderID
WHERE CONVERT (NVARCHAR (4), OrderDate, 112) = @year) AS O
GROUP BY O.Kwartaal) AS O1;
ALTER TABLE Statistiek ALTER COLUMN Kwartaal INT NOT NULL;
ALTER TABLE Statistiek ALTER COLUMN Richting NVARCHAR (8);
ALTER TABLE Statistiek
ADD PRIMARY KEY (Kwartaal);
...
And here's his code (inserting values in variables is read-only excluded (his code is a little more cumbersome):
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'BerekenStatistiek'
AND xtype = 'p')
BEGIN
DROP PROCEDURE BerekenStatistiek;
END
GO
CREATE PROCEDURE BerekenStatistiek
@jaartal INT
AS
BEGIN
DECLARE @huidigkwartaal AS INT = 1;
DECLARE @beginmaand AS INT;
DECLARE @eindmaand AS INT;
DECLARE @vorige_netto_ontvangsten AS MONEY;
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = 'Statistiek'
AND xtype = 'U')
BEGIN
DROP TABLE Statistiek;
END
CREATE TABLE Statistiek
(
kwartaalnummer INT ,
beginmaand INT ,
eindmaand INT ,
orderbedrag MONEY ,
korting MONEY ,
vervoerskost MONEY ,
netto_ontvangsten MONEY ,
stijgend_dalend_gelijk NVARCHAR (10)
);
--Variables get their data here.
INSERT INTO Statistiek (kwartaalnummer, beginmaand, eindmaand, orderbedrag, korting, vervoerskost, netto_ontvangsten, stijgend_dalend_gelijk)
VALUES (@huidigkwartaal, @beginmaand, @eindmaand, @orderbedrag, @korting, @vervoerskost, @netto_ontvangsten, @stijgend_dalend_gelijk);
source to share
If you can use a different table name, start with that. And if the table only needs to exist momentarily after the proc is executed to be selectable, then create a global temporary table (i.e. the table name starts with ##
like in ##MyTable
).
However, if it is required to use the same table name as your classmate, then the teacher is probably trying to get you to know about resolving deferred objects (like @ Shannon's answer) and how to get around it, since Studying this the script has no no sense, because in reality it will never be.
The subprocesses (i.e. EXEC
and sp_executesql
) are not immediately eliminated because they are not executed when the stored procedure is created. So, in a simplified way, just declare a new variable NVARCHAR (MAX) to hold some dynamic SQL and put the statement here SELECT
. Use sp_executesql
to jump to a variable @year
. You create a real table to survive the end of the subprocess and then the statement ALTER TABLE
will run.
Additional Notes:
-
You don't really need an operator
ALTER
to set the data type in the [Richting] field. Just tell SQL Server what the type is in the SELECT statement:CONVERT(NVARCHAR(8), '') AS [Richting]
-
You really don't want to do the
CONVERT(NVARCHAR(8), OrderDate, 112)
comparison against the value as that invalidates the use of any indices that may be on[OrderDate]
. Instead, create a date value from strings and convert it to DATETIME or DATE (i.e.CONVERT(DATETIME, @year + '0101')
).To better understand this issue please read Resistance: Why% string% Is Slow and at least the first link at the bottom which is: What makes the SQL statement valid?
-
You really don't want to convert the OrderDate field to NVARCHAR (4) to compare the year for the same reason mentioned above. At least using the function
YEAR()
would be more direct. But if you want the indices to be usable, you cannot put the function in the field. But you only want a year. So the year is not the same asBETWEEN @Year + '0101' AND @Year + '1231'
?; -)Interestingly, the first example in the accepted answer under "What makes a SQL clause persistent?" SO. the question linked in the previous pool is exactly what I recommend here :).
source to share
"however, when creating an SP after using another, I get this error" (emphasis added). SQL Server will insist that the stored procedure matches the table definitions that exist as of the time the stored procedure was created. If the table does not exist when the stored procedure is created, SQL Server will assume that the lookup table will be displayed at runtime.
create table t (c int)
go
create procedure p as begin
drop table t
select 1 as diff_column_name into t
select diff_colun_name from t
end
leads to:
Msg 207, Level 16, State 1, Procedure p, Line 6
Invalid column name 'diff_colun_name'.
Now, cancel the table t
and create a procedure:
drop table t
go
create procedure p as begin
drop table t
select 1 as diff_column_name into t
select diff_colun_name from t
end
Command(s) completed successfully.
source to share
For I can understand that the wrong queries are inserts because the engine cannot find the correct table structure, check if the inserts have the same structures of your second example table. Don't forget to check the USE at the beginning of the script, maybe you are using a different db, this might happen :).
source to share