3

For example, a query fragment what should work:

SELECT
  TagName AS Tag,
  Count FROM [##Site1:string?StackExchange.Math##].dbo.Tags
UNION
SELECT TagSynonyms.SourceTagName AS Tag, Tags.Count
FROM [##Site1##].dbo.TagSynonyms
LEFT JOIN [##Site1##].dbo.Tags
  ON TagSynonyms.TargetTagName = Tags.TagName

Unfortunately, it doesn't work. I get the error message

Invalid object name ''StackExchange.Math'.dbo.Tags'.

My naive tries to pass the syntax of both the SEDE and the MS-SQL heuristically weren't successful. How could I make it working?

9
  • Let me check but I'm afraid that will not work as T-SQL doesn't allow the use of an parameter at that place in the syntax.
    – rene
    Jun 9 '17 at 15:24
  • @rene No problem - also the negative answer ("it doesn't work") is helpful, because the OP can focus his efforts to alternative solutions (in my case, to solve the same with INSERT ... EXEC).
    – peterh
    Jun 9 '17 at 15:27
  • I can make it work if you're OK to include the [ and ] in the value you provide: data.stackexchange.com/stackoverflow/query/…
    – rene
    Jun 9 '17 at 15:30
  • @rene Thanks. It is funny, this was also my first try, but it still didn't work.
    – peterh
    Jun 9 '17 at 15:31
  • But you did enclose the dbname in [ ] like so [StackExchange.math]? Because without those [ and ] it will blow up.
    – rene
    Jun 9 '17 at 15:36
  • @rene Yes. The result is: Incorrect syntax near '[StackExchange.Math]'. Latest try.
    – peterh
    Jun 9 '17 at 15:37
  • @rene I specificed also the type (string) and gave a default value. Without them, it works.
    – peterh
    Jun 9 '17 at 15:38
  • Yes, you're right. I was afraid the CTE was causing the trouble. I would have to dig a bit to understand why/how this works.
    – rene
    Jun 9 '17 at 15:41
  • @rene Ok, thanks! If you convert it to an answer, it would be ok for an upvote + accept.
    – peterh
    Jun 9 '17 at 15:43
5

As you already found out: It Works™.

When you execute an SEDE query with parameters both the SQL statement and parameters are Parsed. Once the parsing is complete it calls a method called SubstituteParameters that takes the sqlstatement and all provided values for the parameters. For each parameter that method will, in the end execute:

sql.Replace("##" + name + "##", value);

Yes, that is correct. A simple string replacement which means you can provide any arbitrary sql statement as a parameter and as long as its result is valid SQL it will execute.

But there is a caveat, which you run exactly into. And that is when you provide a type for the parameter:

select count(*) from [##Database:string##].dbo.votes

Before the replace is executed, the value is first transformed by this method EncodeType(parameter.Type, value);

Based on the type, it takes the value and transforms the value by means of an encoder, which for the string type looks like this:

data => string.Format("'{0}'", data.Replace("'", "''"))

Basically is takes Database and then returns 'Database' which makes sense if you use a string parameter on an character field of an table. But it screws up the syntax when used to denote a database because this:

select count(*) from ['Database'].dbo.votes

is not valid T-Sql syntax.

Conclusion

Parameters in SEDE are simple name ⇄ value replacements with a few basic checks. If you use a parameter to replace part of your SQL statement, you better not define a type for the parameter so SEDE replaces the value as is:

select count(*) from [##Database##].dbo.votes

If you do specify a type (string, int, float) validations and/or encodings take place, altering the value of your parameter or rejecting your input.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .