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.