Delphi and SQL Server Stored Procedure Programming

Many beginners often ask how to call the stored procedure of SQL Server in Delphi? The problem is actually a good solution, but I don't want to answer it if I ask more. The following describes how to use an instance, from creating a stored procedure in SQL Server to calling a complete instance.

First, open the SQL Server Manager and create a test table in the pubs database. The table name is "test" and the fields include ID, name, and desc. All the tables are in the metadata type. If you do not know how to create a table, then open the SQL query analyzer and paste the followingCodeAnd then, the test table is automatically generated.

Use pubs

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 [DBO]. [test] (

[ID] [char] (10) Collate chinese_prc_ci_as not null,

[Name] [char] (12) Collate chinese_prc_ci_as null,

[Descrip] [char] (30) Collate chinese_prc_ci_as null

) On [primary]

Go

Create a stored procedure to insert a new record to test. the code for creating a stored procedure is as follows. Similarly, you can copy it to the query analyzer and execute it directly:

Create procedure myinsert

@ ID char (10 ),

@ Name varchar (12 ),

@ Descrip varchar (30)

As

Begin

Insert into test (ID, name, descrip) values (@ ID, @ name, @ descrip)

If @ rowcount = 0

Begin

Raiserror ('error', 16, 1)

Rollback transaction

End

End

Go

Next, create a new project file, place the following controls on form1, and set properties (in parentheses ):

One adoconnection1: tadoconnection;

(Loginprompt: = false;

Connectionstring: = provider = sqloledb.1; persist Security info = true; user id = sa; initial catalog = pubs; Data Source = (local );)

An adostoredproc1: tadostoredproc; attribute:

(Connection: = adoconnection1;

Procedurename: = myinsert; // The one we created above)

An adotable1: tadotable; attribute:

(Connection: = adoconnection1;

Tablename: = test; // The one we created above)

A performance1 property:

(Dataset: = tadotable ;)

One dbgrid1: TDBGrid; attribute:

(Datasource: = performance1 ;)

A button1, written in its onclick:

With adostoredproc1 do

Begin

Parameters. parambyname ('@ id'). Value: = '2 ';

Parameters. parambyname ('@ name'). Value: = 'myname ';

Parameters. parambyname ('@ descrip'). Value: = 'nosubobject ';

Execproc;

End;

Adotable1.close;

Adotable1.open;