October 12, 2003 at 2:01 pm
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.
October 12, 2003 at 4:57 pm
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
October 12, 2003 at 11:41 pm
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.
October 13, 2003 at 12:03 am
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
October 13, 2003 at 6:49 am
--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