SQL String in Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL String in Stored procedure

Author

Topic

Neilson
Starting Member

11 Posts

Posted - 2010-01-06 : 15:22:20

I have the following SP, but it is returning a syntax error on the where clause, I not sure whats up?

It says, " ' is not a valid identifier. "


set
ANSI_NULLS ON

set
QUOTED_IDENTIFIER ON

go


alter PROCEDURE [dbo].[spGetArticleList]

--spGetArticleList '182336bb-0556-4794-8383-eb09dd195b24'

@RetailerId varchar(50)

AS

Declare @SQLString as Varchar(max)

Set @SQLString = ' '
Set @SQLString = @SQLString + ' select	a.Id, PublicationDate, Headline, PreviewText, Body, LinkText, '
Set @SQLString = @SQLString + ' PreviewImagePath, PreviewImageText, MetaKeywords, UserId, u.FirstName '
Set @SQLString = @SQLString + ' from Article a  '
Set @SQLString = @SQLString + ' inner join Users u on u.id = a.UserID '

set @SQLString = @SQLString + ' where a.Id not in (select ArticleID from RetailerArticle where retailerId = ''' + @RetailerId + ''')'


Print
@SQLString

Execute
@SQLString

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-06 : 15:28:36

' where a.Id not in (select ArticleID from RetailerArticle where retailerId = '' + @RetailerId + '')'

I think I had it actually...

Neilson
Starting Member

11 Posts

Posted - 2010-01-06 : 15:39:23

Still gives me ' is not a valid identifier.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-06 : 15:44:02

Sorry try your code but put Execute (@SQLString) see if that works?

Neilson
Starting Member

11 Posts

Posted - 2010-01-06 : 15:48:49

Sweet, made the following changes and everything is fine:

Thanks :-)



' where a.Id not in (select ArticleID from RetailerArticle where retailerId = ''' + @RetailerId + ''')'


Print (@SQLString)

Execute (@SQLString)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 15:51:32

The problem is not the @SQLString!

Do just:
exec (@SQLString)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-06 : 15:52:19

oh sorry - too late

No, you're never too old to Yak'n'Roll if you're too young to die.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-06 : 15:52:42

Ya I know fred, I told him after to use his original code, he was missing the parenthasis. I just didn't edit my original reply, sorries!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-07 : 01:46:30

What is the need of dynamic sql when it can be done easily without it

set
ANSI_NULLS ON

set
QUOTED_IDENTIFIER ON

go

alter PROCEDURE [dbo].[spGetArticleList]

--spGetArticleList '182336bb-0556-4794-8383-eb09dd195b24'

@RetailerId varchar(50)

AS

select a.Id, PublicationDate, Headline, PreviewText, Body, LinkText,
PreviewImagePath, PreviewImageText, MetaKeywords, UserId, u.FirstName
from Article a inner join Users u on u.id = a.UserID
where a.Id not in (select ArticleID from RetailerArticle where retailerId = @RetailerId

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources