Help with stored procedure - SQL Server Forums

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)

Help with stored procedure

Author

Topic

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 15:36:50

Hi,

I need help with a stored procedure quite badly, and I'm willing to paypal $20 to whoever can get this right.

I need to:
Select Field1, field2, etc FROM table1 WHERE This = That and Him = Her
Insert table2 (field1, field2, etc) VALUES (@field1, @field2, @etc)
Delete from table1 WHERE This = That and Him = Her

This will all happen in the background, without any information passed in.

Is this possible?

Basically a stored procedure that selects, inserts the selected data, then deletes.

Thanks!
-Kyle

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 15:53:07

How does it know what to select if no information is being passed in?

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 15:58:21

With the WHERE This = That and Him = Her

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 15:59:46

So what part do you need help on? Setting up the job?

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 16:02:56

I just "don't know how" to do it, as I've only used a Stored Procedure once, and it was for something very basic.
I'm using MSSQL 2000.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 16:04:54

CREATE PROC SomeProc(@field1 int, @field2 int, @etc int)
AS
Select Field1, field2, etc FROM table1 WHERE This = That and Him = Her
Insert table2 (field1, field2, etc) VALUES (@field1, @field2, @etc)
Delete from table1 WHERE This = That and Him = Her
GO

For more details on stored procedures, do some reading in SQL Server Books Online.

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 16:14:32

I have this:


CREATE PROCEDURE spUpdateHistory 
(
@Color varchar(50),
@ItemTitle varchar(50),
@Waist varchar(50),
@Length varchar(50),
@Quantity int,
@Size varchar(50),
@Price decimal,
@UserName varchar(50),
@ItemNumber varchar(50)
)
AS
SELECT ItemNumber, UserName, Color, ItemTitle, Waist, Length, Quantity, Size, Price FROM
 irco_ShoppingCart WHERE UserName = @UserName AND IsCheckedOut = 'No'


INSERT irco_History(ItemNumber, UserName, Color, ItemTitle, Waist, Length, Quantity, Size, Price) VALUES
 (@ItemNumber, @UserName, @Color, @ItemTitle, @Waist, @Length, @Quantity, @Size, @Price)
GO

Error: Procedure 'spUpdateHistory' expects parameter '@Color', which was not supplied.

Looks like it is expecting me to pass all of the @s through?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 16:16:56

You have that error when you are executing the stored procedure, right? Please post how you are executing it.

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 16:19:30

From an asp.net 2.0 in VB.net:


Dim strUpdateHistory As String = "spUpdateHistory"
        Dim cmd As New SqlCommand(strUpdateHistory, con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@UserName", Request.ServerVariables("REMOTE_USER"))
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 16:22:03

You'll need to pass in a value for @Color or provide a default value for it. You can check the CREATE PROCEDURE article in SQL Server Books Online for stored procedure defaults.

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 16:32:32

I've just tried that - it's not passing in the default values.

I need the insert command to use the values from the select command.
Instead, the insert command is inserting the default values.

CREATE PROCEDURE spUpdateHistory
(
@Color varchar(50) = blah,
@ItemTitle varchar(50) = blah,
@Waist varchar(50) = blah,
@Length varchar(50) = blah,
@Quantity int = 1,
@Size varchar(50) = blah,
@Price decimal = 1.10,
@UserName varchar(50) = blah,
@ItemNumber varchar(50) = blah
)
AS
SELECT ItemNumber, UserName, Color, ItemTitle, Waist, Length, Quantity, Size, Price FROM
irco_ShoppingCart WHERE UserName = @UserName AND IsCheckedOut = 'No'

INSERT irco_History(ItemNumber, UserName, Color, ItemTitle, Waist, Length, Quantity, Size, Price) VALUES
(@ItemNumber, @UserName, @Color, @ItemTitle, @Waist, @Length, @Quantity, @Size, @Price)
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 16:37:14

Ok, I now get what you want. You don't want most of those input variables.

CREATE PROCEDURE spUpdateHistory
(@UserName varchar(50))
AS

DECLARE @Color varchar(50),
@ItemTitle varchar(50),
@Waist varchar(50),
@Length varchar(50),
@Quantity int,
@Size varchar(50),
@Price decimal,
@ItemNumber varchar(50)

SELECT @ItemNumber = ItemNumber, @Color = Color, @ItemTitle = ItemTitle, @Waist = Waist, @Length = Length, @Quantity = Quantity, @Size = Size, @Price = Price
FROM irco_ShoppingCart
WHERE UserName = @UserName AND IsCheckedOut = 'No'

INSERT irco_History(ItemNumber, UserName, Color, ItemTitle, Waist, Length, Quantity, Size, Price) VALUES
(@ItemNumber, @UserName, @Color, @ItemTitle, @Waist, @Length, @Quantity, @Size, @Price)
GO

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 16:39:36

And here's what it probably should look like instead of what I posted:

CREATE PROCEDURE spUpdateHistory
(@UserName varchar(50))
AS

INSERT irco_History(ItemNumber, UserName, Color, ItemTitle, Waist, Length, Quantity, Size, Price)
SELECT ItemNumber, Color, ItemTitle, Waist, Length, Quantity, Size, Price
FROM irco_ShoppingCart
WHERE UserName = @UserName AND IsCheckedOut = 'No'

GO

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 16:41:27

I actually tried this before posting here, and (then and now) it's inserting NULL into the database, aside from the username.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 16:43:49

Which one did you try? I posted two solutions now. The second being the one that you should be using.

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 16:47:22

You're fast!
I was referring to the first one.
However, I've tried the second one, and the command does run, however nothing at all is inserted.
Should there be another INSERT after the SELECT?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 16:50:19

You need to debug this in Query Analyzer and not from within your application. You need to get the code working in QA first before even attempting to get it to work in VB.NET. So open up QA and run this:

SELECT ItemNumber, Color, ItemTitle, Waist, Length, Quantity, Size, Price
FROM irco_ShoppingCart
WHERE UserName = @UserName AND IsCheckedOut = 'No'

Make sure to hard code a value for @UserName instead of the variable. Does it return what you want inserted?

Tara Kizer
aka tduggan

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-09 : 17:02:57

Ohh I'm a dolt.
I forgot that I had IsCheckedOut updated to 'Yes' before this command - your second command works.
What's your paypal address? :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 17:08:24

We do this for free. So cheers!