sp_executesql or execute without variables

Question

Id like to execute this query on sql server 2012 without using variables:

DECLARE @query VARCHAR(4000)

set @query= concat('select * from customer where id in (',
(select id from customer where comid=1 and code='30.00.0000'),
') order by code')

execute @query

So I tried this:

sp_executesql N'
select concat(''select * from customer where id in ('',
(select id from customer where comid=1 and code=''30.00.0000''),
'') order by code'')'

with no effect as it produces the query instead of returning the values.

Above version is cropped. This is whole script:

DECLARE @query VARCHAR(4000)`
DECLARE @years VARCHAR(2000)`

SELECT @years = STUFF((
    SELECT DISTINCT
        '],[' + ltrim(str(etos))
    FROM 
    (
        select c.code , year(f.ftrdate) as etos , sum((it.outputvalmode-it.inputvalmode)*st.netlinevalue) as katharh
        from fintrade f left join itemtrans it on it.ftrid=f.id 
                left join material m on m.id=it.iteid 
                left join storetradelines st on it.stlid=st.id
                left join customer c on c.id=f.cusid
        where m.code like '73.00.901%' and m.comid=1
        group by c.code , year(f.ftrdate)
    )a
    ORDER BY '],[' + ltrim(str(etos))
    FOR XML PATH('')), 1, 2, '') + ']'

SET @query =
    'SELECT * FROM
    (
        select c.code , year(f.ftrdate) as etos , sum((it.outputvalmode-it.inputvalmode)*st.netlinevalue) as katharh
        from fintrade f left join itemtrans it on it.ftrid=f.id 
                left join material m on m.id=it.iteid 
                left join storetradelines st on it.stlid=st.id
                left join customer c on c.id=f.cusid
        where m.code like ''73.00.901%'' and m.comid=1
        group by c.code , year(f.ftrdate)
    ) AS t
    PIVOT (MAX(katharh) FOR etos IN (' + @years + ')) AS pvt'`

print (@query) 
execute (@query)

Why do you need to use dynamic sql here in the first place? From what you posted the dynamic sql is no needed. — Sean Lange, Jun 28 '17 at 14:11
and you also don't need the subquery. You just need `select * from customer where comid=1 and code='30.00.0000' order by code` — S3S, Jun 28 '17 at 14:12
its a portion of my script only for the reason of being answered. My main script is way to large and dynamic, as well. — Constantine Nikolopoulos, Jun 28 '17 at 14:13
So maybe your entire script could be improved. You have asked an [XY Problem](http://xyproblem.info/) Otherwise, `sp_executesql N'select * from customer where id in (select id from customer where comid=1 and code=''30.00.0000'')order by code'` should work but is pointless since you don't need the subquery or sp_executesql — S3S, Jun 28 '17 at 14:15
guys i appreciate all this comments on how can i think on solving problems.. but i dont think that is logic erroneous here.. its a technical limitation i want to overcome. How can i produce a executable script without variables! as well as the topic! — Constantine Nikolopoulos, Jun 28 '17 at 14:39
Looks like it can't be done. You have to use variables. I can't find a way to nest dynamic sql inside of dynamic sql without using variables. — Tab Alleman, Jun 28 '17 at 14:41
i see.. by the way i cant solve it another way.. cause i have a dynamic product (pivot) of years which burdens me. — Constantine Nikolopoulos, Jun 28 '17 at 14:48
I can't imagine any situation where a combination of variables and temp tables can't be used to solve the problem. I don't even see where you're trying to nest dynamic sql in your complete script. What is the error with your complete script? — Tab Alleman, Jun 28 '17 at 14:52
dear tab.. there is no error at all.. i need to optimize it in order to achieve variable-less script and that is all! I consulted this article which describes how to pivot data.. but i want to make the script below EDIT section variable-less without variables.. https://stackoverflow.com/questions/3720728/how-to-design-query-for-creating-dynamic-columns-from-rows — Constantine Nikolopoulos, Jun 29 '17 at 07:23

score 0 · Answer 1 · answered Jul 03 '17 at 13:59

Taking your code

sp_executesql N'
select concat(''select * from sys.objects object_id id in ('',
(select object_id from sys.objects where object_id=1 and object_id=''30000000''),
'') order by object_id'')'

and removing one level of nesting (the sp_executesql) we get

select concat('select * from sys.objects object_id id in (',
(select object_id from sys.objects where object_id=1 and object_id='30000000'),
') order by object_id')

which of course outputs

select * from sys.objects object_id id in () order by object_id

as you observed.

I think you should do:

sp_executesql @query

sp_executesql or execute without variables

1 Answers1