mssql syntax error in stored proc

[sujiforsql]

i have a stored procedure like below[code=mssql]
CREATE PROCEDURE display_Product s
@CategoryID int
AS
BEGIN
DECLARE @authors_cursor CURSOR
SET @authors_cursor =CURSOR FAST_FORWARD
FOR SELECT TOP 10 COUNT(iProductF K) AS s ,iProductFK
FROM tblOrderDetail WHERE iProductFK IN(SELECT iProductFK FROM tblCategoryProd uct WHERE iCategoryFK = @CategoryID)
GROUP BY iProductFK
ORDER BY s desc
DECLARE @a int
DECLARE @b int
declare @c varchar(200)
OPEN @authors_cursor
FETCH NEXT FROM @authors_cursor INTO @a, @b
WHILE @@FETCH_STATUS = 0
BEGIN
set @c=@c+convert(v archar,@b)+'*'
FETCH NEXT FROM @authors_cursor INTO @a, @b
END
CLOSE @authors_cursor
DEALLOCATE @authors_cursor
return @c
END
GO
[/code]

when i try to execute this procedure from a php page
this was the code i put[php]
mssql_bind($sq_ top,"@CategoryI D",$LintCatID,S QLINT4);
mssql_bind($sq_ top,"RETVAL",st ripslashes($pID ),SQLVARCHAR,fa lse, false, 200);
$res_top=mssql_ execute($sq_top );[/php]
This was the error message i got

Warning: mssql_execute() [function.mssql-execute]: message: Syntax error converting the varchar value '71643*71318*20 114*258681*2009 7*232296*42432* 71501*71410*233 548*' to a column of data type int. (severity 16) in C:\wamp\www\lio n\subcat.php on line 45

Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed in C:\wamp\www\lio n\subcat.php on line 45

if anybody know how to solve this please help me

[ronverdonk]

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[ronverdonk]

Problem is in your stored procedure, not in PHP.

I will move this thread to the appropriate forum.

moderator

[deepuv04]

i have a stored procedure like below[code=mssql]
CREATE PROCEDURE display_Product s
@CategoryID int
AS
BEGIN
DECLARE @authors_cursor CURSOR
SET @authors_cursor =CURSOR FAST_FORWARD
FOR SELECT TOP 10 COUNT(iProductF K) AS s ,iProductFK
FROM tblOrderDetail WHERE iProductFK IN(SELECT iProductFK FROM tblCategoryProd uct WHERE iCategoryFK = @CategoryID)
GROUP BY iProductFK
ORDER BY s desc
DECLARE @a int
DECLARE @b int
declare @c varchar(200)
OPEN @authors_cursor
FETCH NEXT FROM @authors_cursor INTO @a, @b
WHILE @@FETCH_STATUS = 0
BEGIN
set @c=@c+convert(v archar,@b)+'*'
FETCH NEXT FROM @authors_cursor INTO @a, @b
END
CLOSE @authors_cursor
DEALLOCATE @authors_cursor
return @c
END
GO
[/code]

when i try to execute this procedure from a php page
this was the code i put[php]
mssql_bind($sq_ top,"@CategoryI D",$LintCatID,S QLINT4);
mssql_bind($sq_ top,"RETVAL",st ripslashes($pID ),SQLVARCHAR,fa lse, false, 200);
$res_top=mssql_ execute($sq_top );[/php]
This was the error message i got

Warning: mssql_execute() [function.mssql-execute]: message: Syntax error converting the varchar value '71643*71318*20 114*258681*2009 7*232296*42432* 71501*71410*233 548*' to a column of data type int. (severity 16) in C:\wamp\www\lio n\subcat.php on line 45

Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed in C:\wamp\www\lio n\subcat.php on line 45

if anybody know how to solve this please help me

Hi,

First initialize @c to empty string after your declaration.
and in the while loop change the statement
set @c=@c+convert(v archar,@b)+'*' to
set @c=@c+convert(v archar(10),@b)+ '*'

can you tell me what is the data type of column iProductFK

thanks

[sujiforsql]

Hi,

First initialize @c to empty string after your declaration.
and in the while loop change the statement
set @c=@c+convert(v archar,@b)+'*' to
set @c=@c+convert(v archar(10),@b)+ '*'

can you tell me what is the data type of column iProductFK

thanks

iProductFK is of type int with size 4