Dynamic SQL question

Can someone tell me why the exec statement throws the following error?
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

Code Snippet

create table #t (Val1 smallint, Val2 smallint )
go
DECLARE @.Diff as int;
set nocount on

insert into #t values(10, 15)

--Why does this work?
SET @.Diff=(SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)
select @.Diff

--And yet this does not?
EXEC('(SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)')

drop table #t
go

Thanks

Colin

It is because you dont have a SELECT or anything in your dynamic sql.

change to:

Code Snippet

EXEC('SELECT (SELECT Val2 FROM #t) - (SELECT Val1 FROM #t)')

and it works fine.

Dynamic SQL help needed

I have some dynamic SQL code that is executed using the following:

SET @.SQLSTRING = @.SELECT+@.FROM+@.WHEREPARM+@.ORDER

EXEC sp_executesql @.SQLSTRING

and this works fine if my @.WHEREPARM is

SET @.WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate'

However, it fails if my @.WHEREPARM is

SET @.WHEREPARM = ' Where srm.EVENT_HISTORY.EVENT_DATE <> ampfm.rpt_Abstract.AbsCompleteDate

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = '460''

I am getting the following error: Incorrect syntax near '460'

I believe I need to change the number of apostrophes around the 460 value but can't quite figure this out. Can someone provide me a correct example?

Thanks!

Try 3 quotes at the end: '460'''

|||

You are correct channge:

Code Snippet

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = '460''

to

Code Snippet

and srm.EVENT_HISTORY.EVENT_TYPE_KEY = ''460'''

|||

Thank you both for the quick response. The ''460''' did the trick.