Stored procedure - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Stored procedure

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-15 : 20:09:47

I have the following stored procedure. I copied the SQL statement directly from a view that works, but when I try to run the procedure I get an error stating:

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'JOIN'.

CREATE proc [dbo].[search_pmCode]
@descn nvarchar(200)
as
declare @sch nvarchar(1000)
select @sch = 'SELECT MFGPRO.dbo.[ANA No Dups].ITEM, MFGPRO.dbo.[ANA No Dups].DESC1, MFGPRO.dbo.[ANA No Dups].STATUS AS ana_s,
MFGPRO.dbo.[ANA No Dups].PTPMCODE AS ana, MFGPRO.dbo.[BRI No Dups].STATUS AS bri_s, MFGPRO.dbo.[BRI No Dups].PTPMCODE AS bri,
MFGPRO.dbo.[DEN No Dups].STATUS AS den_s, MFGPRO.dbo.[DEN No Dups].PTPMCODE AS den, MFGPRO.dbo.[FTM No Dups].STATUS AS ftm_s,
MFGPRO.dbo.[FTM No Dups].PTPMCODE AS ftm, MFGPRO.dbo.[TEC No Dups].STATUS AS tec_s, MFGPRO.dbo.[TEC No Dups].PTPMCODE AS tec,
MFGPRO.dbo.[WAY No Dups].STATUS AS way_s, MFGPRO.dbo.[WAY No Dups].PTPMCODE AS way
FROM MFGPRO.dbo.[ANA No Dups] FULL OUTER JOIN
MFGPRO.dbo.[FTM No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[FTM No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[WAY No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[WAY No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[BRI No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[BRI No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[TEC No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[TEC No Dups].ITEM FULL OUTER JOIN
MFGPRO.dbo.[DEN No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[DEN No Dups].ITEM
WHERE (MFGPRO.dbo.[ANA No Dups].ITEM = '
select @sch = @sch + @descn

exec ( @sch)
GO

What an I doing wrong?

Thanks,
Lane

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-15 : 20:47:20

I think you need to quote your @descn

eg

....
WHERE (MFGPRO.dbo.[ANA No Dups].ITEM = '
select @sch = @sch + '''' + @descn + ''''

everything else looks OK to me....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-16 : 12:16:23

Thanks, but I am still getting the same message:

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'JOIN'.

Thanks for your help!
Lane

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-16 : 12:32:40

I have a question: why are you using dynamic SQL? Doesn't this work:

CREATE proc [dbo].[search_pmCode]
@descn nvarchar(200)
as
SELECT MFGPRO.dbo.[ANA No Dups].ITEM, MFGPRO.dbo.[ANA No Dups].DESC1,
MFGPRO.dbo.[ANA No Dups].STATUS AS ana_s,
MFGPRO.dbo.[ANA No Dups].PTPMCODE AS ana, MFGPRO.dbo.[BRI No Dups].STATUS AS bri_s,
MFGPRO.dbo.[BRI No Dups].PTPMCODE AS bri,
MFGPRO.dbo.[DEN No Dups].STATUS AS den_s, MFGPRO.dbo.[DEN No Dups].PTPMCODE AS den,
MFGPRO.dbo.[FTM No Dups].STATUS AS ftm_s,
MFGPRO.dbo.[FTM No Dups].PTPMCODE AS ftm, MFGPRO.dbo.[TEC No Dups].STATUS AS tec_s,
MFGPRO.dbo.[TEC No Dups].PTPMCODE AS tec,
MFGPRO.dbo.[WAY No Dups].STATUS AS way_s, MFGPRO.dbo.[WAY No Dups].PTPMCODE AS way
FROM MFGPRO.dbo.[ANA No Dups] FULL OUTER JOIN
MFGPRO.dbo.[FTM No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[FTM No Dups].ITEM
FULL OUTER JOIN
MFGPRO.dbo.[WAY No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[WAY No Dups].ITEM
FULL OUTER JOIN
MFGPRO.dbo.[BRI No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[BRI No Dups].ITEM
FULL OUTER JOIN
MFGPRO.dbo.[TEC No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[TEC No Dups].ITEM
FULL OUTER JOIN
MFGPRO.dbo.[DEN No Dups] ON MFGPRO.dbo.[ANA No Dups].ITEM = MFGPRO.dbo.[DEN No Dups].ITEM
WHERE MFGPRO.dbo.[ANA No Dups].ITEM = @descn

Also, in your original post, you have an open parenthesis without a closing one.

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-16 : 12:42:37

Your right, I didn't need to use dyn SQL.

Thanks for your help!
Lane

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-16 : 13:04:03

I know Rob gave you an updated script, but I wanted to let you know what possibly caused the error. Your variable @sch is declared as ntext (1000). My guess is that your sql statement is longer than 1000 characters. If this is the case, the first 1000 characters of the SQL statement would be retained in the variable leaving an incomplete SQL statement when you run the exec(@sch). One way to check is to comment out the exec(@sch) and add a PRINT @sch and run the procedure from query analyzer.

I wanted to let you know so that if you ever have the need to run dynamic sql, you will be aware of one of the things that can come up to bite you. I know I spent hours the first time I tried this only to find out my variable was not large enough to fit the entire statement.

Jeremy

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-05-16 : 19:56:34

Yip that'll do it....I lost count at 13...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources