How to get values from queries, and then using in another query?

[Iain]

Hi

I have the following tables and stored procedure. I need to pass a value to
the stored procedure and have it use the value in a query. After running
that query it will return an ID which is then used in an insert statement.
At present the values in @MovieId int, @UserID int are left empty (see my
original code at the bottom of posting). I think this is due to an issue
with when the sql is executed (?).

I get the feeling I should have something like this instead, but not sure:

ALTER proc inserttransactions

@MovieName nvarchar(50) = 'team',
@uName nvarchar(50) = 'frank',
@FrameNumber int = 0

as

DECLARE @MovieId int, @UserID int

-- Find MovieID from MovieName
@MovieId = exec MovieName2Id @MovieName

-- Find UserID from UserEmail
@UserEmail = exec Email2UserId @uName

-- Insert Data
INSERT INTO Transactions(MovieId, UserId, FrameNumber)
VALUES (@MovieId, @UserID, @FrameNumber)
Thanks in advance.


========MY CODE=============
Tables:

CREATE TABLE [dbo].[movies] (
[movieID] [int] IDENTITY (1, 1) NOT NULL ,
[movieName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[movieDateAdded] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[transactions] (
[userID] [int] NULL ,
[movieID] [int] NULL ,
[FrameNumber] [int] NOT NULL ,
[transDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[userDateRegistered] [datetime] NULL
) ON [PRIMARY]
GO

Stored Procedure:

ALTER proc inserttransactions

@MovieName nvarchar(50) = 'team',
@uName nvarchar(50) = 'frank',
@FrameNumber int = 0

as

DECLARE @MovieId int, @UserID int

-- Find MovieID from MovieName
SELECT @MovieId = MovieId
FROM Movies
WHERE MovieName = @MovieName

-- Find UserID from UserEmail
SELECT @UserID = UserID
FROM Users
WHERE UserEmail = @uName

-- Insert Data
INSERT INTO Transactions(MovieId, UserId, FrameNumber)
VALUES (@MovieId, @UserID, @FrameNumber)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[David Portas]

You don't return values from EXEC like that. You need to specify an OUTPUT
parameter. See Books Online for examples.

Let me make some assumptions about your SPs and your DDL. I would re-write
your code like this:

CREATE PROCEDURE inserttransactions
(@MovieName NVARCHAR(50) = 'team', @uName NVARCHAR(50) = 'frank',
@FrameNumber INTEGER = 0)
AS

INSERT INTO Transactions (movieid, userid, framenumber)
SELECT
(SELECT movieid
FROM Movies
WHERE moviename = @MovieName),
(SELECT userid
FROM Users
WHERE username = @uName),
@FrameNumber

--
David Portas
------------
Please reply only to the newsgroup
--

[--CELKO--]

>> I have the following tables and stored procedure. I need to pass a
value to the stored procedure and have it use the value in a query.
After running that query it will return an id which is then used in an
insert statement. <<

You have some serious schema problems. There are no keys on these
"tables" -- without a key, they are not tables and you know that
IDENTITY is **never** a key by definition. The USPS says a name line
on an address label is CHAR(35), so why are you allowing CHAR(50)?

Can you use the UPC code for the movie's natural key? Did you research
the movie industry to see if they have another code?

How can you allow a transaction without having a user or a movie
involved, yet have it exist at a point in time? Why did you allow an
NVARCHAR(n) email address -- do you want to have a lot of non-Latin
garbage in the database?

You have a natural and verifiable key for a user -- his email address,
so use it.

CREATE TABLE Users
(user_email VARCHAR(128) NOT NULL -- what is the max length?
CHECK (<< valid email >>)
user_name CHAR(35) NOT NULL, --USPS standard
userdate_registered DATETIME
DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TABLE Movies
(movie_id CHAR(10) NOT NULL PRIMARY KEY, --upc code
movie_name NVARCHAR (50) NOT NULL, -- foreign films allowed
movie_date_added DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TABLE Transactions
(user_email VARCHAR(50) NOT NULL
REFERENCES Users(user_email)
ON UPDATE CASCADE,
movie_id CHAR(10) NOT NULL
REFERENCES Users(user_email)
ON UPDATE CASCADE,
frame_number INTEGER NOT NULL,
trans_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL);

-- do it in one simple, pure SQL statement

CREATE PROCEDURE InsertTransactions
(@my_movie_name NVARCHAR(50),
@my_user_name VARCHAR(35))
AS
BEGIN
INSERT INTO Transactions (movie_id, user_email, frame_number)
SELECT movie_id, user_email, @frame_number
FROM Movies AS M1, Users AS U1
WHERE M1.movie_name = @my_movie_name
AND U1.user_name = @my_user_name;

<<error handling goes here>>;
END;

Your real problem is conceptual; you are still writing procedural code
in a non-procedural language. You break things down into steps and do
them one at a time -- "find movie_id" and then "find user_id" and then
"build a row" and then "insert the new row". SQL does not work that
way; you want to think "insert a new row, which has the following
attributes and values" as one unit of work.