What is wrong with this? - 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)

What is wrong with this?

Author

Topic

panayiotis
Starting Member

16 Posts

Posted - 2008-05-04 : 08:15:26

select * from dbo.Advertisement_Search '%'

Advertisement_Search is a store procedure and i am trying this in ms sql.

Thanks in advance.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-04 : 09:28:53

What are you trying to achieve with the query?You cannot use select statement for a stored proc.It only works on UDF & why is that '%' there for?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-04 : 09:50:58

I think you're trying to invoke the stored procedure with parameter value as %. You should use EXEC for that

EXEC dbo.Advertisement_Search '%'

and if you want to use it in select statement, Advertisement_Search should be a UDF and statement becomes:-

select * from dbo.Advertisement_Search ('%')

panayiotis
Starting Member

16 Posts

Posted - 2008-05-04 : 10:14:03

quote:
Originally posted by ayamas

What are you trying to achieve with the query?You cannot use select statement for a stored proc.It only works on UDF & why is that '%' there for?

I have changed my approach but still i have problems.

@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max)
DECLARE @Result int

SET @SQL = 'Return COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID
AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''')
AND (' + @KeywordsFilter + ')'

SET @Result = (EXEC sp_executesql @SQL)
END

The error is :
Msg 156, Level 15, State 1, Procedure Advertisement_AdvanceSearch_Count, Line 24
Incorrect syntax near the keyword 'EXEC'.

At the end i need to write Return @Result

Can you please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-04 : 10:26:09

[code]@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max)
DECLARE @Result int

SET @SQL = 'SELECT COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID
AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''')
AND (' + @KeywordsFilter + ')'

EXEC (@SQL)
END[/code]

You dont require sp_executesql and extra variable @Result. You can use EXEC. Also you have an incomplete condition specified.

panayiotis
Starting Member

16 Posts

Posted - 2008-05-04 : 10:32:23

quote:

Originally posted by visakh16

@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max)
DECLARE @Result int

SET @SQL = 'SELECT COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID 
AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''') 
AND (' + @KeywordsFilter + ')'

EXEC (@SQL)
END

You dont require sp_executesql and extra variable @Result. You can use EXEC. Also you have an incomplete condition specified.

Hmmm ... There is no return though

alter Function [dbo].[Advertisement_AdvanceSearch_Count](
@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max)

SET @SQL = 'Return COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID
AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''')
AND (' + @KeywordsFilter + ')'

EXEC (@SQL)

END

Msg 443, Level 16, State 14, Procedure Advertisement_AdvanceSearch_Count, Line 22
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
Msg 455, Level 16, State 2, Procedure Advertisement_AdvanceSearch_Count, Line 22
The last statement included within a function must be a return statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-04 : 11:16:27

quote:
Originally posted by panayiotis

quote:
Originally posted by visakh16

@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max)
DECLARE @Result int

SET @SQL = 'SELECT COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID
AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''')
AND (' + @KeywordsFilter + ')'

EXEC (@SQL)
END

You dont require sp_executesql and extra variable @Result. You can use EXEC. Also you have an incomplete condition specified.

Hmmm ... There is no return though

alter Function [dbo].[Advertisement_AdvanceSearch_Count](
@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max)

SET @SQL = 'Return COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID
AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''')
AND (' + @KeywordsFilter + ')'

EXEC (@SQL)

END

Msg 443, Level 16, State 14, Procedure Advertisement_AdvanceSearch_Count, Line 22
Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.
Msg 455, Level 16, State 2, Procedure Advertisement_AdvanceSearch_Count, Line 22
The last statement included within a function must be a return statement.

You cant use return like this. I think you can try like this. I still didnt understand the purpose of last fileter condition. I think its incomplete.

alter Function [dbo].[Advertisement_AdvanceSearch_Count](
@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max) 

SET @SQL = 'DECLARE @Return int;SELECT @Return=COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID 
AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''') 
AND (' + @KeywordsFilter + ')'

EXEC (@SQL)
RETURN @Return

END

panayiotis
Starting Member

16 Posts

Posted - 2008-05-04 : 13:45:07

I am afraid that it doesnt work as @Return doesnt exists.

Msg 137, Level 15, State 2, Procedure Advertisement_AdvanceSearch_Count, Line 24
Must declare the scalar variable "@Return".

panayiotis
Starting Member

16 Posts

Posted - 2008-05-04 : 15:21:23

quote:
Originally posted by panayiotis

I am afraid that it doesnt work as @Return doesnt exists.

Msg 137, Level 15, State 2, Procedure Advertisement_AdvanceSearch_Count, Line 24
Must declare the scalar variable "@Return".

I have found the answer... In case anyone wants to do something similar.

ALTER Function [dbo].[Advertisement_AdvanceSearch_Count](
@TypeIDFilter varchar(50),
@LocationIDFilter varchar(50),
@CategoryIDFilter varchar(50),
@KeywordsFilter varchar(max))
RETURNS int
AS
BEGIN

DECLARE @SQL nvarchar(max);
DECLARE @ParmDefinition nvarchar(500);

DECLARE @ReturnValue int;

SET @SQL = 'SELECT @ReturnOUT=COUNT(*) FROM Advertisement A, EnglishAdDetails B, Category D, Location E
WHERE A.AdvertisementID = B.AdvertisementID AND
A.CategoryID = D.CategoryID AND A.LocationID=E.LocationID
AND (A.Type LIKE ''' + @TypeIDFilter + ''') AND
(D.CategoryID like ''' + @CategoryIDFilter + ''' OR D.ParentID like ''' + @CategoryIDFilter + ''')
AND
(E.LocationID like ''' + @LocationIDFilter + ''' OR E.LocationParentID like ''' + @LocationIDFilter + ''')
AND (' + @KeywordsFilter + ');'

SET @ParmDefinition = N'@ReturnOUT int OUTPUT';

EXEC sp_executesql @SQL,
@ParmDefinition,
@ReturnOUT = @ReturnValue OUTPUT;
RETURN @ReturnValue

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources