mssql syntax error in stored proc

[sujiforsql]

i have a stored procedure like below

CREATE PROCEDURE display_Products
@CategoryID int
 AS
BEGIN
     DECLARE @authors_cursor CURSOR
    SET @authors_cursor =CURSOR FAST_FORWARD
    FOR SELECT TOP 10 COUNT(iProductFK) AS s ,iProductFK 
    FROM tblOrderDetail  WHERE iProductFK IN(SELECT iProductFK FROM tblCategoryProduct 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(varchar,@b)+'*'
        FETCH NEXT FROM @authors_cursor INTO @a, @b
    END
CLOSE @authors_cursor
DEALLOCATE @authors_cursor
return @c
END
GO
 

when i try to execute this procedure from a php page
this was the code i put[php]
mssql_bind($sq_top,"@CategoryID",$LintCatID,SQLINT 4);
mssql_bind($sq_top,"RETVAL",stripslashes($pID),SQL VARCHAR,false, 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*20114*258681*20097*232296*42432*71501 *71410*233548*' to a column of data type int. (severity 16) in C:\wamp\www\lion\subcat.php on line 45

Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed in C:\wamp\www\lion\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

CREATE PROCEDURE display_Products
@CategoryID int
 AS
BEGIN
     DECLARE @authors_cursor CURSOR
    SET @authors_cursor =CURSOR FAST_FORWARD
    FOR SELECT TOP 10 COUNT(iProductFK) AS s ,iProductFK 
    FROM tblOrderDetail  WHERE iProductFK IN(SELECT iProductFK FROM tblCategoryProduct 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(varchar,@b)+'*'
        FETCH NEXT FROM @authors_cursor INTO @a, @b
    END
CLOSE @authors_cursor
DEALLOCATE @authors_cursor
return @c
END
GO
 

when i try to execute this procedure from a php page
this was the code i put[php]
mssql_bind($sq_top,"@CategoryID",$LintCatID,SQLINT 4);
mssql_bind($sq_top,"RETVAL",stripslashes($pID),SQL VARCHAR,false, 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*20114*258681*20097*232296*42432*71501 *71410*233548*' to a column of data type int. (severity 16) in C:\wamp\www\lion\subcat.php on line 45

Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed in C:\wamp\www\lion\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(varchar,@b)+'*' to
set @c=@c+convert(varchar(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(varchar,@b)+'*' to
set @c=@c+convert(varchar(10),@b)+'*'

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

thanks

iProductFK is of type int with size 4