Error on this sp - 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

Transact-SQL (2000)

Error on this sp

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-02 : 05:25:49

I ma getting the error 'Incorrect syntax near the keyword 'RETURN' on this sp. Do I need to use EXEC instead and if so can someome help me with the syntax of that please.

CREATE PROCEDURE spRB_CountUnavailableDates (
@mycount INT OUTPUT,
@WHEREClause nvarchar(4000)

)
AS

BEGIN
SELECT @mycount = COUNT(*)
FROM vweFacilityCheck where @WHEREClause

RETURN
END
GO

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-02 : 05:33:53

u need to build the sql statement dynamic
try this ...
@sqlString = 'select COUNT(*)into #tempTable
FROM vweFacilityCheck where' + @whereClause
exec @sqlstring
Select @mycount= Select * from #tempTable

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 05:35:29

... and it would be better (efficiency-wise) if you could parameterise the query and use sp_ExecuteSQL (instead of EXEC @sqlString)

Kristen

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-02 : 05:42:39

Thank you. I now have this which does not produce an error on syntax check. But if I add the line Select @mycount= Select * from #tempTable it does. 'Incorrect syntax near the keyword SELECT' Also, How do I return the count in this instance ?

CREATE Procedure [spRB_FacilityCheck]

@WHEREClause varchar(4000)

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(4000)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement

SELECT @sqlStatement = "select COUNT(*)into #tempTable
FROM vweFacilityCheck where"+ @whereClause

exec(@sqlStatement)

Select @mycount= Select * from #tempTable

GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-02 : 05:46:13

Select @mycount= Select * from #tempTable

Should be

Select @mycount= count(*) from #tempTable

Madhivanan

Failing to plan is Planning to fail

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-02 : 05:46:58

yeah there was an error
@sqlString = 'select COUNT(*)as Col1 into #tempTable
FROM vweFacilityCheck where' + @whereClause
exec @sqlstring
Select @mycount= Col1 from #tempTable

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-02 : 05:59:00

I am now getting this error'Line 2: Incorrect syntax near '='. Invalid object name '#tempTable'.

CREATE Procedure [spRB_FacilityCheck]

@WHEREClause varchar(4000),
@mycount INT OUTPUT

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(4000)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement

SELECT @sqlStatement = "select COUNT(*)as Col1 into #tempTable
FROM vweFacilityCheck where"+ @whereClause

exec(@sqlStatement)

Select @mycount= Col1 from #tempTable

RETURN
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-02 : 06:14:14

You need to use #tempTable in the same scope

In this statement it is out of scope
Select @mycount= Col1 from #tempTable

Why do you want to do this using Dynamic SQL?

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-02 : 06:23:30

I thought I had to use dynamic sql. I am building my WHERE clause in asp.net on my web page. I want to count the records that this where clause extracts. It could be several or none. I didn't know what other way to do it.I have written dynamic sql sp using a WHERE clause and also just done an ordinary count (not using dynamic sql). I was trying to combine the two.

I have tried this now - no good either

CREATE Procedure [spRB_FacilityCheck]

@WHEREClause varchar(4000),
@mycount INT OUTPUT

AS

-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(4000)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement

SELECT @sqlStatement = "SELECT @mycount = COUNT(*) FROM vweFacilityCheck where "+ @whereClause

exec(@sqlStatement)

RETURN
GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-02 : 06:44:40

http://mindsdoor.net/SQLTsql/sp_executeSQL.html

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2005-12-02 : 10:46:39

I'm afraid I couldn't understand that. Thanks for your help anyway

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources