Creating a stored procedure with paramaters

  • Hi all, I am new to SQL Server. I have an assignment for class where I have to create a Stored procedure that accepts paramaters and inserts a record into a database.

    I have learned very basics of Stored Procedures and paramaters. I was just wondering if someone could give me a mock-up example of what i have to do so I can get this assignment working. I have to insert a record into a table called Students.

    It must accept like 4 or 5 paramater s and also check to see if a balance field is not zero or not null. If it is I have to put another value (like $100) into it. I also have to check for an error number after insertion. Any help appreciated,

    Thanks.

  • I'm afraid doing a mock-up example for you would be pretty much completing your assignment for you and putting you at a disadvantage because you wouldn't have learned anything.

    Do what I do.... steal ideas from Microsoft. ie. Look in SQL Books Online for syntax and simple examples, and then look at some of the sp_addxxxxxx stored procedures in your master database for some insert procedures that validate parameters.

    Cheers,

    - Mark


    Cheers,
    - Mark

  • quote:


    Hi all, I am new to SQL Server. I have an assignment for class where I have to create a Stored procedure that accepts paramaters and inserts a record into a database.

    I have learned very basics of Stored Procedures and paramaters. I was just wondering if someone could give me a mock-up example of what i have to do so I can get this assignment working. I have to insert a record into a table called Students.

    It must accept like 4 or 5 paramater s and also check to see if a balance field is not zero or not null. If it is I have to put another value (like $100) into it. I also have to check for an error number after insertion. Any help appreciated,

    Thanks.


  • quote:


    Hi all, I am new to SQL Server. I have an assignment for class where I have to create a Stored procedure that accepts paramaters and inserts a record into a database.

    I have learned very basics of Stored Procedures and paramaters. I was just wondering if someone could give me a mock-up example of what i have to do so I can get this assignment working. I have to insert a record into a table called Students.

    It must accept like 4 or 5 paramater s and also check to see if a balance field is not zero or not null. If it is I have to put another value (like $100) into it. I also have to check for an error number after insertion. Any help appreciated,

    Thanks.

    Check this

    CREAT PROCEDURE procedurename_sp

    @parameter1, @parameter2, @parameter3, @parameter4

    AS

    SELECT @parameter1 = value1 in your table, @parameter2 = value2, @parameter3=value3, @parameter4=value4

    FROM tablename

    WHERE conditions if needed

    and in your vb write this code,

    connection.Open

    Set CMD.ActiveConnection = Connection

    CMD.CommandText = "procedurename_sp"

    CMD.CommandType = adCmdStoredProc

    Dim Parameter1 As ADODB.Parameter

    Set parameter1=New ADODB.Parameter

    Parameter.Name = "name"

    Parameter.Type= (specify data type like adDate for example)

    Parameter.Value="Text1.Text" (from the entry form if you like)

    Parameter.Direction=adParamInput

    CMD.Parameters.Append Parameter1

    Now, repeat for the other parameters

    Dim Parameter2,,

    ,

    ,

    CMD.Parameters.Append Parameter2

    ,etc

    finaly, write this line of code,

    Set RS = CMD.Excute

    Hope this will work


  • --Here you are. Good luck & have fun.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[Test]

    GO

    Create Table Test (testfield varchar(25))

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_TestProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[usp_TestProc]

    GO

    Create PROCEDURE usp_TestProc @testfield varchar(25)

    AS

    Insert into dbo.Test (testfield) values ( @testfield)

    GO

    Declare @mockup varchar(25)

    Select @mockup = 'THIS IS A MOCK-UP'

    Execute usp_TestProc @testfield = @mockup

    Select * from [dbo].[Test]

    GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply