Error in 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 2005 Forums

Transact-SQL (2005)

Error in Stored Procedure

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-10 : 04:48:30

Hi, I am getting below error when i am trying to execute it. Can any one give the solution or correct this one. Look Error in Bold line.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Top'.

ALTER Procedure GetCulture
(
@Zipcode INT,
@Querystr Varchar(200),
@TblName Varchar(100),
@existsZip INT OUTPUT
)
AS
Begin
SET NOCOUNT ON

DECLARE @Result VARCHAR(MAX),
@ZcodeToUse INT,
@CondStmt1 nvarchar(200)

Set @CondStmt1 = 'SELECT @ex = COUNT(1) from ' + @TblName + '
WHERE ZipData = ' + Convert(varchar(30),@Zipcode)
EXEC sp_executesql @CondStmt1, N'@ex INT OUTPUT', @existsZip OUTPUT

if (@existsZip >0 )
Begin
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@Zipcode) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
End
else
Begin

Set @CondStmt1 = 'SELECT @ex = Top 1 ZipData from ' + @TblName + ' Order by ABS(ZipData = ' + Convert(varchar(30),@Zipcode) + ')' EXEC sp_executesql @CondStmt1, N'@ex INT OUTPUT', @existsZip OUTPUT

if (@existsZip >0 )
Begin
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM ' + @TblName + ' WHERE ZipData =' + Convert(varchar(30),@existsZip) + ') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
End

END
Exec(@Result)
--Print @Result
End

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-10 : 05:06:04

Set @CondStmt1 = 'SELECT top 1 @ex = ZipData from ' + @TblName + ' Order by ABS(ZipData = ' + Convert(varchar(30),@Zipcode) + ')'

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-10 : 07:07:22

Still getting the below Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

At same line.

quote:
Originally posted by asgast

Set @CondStmt1 = 'SELECT top 1 @ex = ZipData from ' + @TblName + ' Order by ABS(ZipData = ' + Convert(varchar(30),@Zipcode) + ')'

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-10 : 07:17:01

Set @CondStmt1 = 'SELECT top 1 '+@ex +'= ZipData from ' + @TblName + ' Order by ABS(ZipData = ' + Convert(varchar(30),@Zipcode) + ')'

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-10 : 07:35:59

I think your error is here Order by ABS(ZipData = ' + Convert(varchar(30),@Zipcode) + ')
if you are trying to filter then move your ZipData = ' + Convert(varchar(30),@Zipcode) + ' to a where
you can't have = in ORDER BY part

I think you are trying to have something like this
Set @CondStmt1 = 'SELECT top 1 @ex = ZipData from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)+' Order by ABS(ZipData)'

bklr your solution will not work

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-09-10 : 07:51:37

No, I am trying to find the closest zipcode. The query should follow order by before filter.

quote:
Originally posted by asgast

I think your error is here Order by ABS(ZipData = ' + Convert(varchar(30),@Zipcode) + ')
if you are trying to filter then move your ZipData = ' + Convert(varchar(30),@Zipcode) + ' to a where
you can't have = in ORDER BY part

I think you are trying to have something like this
Set @CondStmt1 = 'SELECT top 1 @ex = ZipData from ' + @TblName + ' WHERE ZipData = ' + Convert(varchar(30),@Zipcode)+' Order by ABS(ZipData)'

bklr your solution will not work

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-10 : 08:03:03

sorry in sql order by follows filter this is required sintax

1 first you get the data you need
2 then you order it

no other way

What is your logic for looking for a closest zip?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources