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?

  • 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 - Reinstate Monica 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 - Reinstate Monica 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 - Reinstate Monica Jun 9 '17 at 15:37
  • @rene I specificed also the type (string) and gave a default value. Without them, it works. – peterh - Reinstate Monica 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 - Reinstate Monica 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.

| improve this answer | |

You must log in to answer this question.

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