Hello, I mainly use Toad for Oracle which allows me to embed variables within a sql statement or unioned sql statements using the prefix “&&”.
ie &&LAST_NAME. &&SCHEMA_ID &&DB_LINK
When I execute the sql statement I am prompted for the runtime value I wish to use at that particular time.
Using the double “&&” Toad for Oracle only prompts me only once even if this variable is used in multiple unioned statements(I have some statements with 8 to 10 unions).
Which is usually what I want.
I understand in Toad for sql server I can use the set command to intialize variables. But that falls short of a real runtime initialization of a variable, especially if there are many variable embedded in your sql statements.
Does Toad For SqlServer have an equivalent of the Oracle variable designation “&&” and prompt and if so what is the syntax?
Hi,
We can use {{Unquote(:a)}}
Simple Example:
SET a = 1
WHILE a < 10
CREATE TABLE toadscripttest{{Unquote(:a)}} (id INT);
SET a = a + 1
For more detail, please refer to toad help document:
EditSQL\Create and Edit SQL\About Toad Script.
Kelly
Thanks Kelly I supposed you answered my question. I don’t want to use set command as i can just a replace in the editor.
Here is what I want to be prompted for like in the Oracle Toad:
Select &&USER_NAME
from &&SCHEMA1.TableA
union
Select &&USER_NAME
from &&SCHEMA2.TableA
I am asking if SQL-SERVER TOAD can prompt me at runtime for the common USERNAME, unique table owner
Hi,
Sorry for the example confusing you. We don't need the set command to use this.
Would you please try to execute this? This will prompt you to enter the table owner.
Select {{Quote(:USER_NAME)}}
from{{Quote(:Schema1)}}.TableA
union
Select {{Quote(:USER_NAME)}}
from
{{Quote(:Schema2)}}.TableA
Kelly
Well that does indeed provide a prompt how ever gets a Invalid column name error message.
Below TableA has column called CompanyNo. When prompted and intialize the variable to a value such as 99, I get the error message “invalid column name 99”.
It is not recognizing is a literal and not a field name.
select *
from [databasename].dbo.tableA
where 1 = 1
and CompanyNo = {{Quote(:Company_Num)}}
I also tried this for fun but get the same error. Simple put I can’t get TOAD/SQL-SERVER to except the variable as literal instead of a column name.
Declare @Company_num Varchar(20)
set @Company_num = {{Quote(:Company_num)}}
select *
from [databasename].dbo.tableA
where 1 = 1
– and CompanyNo > 999
and CompanyNo = ‘@Company_num’ (still says 99 is an invalid column name
Hi,
This way related to the type. I try your script, the number need to use {{Round(:Company_num)}}.
So for the first script your provided, Maybe below can work.
select *
from [databasename].dbo.tableA
where 1 = 1
and CompanyNo = {{Round(:Company_Num)}}
And we need to set the type:
Or Script:
select *
from [databasename].dbo.tableA
where 1 = 1
and CompanyNo =?
For more details, we can find in help document. (About Toad Script and Bind variables)
But fot this statement funtions, It easy to get exception and error not clear. I create TSS-1156 for this.
Best Regards,
Kelly
From my perpective using the set command is not much different that using the sql editor to replace what the variable represents.
Guess it just a personal preference that Oracle for Toad allows promting for a literal whether its schema id or tablename or data value.
I just wanted to make sure that Toad For SQLServer did not have such a functionality.
I do really appreciate your time and input. thankss Again.
Hi,
Toad does not have the functionality yet.
Sorry for inconvenience.
Kelly
