0

I'm trying to run a dynamic sql query using excel to pull in a list of codes which are my variables in the query.

I'm currently trying to store my SQL procedure to call later but I'm getting an error. I'm very new to this so I'm not even sure if I'm doing this in the right place. To start, I have opened visual basic, and then went to Insert > Procedure. In that screen I have the following:

CREATE PROCEDURE society_detail  
Dim code_list  
AS  
BEGIN  
SET NOCOUNT ON;

SELECT DISTINCT client_code , client_name, security_code 
FROM Connection1.table_1  
WHERE client_code in (@code_list) 
ORDER BY client_code

End

For reference, Connection1 is my SQL connection that is set up and connecting properly. When I try to make the connection to this procedure I get an error saying "Compile Error: Expected: End of statement" and society_detail is highlighted. Basically I'm trying to call this procedure and then fill '@code_list' with a dataset that is in excel already. Hopefully this makes sense to someone as I've been searching the internet everywhere and nothing seems to be helping. I could be totally off on what I'm trying to do. Let me know if you need any more clarifications.

Thanks!

Improve this question
2
  • 1
    Procedure isnt that type of procedure. You need to look at recordset's and connections to do what you require. A connection object, then a recordset would be recordset=connection.execute "exec society_detail" I'll add a good link in a moment Commented Apr 7, 2020 at 16:42
  • stackoverflow.com/questions/31986552/… Commented Apr 7, 2020 at 16:53

1 Answer 1

Reset to default
0

This is how I would do this kind of thing.

' Set a Reference: Microsoft ActiveX Data Objects 2.8 Library
Sub Working()

Dim con As Connection
Dim rst As Recordset
Dim strConn As String

Set con = New Connection
strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Data Source=Your_server_name;"
strConn = strConn & "Initial Catalog=Your_DB_Name;"
strConn = strConn & "Integrated Security=SSPI;"

con.Open strConn

'Put a country name in Cell E1
Set rst = con.Execute("Exec dbo.MyOrders @BeginDate = " & "'" & ActiveSheet.Range("E1").Text & "'," & "@EndDate = '" & ActiveSheet.Range("E2").Text & "'")
ActiveSheet.Range("A1").CopyFromRecordset rst

rst.Close
con.Close

End Sub

My worksheet looks like this.

enter image description here

This will definitely work. There are certainly other ways to do the same thing. I'll leave it to you to discover other, similar, options.

Reference:

https://www.mssqltips.com/sqlservertip/3436/passing-dynamic-query-values-from-excel-to-sql-server/

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.