viewtopic.php?f=8&t=5119&hilit=msquery
viewtopic.php?f=33&t=584&hilit=msquery
Am filmat un VIDEO AICI: http://www.itlearning.ro/tutorials/tuto ... chapter=12
Atentie! Se poate si vizual (fara cod VBA), explic in film cum
Cod: Selectaţi tot
-----------------------COD Sql Server-----------
--Am facut un tabel de test
Create Table tblClienti
(
ID INT,
Nume nvarchar(20),
CifraAfaceri INT
)
--Add linii
Insert Into tblClienti Values(1,'SC AAA Srl',500000)
Insert Into tblClienti Values(2,'SC BBB Srl',600000)
Insert Into tblClienti Values(3,'SC CCC Srl',700000)
Insert Into tblClienti Values(4,'SC DDD Srl',800000)
--O procedura stocata neparametrizata
Create Procedure spDaClienti
As
Begin
Select * from tblClienti
End
--Testez
Execute spDaClienti
--O noua procedura stocata (parametrizata)
Create Procedure spDaClienti2
@pValoareaMinima INT
As
Begin
Select * from tblClienti
Where CifraAfaceri> @pValoareaMinima
End
Execute spDaClienti2 600001
--O noua procedura
-Add clienti in tabela tblClienti
Alter Procedure spDaClienti3
@pId INT,
@pNume nvarchar(50),
@pSuma Int
As
Begin
Insert Into tblClienti Values(@pId,@pNume,@pSuma)
End
Execute spDaClienti3 6,'ionica',1000
Cod: Selectaţi tot
Sub addClient()
Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorks2012;Data Source=localhost"
con.Open
Dim sqlCom As New ADODB.Command
Set sqlCom.ActiveConnection = con
sqlCom.CommandType = adCmdStoredProc
sqlCom.CommandText = "spDaClienti3"
'add para de input
Dim paraId As New ADODB.Parameter
paraId.Direction = adParamInput
paraId.Name = "@pId"
paraId.Type = adInteger
paraId.Value = Range("D3").Value
sqlCom.Parameters.Append paraId
Dim paraNume As New ADODB.Parameter
paraNume.Direction = adParamInput
paraNume.Name = "@pNume"
paraNume.Type = adVarChar
paraNume.Value = Range("D4").Value
paraNume.Size = 50
sqlCom.Parameters.Append paraNume
Dim paraSuma As New ADODB.Parameter
paraSuma.Direction = adParamInput
paraSuma.Name = "@pSuma"
paraSuma.Type = adInteger
paraSuma.Value = Range("D5").Value
sqlCom.Parameters.Append paraSuma
sqlCom.Execute
End Sub