• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

How to create a stored procedure in sql server 2005

Hi,

I have the following sql statement and would like to put into into a stored procedure in sql server 2005: -

                sql = "insert into dbo.Products values (newid(),'" & _
                                    txtProductName.Text & "', '" & _
                                    txtStockCode.Text & "', '" & _
                                    txtPrice.Text & "', '" & _
                                    cmbStockLocation.SelectedValue.ToString & "', '" & _
                                    cmbSupplier.SelectedValue.ToString & "')"

as unfortunatly I have little experience with sql server and would apprectiate your help.

Many Thanks
Lee
0
ljhodgett
Asked:
ljhodgett
  • 4
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
create procedure InsertProducts (
@ProdName varchar(100),
@StockCode varchar(100),
@TextPrice  varchar(100),
@StockLocation varchar(100),
@Supplier varchar(100) )
AS
BEGIN
   SET NOCOUNT ON
   INSERT INTO dbo.Products
   SELECT NEWID() , @ProductName, @StockCode, @TextPrice, @StockLocation,@Supplier
END
0
 
appariCommented:
try something like this

create procedure ins_Product (
@txtProductName varchar(30), @txtStockCode varchar(30),@txtPrice varchar(30),@stockLocation varchar(30), @Supplier varchar(30)
)
as
begin
insert into dbo.Products values (newid(),@txtProductName, @txtStockCode,@txtPrice,@stockLocation, @Supplier)

end


you need to define the parameters as same type and length as respective table fields.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ljhodgettAuthor Commented:
Hi,

I created a stored procedure using the programability section \ stored procedure and have placed the text into there. My only issue is that when I close it I am asked where I want to save the .SQL file. I am currently interacting with an sql server using my desktop pc and sql manager. How do I place it under the stored procedure section of the database under programatically \ stored procedures.

Many Thanks
Lee
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
if you wish you can keep as another soft copy, otherwise after typeing, you just need to press 'F5' and the sp will get stored in the database, if you dont have any compiler errors
0
 
ljhodgettAuthor Commented:
Hi,

i entered the following into a stored procedure: -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            LH
-- Create date: 02/08/02007
-- Description:      Insert product information into "dbo.Products"
-- =============================================
create procedure InsertProducts (
@ProdName varchar(100),
@StockCode varchar(100),
@TextPrice  varchar(100),
@StockLocation varchar(100),
@Supplier varchar(100) )
AS
BEGIN
   SET NOCOUNT ON
   INSERT INTO dbo.Products
   SELECT NEWID() , @ProductName, @StockCode, @TextPrice, @StockLocation,@Supplier
END

but it comes up with an error as follows: -

Msg 137, Level 15, State 2, Procedure InsertProducts, Line 16
Must declare the scalar variable "@ProductName".

Best Regards
Lee
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>SELECT NEWID() , @ProductName, @StockCode, @TextPrice, @StockLocation,@Supplier

you have declared @ProdName  so use it


SELECT NEWID() , @ProdName, @StockCode, @TextPrice, @StockLocation,@Supplier
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now