How can I write and run a query in SEDE without the query itself being visible to other regular SEDE users, such as in the "everything" tab of the "queries" page?
Add a comment
|
2 Answers
The Data Explorer "parameters" aren't parameters at all. They are plain string replace operations.
So all you need is this query so you can provide the actual statement as parameter:
##query##
And this is how you use it:
Note that you can't and shouldn't add any of the optional type declarations. For example ##query:string## won't work because now the value you provide will be enclosed in single quotes. So given
select * from posts
will become
'select * from posts'
and that won't run.
Keep in mind SEDE is updated once a week on Sunday.
Never forget that Monica Cellio created the awesome SEDE Tutorial.
Say "Hi" in SEDE chat.
Not sure if I'm missing anything, but at the very least, the following technique won't give away the query of interest in the "everything" tab of the queries listing:
DECLARE @query nvarchar(max) = ##query:string##;
EXEC sp_executesql @stmt=@query;
The "everything" tab of the queries listing (to my knowledge) doesn't show what arguments were passed to parameters, so the above, which is just a wrapper query that runs a query passed as an argument to the parameter named "query", will simply show up in the "everything" tab as:
DECLARE @query nvarchar(max) = ##query:string##; EXEC sp_executesql @stmt=@query;
I wanted to name the parameter sql instead of query, but that was being weird.
This won't be hidden to SE staff. They have a record of queries that are run, and also a since-last-refresh query cache which is used for performance. Interestingly, that cache includes parameter arguments, so using this technique won't bust that query cache and you can still enjoy its benefits.
Using parameters
If your query takes parameters, you can still handle this with a bit of extra stuff.
For example, let's say your query is:
SELECT arg1 = ##param1:string##, arg2 = ##param2:int##, arg3 = ##param3:float##;
Then you'd need to modify your query to something like:
SELECT arg1 = @param1, arg2 = @param2, arg3 = @param3;
And modify the the wrapper to something like:
DECLARE
@query nvarchar(max) = ##query:string##,
@arg1 nvarchar(max) = ##param1:string##,
@arg2 int = ##param2:int##,
@arg3 float = ##param3:float##
;
EXEC sp_executesql @stmt=@query,
@params=N'@param1 nvarchar(max),@param2 int,@param3 float',
@param1=@arg1,@param2=@arg2,@param3=@arg3;
Docs for sp_executesql are here.