I have one record in [Users]
table and one record with the same UserID
in [ShoppingCart]
table,
I want to get two lines or more in the result - one is of the user in [Users]
table and second otr more rows are from the same user('ID) in [UsersCart]
table.
ShoppingCart
is to indicate if there is or there are records in the [UsersCart]table with that share the same
UserID` (int)
But I get this error -
(1 row affected)
(0 rows affected)
Msg 205, Level 16, State 1, Procedure Login, Line 16 [Batch Start Line 0]
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
- Why I get an error here, if the first
DROP
is inside an IF statement-
ELSE
BEGIN
SELECT * FROM #TemporaryTable -- here there's a red line underneath #TemporaryTable
DROP TABLE #TemporaryTable;
END
CREATE TABLE [Users](
UserID INT IDENTITY(1,1) PRIMARY KEY,
EmailAddress NVARCHAR(320) UNIQUE,
PasswordHash VARCHAR(32),
Salt VARCHAR(36),
FirstName VARCHAR(256) NOT NULL,
LastName VARCHAR(256) NOT NULL,
City VARCHAR(50) NULL DEFAULT NULL,
Street VARCHAR(50) NULL DEFAULT NULL,
Country VARCHAR(50) NULL DEFAULT NULL,
)
CREATE TABLE [ShoppingCart]
(
OrderID INT IDENTITY (1,1) PRIMARY KEY,
UserID INT FOREIGN KEY REFERENCES [Users](UserID),
CreatedDate DATETIME DEFAULT(GETDATE())
)
CREATE TABLE [UsersCart]
(
OrderID INT FOREIGN KEY REFERENCES [ShoppingCart](OrderID),
UserID INT FOREIGN KEY REFERENCES [Users](UserID),
ProductID INT FOREIGN KEY REFERENCES [Products](ProductID),
ItemPrice DECIMAL(4,2),
ItemCount INT NOT NULL
)
The Procedure - UPDATED
ALTER PROC [dbo].[Login]
@emailAddress NVARCHAR(320),
@password VARCHAR(32)
AS
BEGIN
SELECT * INTO #TemporaryTable FROM [Users] WHERE EmailAddress = @emailAddress AND
PasswordHash = HASHBYTES('SHA2_256', @password +CAST(Salt AS NVARCHAR(36)))
ALTER TABLE #TemporaryTable DROP COLUMN Salt, PasswordHash
DECLARE @userId AS INT;
SET @userId = (SELECT UserID FROM #TemporaryTable)
IF EXISTS (SELECT * FROM ShoppingCart WHERE UserID = @userId)
BEGIN
DECLARE @orderId AS INT;
SET @orderId = (SELECT OrderID FROM [ShoppingCart] WHERE UserID = @userId)
SELECT * INTO #TemporaryCart FROM [UsersCart] WHERE UserID = @userId AND OrderID = @orderId
SELECT * FROM #TemporaryTable , #TemporaryCart
DROP TABLE #TemporaryTable, #TemporaryCart;
END
ELSE
BEGIN
SELECT * FROM #TemporaryTable -- error red line
DROP TABLE #TemporaryTable;
END
END
- Here - "EXEC [Login] @emailAddress = 'elado@gmail.com', @password = '123456';
This gives me in the results an empty row with only column names.. -
BEGIN
DECLARE @orderId AS INT;
SET @orderId = (SELECT OrderID FROM [ShoppingCart] WHERE UserID = @userId)
SELECT * INTO #TemporaryCart FROM [UsersCart] WHERE UserID = @userId AND OrderID = @orderId
SELECT * FROM #TemporaryTable, #TemporaryCart
DROP TABLE #TemporaryTable, #TemporaryCart;
END
Thanks
and A Happy New Year !