August 19, 2006 at 5:56 am
Hi All,
In a stored procedure before performing the insert I want to get the max value of a column and add 1 to it since it is part of the key. the Email, CapNo and Image are passed into the stored procedure and I have a variable declared as @ImageNo. How do I incorporate the subquery into the insert statement so that the returned value becomes @ImageNo in the insert statement.
I have the following code:
my subquery is:
select
isnull(max(imageno),0)+1 as ImageNo
from
mytable where customeremail = @CustomerEmail and capno = @CapNo
and my insert statement at the moment is simply:
INSERT
INTO [MyDB].[dbo].[mytable]
([CustomerEmail]
,[CapNo]
,[ImageNo]
,[Graphic])
VALUES
(@CustomerEmail,
@CapNo
,
@ImageNo
,
)
August 20, 2006 at 8:12 am
INSERT INTO [dbo].[mytable]
( [CustomerEmail]
,[CapNo]
,[ImageNo]
,[Graphic])
SELECT @CustomerEmail
,@CapNo
,(SELECT IsNull(Max(imageno),0)+1
FROM [dbo].[mytable]
WHERE customeremail = @CustomerEmail
AND capno = @CapNo
)
,@Graphic
August 24, 2006 at 12:28 pm
Hi MKEast,
I tried the above but I receive error:
Msg 1046, Level 15, State 1, Procedure InsertImage, Line 34
Subqueries are not allowed in this context. Only scalar expressions are allowed.
So I made the subquery a function and have changed my select statement to be:
..........
@CapNo
,
dbo.FunctionImageNo(@CustomerEmail, @CapNo),
I dont get a compilation error now, but instead an invalid object name dbo.FuntionImageNo
August 24, 2006 at 2:59 pm
I think there is no need for a function for this, just get the result into @ImageNo using below select statement and use the variable in insert
select
@ImageNo=isnull(max(imageno),0)+1 from mytable where customeremail = @CustomerEmail and capno = @CapNo
August 24, 2006 at 3:33 pm
What are the data types of the variables and columns? I retested the query using all int datatypes without any problems. It worked perfectly.
August 25, 2006 at 2:08 am
Sorry, I didn't see the subtle change from 'Values' to Select. I have changed to Select and now works. THanks Very much.
Incidently, if I did want to do this as a function, how would I make the call to the function?
August 25, 2006 at 7:15 am
Select dbo.FunctionImageNo(@CustomerEmail, @CapNo), Cola from ...
However this would result in a cursor like process. In this case it is really not a good idea to use a function.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply