Error in the select statement - 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 the select statement

Author

Topic

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-12-18 : 02:52:50

Hi,

I am getting the following error message in the stored procedure. Please help

Error Message

Msg 102, Level 15, State 1, Procedure publicGetID, Line
Incorrect syntax near '@count'.

Stored Procedure - code

CREATE PROCEDURE dbo. publicGetID
@q nvarchar(40),
@type nvarchar(40),
@count int = 0
AS

if @cnt = 'test'
BEGIN
select distinct top @count col1
from table1
where (col1 Like + '%' + @q + '%')
order by col1
END

SA

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-18 : 03:01:41

Hi

Try this...



CREATE PROCEDURE dbo. publicGetID
	@Q nvarchar(40),
	@type nvarchar(40),
	@count int = 0
AS
	DECLARE @SQL NVARCHAR(MAX)

IF @TYPE = 'TEST'
BEGIN

	SET @q	=	 '%' + @q + '%'

	SET @sql	=	'SELECT DISTINCT TOP '+@COUNT +'COL1
	FROM TABLE1
	WHERE (COL1 LIKE '+ @Q +')
	ORDER BY COL1'

	EXEC SP_EXECUTESQL @sql

END

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-18 : 03:28:11

It should be

CREATE PROCEDURE dbo. publicGetID
@q nvarchar(40),
@type nvarchar(40),
@count int = 0
AS

if @cnt = 'test'
BEGIN
select distinct top (@count) col1
from table1
where (col1 Like + '%' + @q + '%')
order by col1
END

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-18 : 03:40:40

HI

Yes
Only thing we need to declare this @cnt variable or its may be @type param.

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-18 : 04:13:58

quote:
Originally posted by rajdaksha

HI

Yes
Only thing we need to declare this @cnt variable or its may be @type param.

-------------------------
R...

Are you replying to me?
The only change is to use braces around the variable name when used with TOP operator

Madhivanan

Failing to plan is Planning to fail

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-18 : 04:39:24

Hi

Not for you..

if @cnti think @cnt not declare = 'test'

-------------------------
R...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-18 : 04:55:18

quote:
Originally posted by rajdaksha

Hi

Not for you..

if @cnti think @cnt not declare = 'test'

-------------------------
R...

OK. I understand your point

Madhivanan

Failing to plan is Planning to fail

agarwasa2008
Posting Yak Master

109 Posts

Posted - 2009-12-18 : 14:37:19

Thank you to both of you. I appreciate your wisdom.
I will be using madhivanan solution as it involves an easy fix. Thanks again!!

SA

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-21 : 09:16:32

quote:
Originally posted by agarwasa2008

Thank you to both of you. I appreciate your wisdom.
I will be using madhivanan solution as it involves an easy fix. Thanks again!!

SA

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources