need help with syntax-EXEC i get an error

Question

need help with syntax-EXEC i get an error

midan1

SSCertifiable

Points: 5100
More actions
July 25, 2003 at 7:33 am

#83939

i get an error
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@recipient'.
what is the problem ???
-------------------------
DECLARE @recipient VARCHAR(1000)
DECLARE @pstrNames VARCHAR(8000)
SET @pstrNames = ''
-- Append all results into a variable
SELECT @pstrNames = @pstrNames + ISNULL(email, '') + ';'
FROM dbo.em
-- Remove the comma at the end
IF ( RIGHT(@pstrNames, 1) = ',' )
SET @pstrNames = LEFT(@pstrNames, LEN(@pstrNames) - 1)
@recipient='select @pstrNames'
EXEC (@recipient)
--------------------------
thnks ilan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

quote:
i get an error
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '@recipient'.
what is the problem ???
-------------------------
DECLARE @recipient VARCHAR(1000)
DECLARE @pstrNames VARCHAR(8000)
SET @pstrNames = ''
-- Append all results into a variable
SELECT @pstrNames = @pstrNames + ISNULL(email, '') + ';'
FROM dbo.em
-- Remove the comma at the end
IF ( RIGHT(@pstrNames, 1) = ',' )
SET @pstrNames = LEFT(@pstrNames, LEN(@pstrNames) - 1)
@recipient='select @pstrNames'
EXEC (@recipient)
--------------------------

´

change

@recipient='select @pstrNames'

to

SET or SELECT @recipient='select @pstrNames'

should work.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

midan1 SSCertifiable Points: 5100 More actions · Answer 2

NO

i test the tow possibility

and still a gen an error

---------------

when i do this

set @recipient='select @pstrNames'

OR

this

select @recipient='select @pstrNames'

i get this

Server: Msg 137, Level 15, State 2, Line 1

Must declare the variable '@pstrNames'.

-----------------------------------

thnks

ilan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi ilan,

quote:
NO
i test the tow possibility
and still a gen an error
---------------
when i do this
set @recipient='select @pstrNames'
OR
this
select @recipient='select @pstrNames'
i get this
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@pstrNames'.
-----------------------------------

is this the full statement you've posted or is there a GO somewhere in between?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

midan1 SSCertifiable Points: 5100 More actions · Answer 4

this is the all code

-------------------

DECLARE @recipient VARCHAR(1000)

DECLARE @pstrNames VARCHAR(8000)

SET @pstrNames =''

-- Append all results into a variable

SELECT @pstrNames = @pstrNames + ISNULL(email, '') + ';'

FROM dbo.em

-- Remove the comma at the end

IF ( RIGHT(@pstrNames, 1) = ',' )

SET @pstrNames = LEFT(@pstrNames, LEN(@pstrNames) - 1)

exec master.dbo.xp_SMTPSendMail80

@query='SELECT * FROM [mira].[dbo].[UserBirthDay] WHERE (BirthdayToday = 1) ',

@address='out.zahav.net.il',

@subject='Subject Line',

@from='midan1@zahav.net.il',

@html=1, -- Send the results as html

@Body='<html><header>header</header><body>Body Text</html></body>',

@qTableCaption='This is a Caption',

@qTableAttribute='border=2',

@recipient='select @pstrNames'

exec (@recipient)

----------------------------

i insulated the first code to see what is not

ok

with @recipient!!!

---------------------

i think if the first code will work

all the code will be ok

---------------------

when i run the code i dont get any error

but i can not get an output EMAIL

ilan

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Two things

Firstly your code

-- Remove the comma at the end

IF ( RIGHT(@pstrNames, 1) = ',' )

SET @pstrNames = LEFT(@pstrNames, LEN(@pstrNames) - 1)

You will not get a comma (,) at the end of @pstrName, only a semi colon (;)

Secondly

@recipient='select @pstrNames'

exec (@recipient)

should be

@recipient=@pstrNames

you do not want the exec

Edited by - davidburrows on 07/28/2003 06:24:42 AM

Far away is close at hand in the images of elsewhere.
Anon.

midan1 SSCertifiable Points: 5100 More actions · Answer 6

midan1

SSCertifiable

Points: 5100

July 28, 2003 at 11:26 am

#467331

thnks it work 100%

ilan