Insert Statement with Select syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert Statement with Select syntax

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-01-27 : 09:40:31

Hi
I have written the following SP, but I am not sure if this is the correct way to write the stateemnt:

CREATE PROCEDURE spv_Products_InsertDuplicateCategory

@OldCategoryID int,
@VerifiedBy nvarchar (50),
@DateModified datetime,
@CategoryID int OUTPUT
AS

DECLARE @TempCatTypeID int

-- get the category type id. we need it to duplicate the data

SELECT @TempCatTypeID = CategoryTypeID
FROM Categories
WHERE CategoryID = @OldCategoryID

INSERT INTO Categories
(Name, Description, VerifiedBy, DateModified)

Select Name, Description, @VerifiedBy, @DateModified
from Categories where categoryID = @OldCategoryID

SET @CategoryID = @@IDENTITY
GO

Then I am trying to run it like:

declare @CategoryID int
exec spv_Products_InsertDuplicateCategory 25, 'q9', getdate, @CategoryID output
print @CategoryID

However, I am getting some errors when I run the SP. I am not sure if its the SP that is wrong or the way I am running it.

Would be grateful if u can help.
regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 09:45:29

Try this

declare @CategoryID int
declare @Date DateTime
set @Date=getdate()
exec spv_Products_InsertDuplicateCategory 25, 'q9', @Date, @CategoryID output
print @CategoryID

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-27 : 09:50:08

NOthing is wrong with your SP syntax, but when you call your Sp, make sure you pass all the parameters it requires. Also see the correct code for getdate()

declare @CategoryID int
exec spv_Products_InsertDuplicateCategory 25, @CategoryID, 'q9', getdate(), @CategoryID output
print @CategoryID

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-01-27 : 09:52:21

thanks a lot
A very quick reply, as ever.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-27 : 09:54:20

Sorry i corrected the code

quote:
Originally posted by afrika

NOthing is wrong with your SP syntax, but when you call your Sp, make sure you pass all the parameters it requires. Also see the correct code for getdate()

declare @CategoryID int
exec [spv_Products_InsertDuplicateCategory 25], @OldCategoryID, 'q9', getdate(), @CategoryID output
print @CategoryID

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-01-27 : 10:04:22

thanks a lot
A very quick reply, as ever.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-27 : 10:10:16

Is it ok now ?

Hello madhivanan, must be around 9pm your time ?

Slam on your brakes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources