Nested queries in a dynamic query in SQL Server

Question

I am trying to implement a nested query with a dynamic query.

I have tried something like this:

SET @str = 'Select @GRP= ' + @Field +' 
           from Hist_Tab 
           Where TerminalID in (Select id from newtab where lease='+@Lease +')';

exec sp_executesql @query=@str,

@params= N' @GRP Numeric(15,2) Ouptut',
@GRP=@GRP Output

but it seems I can't implement the nested queries this way ...

Any suggestion??

Thanks in advance

Why does it seem I can't implement nested queries this way - do you get an error? If so: what is that error?! Please post the full, complete error message so we might be able to actually understand what's happening.... — marc_s
– marc_s, Commented Jul 9, 2012 at 13:19
I get that @Field must be a varchar but what is the datatype of @Lease? — Chris Moutray
– Chris Moutray, Commented Jul 9, 2012 at 14:21

fvu · Accepted Answer · 2017-02-03 23:43:31Z

1

for any local variable passed in you need to make sure that you add additional '' on each side so that the dynamic statement ends up with the equivalent of this while being executed: where lease = 'value of @Lease' the way you have it written is is turning that into: where lease = value of @Lease

will throw an error because the varchar value being compared to lease is missing the single quotes.

try this:

SET @str = 'Select @GRP= ' + @Field +' 
           from Hist_Tab 
           Where TerminalID in (Select id from newtab where lease='''+@Lease +''')';

exec sp_executesql @query=@str,

@params= N' @GRP Numeric(15,2) Ouptut',
@GRP=@GRP Output

edited Feb 3, 2017 at 23:43

fvu

33.1k6 gold badges64 silver badges84 bronze badges

answered Feb 3, 2017 at 23:22

Jesse Culver

112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested queries in a dynamic query in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related