Toad World® Forums

Question on how to create multiple variables

#1

Hello, I've been using Toad for a while now but am just now really getting into the more advanced features and have a question. I have a couple queries that I want to run as an automated script. I understand how to create a variable and have the system ask me to enter it so it runs against the 2 queries in my script. However the question I have is how can I enter multiple variables? For instance here's one of the scripts I'm trying to run:

SELECT NET_CGMESH_PROPERTIES.METERID, NET_ELEMENT_STATUSES.KIND, NET_ELEMENTS.LASTHEARD

FROM (CGMS_DEV.NET_ELEMENTS NET_ELEMENTS

    INNER JOIN CGMS_DEV.NET_ELEMENT_STATUSES NET_ELEMENT_STATUSES 

       ON (NET_ELEMENTS.NET_ELEMENT_STATUS_ID = NET_ELEMENT_STATUSES.ID)) 

   INNER JOIN CGMS_DEV.NET_CGMESH_PROPERTIES NET_CGMESH_PROPERTIES 

      ON (NET_CGMESH_PROPERTIES.NET_ELEMENT_ID = NET_ELEMENTS.ID) 

WHERE NET_CGMESH_PROPERTIES.METERID IN ('5000040512', '5000040513')

I know the change ('5000040512', '5000040513') to ('#variable#') but I want to have more than one and usually have from 10 to a couple hundred that are listed as comma separated values.

I have looked on line and can't seem to find a way to do this. I'd think there's got to be a fairly simple way of getting this accomplished.

Thanks in advance for any help you may be able to give me.

#2

How about just defining the variable as a "String" type that you get prompted for during the run? And then you're not constrained by the number of numeric entries (assuming separated by commas, as SQL Server syntax dictates. Perhaps even better, read in the input string/s from a file, especially if the input is a long string, and you have lots of number inputs in your list. You probably don't want to take the time to enter dozens of numeric values for your input, I'd wager.

#3

Thank you for that. For some reason I didn't think about just making the variable everything between the parentheses including the single quotes. It was shown to me with the quotes and I guess it was a bit of a brain fart lol. It worked out great, thanks.