2

I would like to run a query that takes a textfield as an input. I know it works fine with int like so:

DECLARE @UserId int = ##UserId##

...

WHERE 
    Posts.OwnerUserId = @UserId

But how can I do for text input? Something like this:

DECLARE @Location nvarchar (35) = ##Location##

select distinct top 10 Users.Id,Users.DisplayName,sum(Post.Score) as score
  from Users,Posts Questions,Posts Post,PostTags
  where Users.Location=@Location

This gives me this error:

enter image description here

Ideas?

Also, how coud I add an input field for something like a comma delimited list like so:

DECLARE @Tags nvarchar (35) = '##Tags##'

    and PostTags.TagId in
      (select Id from Tags where TagName in
        (@Tags))

Ideally I'd like to add stuff like this in the tags.

objective-c, iOS
3

You need to enclose the string field in string delimiters so it is an acceptable string literal.

DECLARE @Location nvarchar (35) = '##Location##'

SEDE is not clever enough to add those itself, instead of a direct substitution (it doesn't know it is a varchar/nvarchar) - numeric literals don't have that limitation.


As for your ##tags## variable - that's not how IN clauses work in SQL. You can't give a varchar with commas to an IN and expect that to work - think about the actual SQL and what you need to do in order to translate that to a "variable":

select Id from Tags where TagName in ('tag1', 'tag2')

You would need to have ##tags## be replaced exactly with 'tag1', 'tag2' - not be put into a single varchar variable - that won't work.

So:

select Id from Tags where TagName in (##tags##)

And you would need to use 'tag1', 'tag2' (etc) as replacement.

  • thanks! i'll grant the upvote as soon as the timer is up.. in the meantime please see my update – abbood May 10 '16 at 8:09
  • (it doesn't know it is a varchar/nvarchar) that is true but you can tell SEDE you meant the parameter to be a string by adding the type like so: DECLARE @Location nvarchar (35) = ##Location:string##. Notice that the type need to be a .Net type, not a SQL type. Demo – rene May 15 '16 at 9:54

You must log in to answer this question.

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