Call Stored Procedure ERRO - NEED ADVICE

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)

Call Stored Procedure ERRO - NEED ADVICE

Author

Topic

kingkong
Starting Member

16 Posts

Posted - 2004-10-21 : 17:08:20

Hello:
I received the error as below , but couldn't figure out, .... Any Advice will appreciate!

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

///////////////////////////////////////////////////////
CREATE PROCEDURE [asi_admin].[sp_EMP_Test]
@SortBy varchar,
@SortDir varchar
AS

SET NOCOUNT ON

DECLARE @SQL varchar(1000)
SELECT @SQL = 'SELECT * from EMP ORDER BY'
SELECT @SQL = @SQL + ' ' + @SortBy + ' ' + @SortDir
EXEC(@SQL )

///////////////////////////////////////////////////////

EXEC SP_EMP_Test 'Emp_id','ASC'

///////////////////////////////////////////////////////

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 17:09:57

You don't need dynamic SQL for this. Please check out this article for a faster solution to a dynamic ORDER BY:

http://www.sqlteam.com/item.asp?ItemID=2209

Make sure to check out the comment section of the article.

To fix your problem, instead of EXEC(@SQL), run PRINT @SQL to determine what is wrong.

Tara

kingkong
Starting Member

16 Posts

Posted - 2004-10-21 : 17:19:16

quote:
Originally posted by tduggan

You don't need dynamic SQL for this. Please check out this article for a faster solution to a dynamic ORDER BY:

http://www.sqlteam.com/item.asp?ItemID=2209

Make sure to check out the comment section of the article.

To fix your problem, instead of EXEC(@SQL), run PRINT @SQL to determine what is wrong.

Tara

I try Print @SQL & return the
SELECT * from FBMI ORDER BY E A
Can you explain why .... if you have times?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 17:22:32

You haven't given a size to the data type. varchar isn't enough. And you shouldn't be using dynamic SQL here when there is an alternative solution. Dynamic SQL is bad for performance and security reasons.

Tara

kingkong
Starting Member

16 Posts

Posted - 2004-10-21 : 17:23:59

quote:
Originally posted by kingkong

quote:
Originally posted by tduggan

You don't need dynamic SQL for this. Please check out this article for a faster solution to a dynamic ORDER BY:

http://www.sqlteam.com/item.asp?ItemID=2209

Make sure to check out the comment section of the article.

To fix your problem, instead of EXEC(@SQL), run PRINT @SQL to determine what is wrong.

Tara

I try Print @SQL & return the
SELECT * from FBMI ORDER BY E A
Can you explain why .... if you have times?

I got it, THANkS
@SortBy varchar(50)
@SortDir varchar(50)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-21 : 17:32:42

You should really check out that article. You should not have a stored procedure like this.

Tara

kingkong
Starting Member

16 Posts

Posted - 2004-10-21 : 18:24:44

quote:
Originally posted by tduggan

You should really check out that article. You should not have a stored procedure like this.

Tara

Tara:
Thanks for this article. I am going to modify it. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources