(Solved) – Passing a list value into SQL stored proc through excel

  • by
(solved)-–-passing-a-list-value-into-sql-stored-proc-through-excel

I’m connecting to SQL Server through excel via odbc and have created a stored proc which will be called multiple times in the excel book via sub and takes in parameters passed into a where clause. Let’s say I am passing in a names variable.

I have the stored proc set up with a where clause as such –> where name in (@name)

The @name will be passed in through VBA.

So if I do one name such as “Bob” it is fine, but I would like to be able to pass in a list of names (either all names like (“‘Bob’,’Susan’,’Steve'”) or better yet a null) so that the where clause is simply ignored or includes all names even if I have to hard code them. I could do this with a separate stored proc but I’d rather not. Any ideas?

sql sample

create procedure names @name varchar(50) as begin
Select * from table where name in (@name)
end

excel

dim names as String
set names = "Bob"

call namesMacro("exec names " & names)   <--- works


set names = "('Bob','Susan','Steve')"
call namesMacro("exec names " & names)    <--- does not work

The parenthesis seem to be causing an issues but without them it will just get read as multiple arguments.

Leave a Reply

Your email address will not be published. Required fields are marked *