By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

mssql syntax error in stored proc

P: 2
i have a stored procedure like below
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE display_Products
  2. @CategoryID int
  3.  AS
  4. BEGIN
  5.      DECLARE @authors_cursor CURSOR
  6.     SET @authors_cursor =CURSOR FAST_FORWARD
  7.     FOR SELECT TOP 10 COUNT(iProductFK) AS s ,iProductFK 
  8.     FROM tblOrderDetail  WHERE iProductFK IN(SELECT iProductFK FROM tblCategoryProduct WHERE iCategoryFK = @CategoryID)
  9.     GROUP BY iProductFK
  10.     ORDER BY s desc
  11.     DECLARE @a int
  12.      DECLARE @b int
  13.     declare @c varchar(200)
  14.     OPEN @authors_cursor
  15.     FETCH NEXT FROM @authors_cursor  INTO @a, @b
  16.     WHILE @@FETCH_STATUS = 0
  17.     BEGIN
  18.         set @c=@c+convert(varchar,@b)+'*'
  19.         FETCH NEXT FROM @authors_cursor INTO @a, @b
  20.     END
  21. CLOSE @authors_cursor
  22. DEALLOCATE @authors_cursor
  23. return @c
  24. END
  25. GO
  26.  
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
Mar 19 '08 #1
Share this Question
Share on Google+
4 Replies


ronverdonk
Expert 2.5K+
P: 4,258
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
Mar 19 '08 #2

ronverdonk
Expert 2.5K+
P: 4,258
Problem is in your stored procedure, not in PHP.

I will move this thread to the appropriate forum.

moderator
Mar 19 '08 #3

deepuv04
Expert 100+
P: 227
i have a stored procedure like below
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE display_Products
  2. @CategoryID int
  3.  AS
  4. BEGIN
  5.      DECLARE @authors_cursor CURSOR
  6.     SET @authors_cursor =CURSOR FAST_FORWARD
  7.     FOR SELECT TOP 10 COUNT(iProductFK) AS s ,iProductFK 
  8.     FROM tblOrderDetail  WHERE iProductFK IN(SELECT iProductFK FROM tblCategoryProduct WHERE iCategoryFK = @CategoryID)
  9.     GROUP BY iProductFK
  10.     ORDER BY s desc
  11.     DECLARE @a int
  12.      DECLARE @b int
  13.     declare @c varchar(200)
  14.     OPEN @authors_cursor
  15.     FETCH NEXT FROM @authors_cursor  INTO @a, @b
  16.     WHILE @@FETCH_STATUS = 0
  17.     BEGIN
  18.         set @c=@c+convert(varchar,@b)+'*'
  19.         FETCH NEXT FROM @authors_cursor INTO @a, @b
  20.     END
  21. CLOSE @authors_cursor
  22. DEALLOCATE @authors_cursor
  23. return @c
  24. END
  25. GO
  26.  
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
Mar 19 '08 #4

P: 2
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
Mar 24 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.