Execute Stored Procedure with Parameters via Makro

[wunderch]

Hi,

I want to run a SQL Stored Procedure via Makro and fill the 3 Parameters for the stored Procedure.

It works when i fill the parametes directly, like:

SUB TEST

dim dbconn, ConnectionString, Name1, Vorname1, Alter1

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=xxx;Data Source="xxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;;Use Encryption for Data=False;Tag with column collation when possible=False"

Set dbconn = CreateObject("ADODB.Connection")

dbconn.Open ConnectionString

dbconn.Execute("DWHBI.dbo.update_from_qv @Name1='Huber', @Vorname1='Hans', @Alter1=50")

END SUB

But how can i fill the parameter with variables from the qvw??

Like this:

sub TEST

dim dbconn, ConnectionString, Name1, Vorname1, Alter1

Name1 = ActiveDocument.Variables("vName1").GetContent.String

Vorname1 = ActiveDocument.Variables("vVorname1").GetContent.String

Alter1 = ActiveDocument.Variables("vAlter1").GetContent.String

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DWHBI;Data Source=segdwh02;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;;Use Encryption for Data=False;Tag with column collation when possible=False"

Set dbconn = CreateObject("ADODB.Connection")

dbconn.Open ConnectionString

dbconn.Execute("DWHBI.dbo.update_from_qv @Name1=Name1, @Vorname1=Vorname1, @Alter1=Alter1")

END SUB

Any Ideas??

Thanks for your regards

Christian

[m_woolf · Accepted solution]

Here's a guess (note the single quotes inside the double quotes):

dbconn.Execute("DWHBI.dbo.update_from_qv @Name1='" & Name1 & "', @Vorname1='" & "Vorname1 & "', @Alter1=" & Alter1)

[wunderch]

Hi. Thanks a lot! That's the answer.