SPROC Error: Syntax error converting the varchar..

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)

SPROC Error: Syntax error converting the varchar..

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-09-01 : 13:22:58

Msg 245, Level 16, State 1, Procedure GetAffectedStudentCount, Line 13
Syntax error converting the varchar value 'SELECT COUNT(*) as Affected FROM Students WHERE ClientID = ' to a column of data type int.


ALTER PROCEDURE [dbo].[GetAffectedStudentCount] (@WhereClause NVARCHAR(500),@ClientID int)

AS
BEGIN
DECLARE @SQL varchar(255)
DECLARE @Counter int

SET @SQL = 'SELECT COUNT(*) as Affected FROM Students WHERE ClientID = ' + @ClientID + ' AND ' + @WhereClause

CREATE TABLE #myResults
(
    Col1 INT
)

INSERT INTO #myResults(Col1)
EXEC (@SQL)

DECLARE my_cursor CURSOR
FOR
 SELECT
    Col1
 FROM
    #myResults

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @Counter

RETURN @Counter

END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-01 : 14:54:52

I'm guessing that clientid in the students table is not integer. Also, what are you trying to accomplish with this query? There may be a better way to write it that avoids cursors and dynamic sql.

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources