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,"@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
4 3307
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
Problem is in your stored procedure, not in PHP.
I will move this thread to the appropriate forum.
moderator
deepuv04 227
Recognized Expert New Member
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,"@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
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Philip Mette |
last post by:
I am in a crunch and need to covert this Oracle statement to MSSQL. Is
there any Oracle/MSSQL experts out there that can help me? I do not
understand the syntax enough to modify this.
Thanks so much for any assistance. Here is the procedure.
CREATE PROCEDURE UPD_ACTIVITY IS
CURSOR ACT_cur1
IS
SELECT DISTINCT A.ACCT_NUM, A.DUE_DATE
|
by: Don |
last post by:
I have MSSQL2k SP3a on WIN2k SP4.
moved a Date/log files to this server about a week ago from a SQL7
server
and attached it to this new Sql2k server.
everything works fine for about 24hrs and then it starts timing out !!
all I have to so is restart the MSSQL service and works fine again
till the next day !
|
by: dmalhotr2001 |
last post by:
Hi,
I have an issue with my query.
1. I have 1 stored proc which have execution calls to multiple stored
procs within it.
2. I want to wrap that main stored proc in the transaction and rollback
if there are errors execution calls to other stored procs. I don't
believe my code is accounting for errors occuring in the execution
|
by: Jen S |
last post by:
I feel like I'm missing something obvious here, but I'm stumped...
I have a stored procedure with code that looks like:
INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('An error occurred in the stored proc.', 16, 1);
|
by: Mike Thomas |
last post by:
This one must be very simple. I am trying to run an SQL Serv 7.0 stored
proc
from a VBA module in an Access 2000 app. This stored proc runs fine when I
remove the parameters from the stored proc and from the CommandText string
below, but when I run it as is I get the error
SQL Serv Syntax Error or Access Violation
on the "Execute" line. _ContactFetchLike 'Thom%' also works when I run it
from the Query Analyser.
| |
by: aaron kempf |
last post by:
I find that ADP does not support any Stored Procedures that use the 'CREATE
PROC spHAPPY' syntax.
CREATE PROC syntax is listed in books online.
This syntax should be supported
Here is a scenario:
1) create proc using query analyzer and CREATE PROC spHAPPY syntax
2) open this proc in ADP
|
by: Michael |
last post by:
Hi Everyone,
I'm having a slight problem, I hope I didn't overlook something. I'm getting
the following error when I try to execute the function below:
Incorrect syntax near 'intake_GetListSet'."
Private sub LoadListSet()
dim da as New SqlDataAdapter
Dim cmd as New SqlCommand
try
cmd.Connection = OpenConnection()
|
by: Sandesh |
last post by:
Hello All,
Me saying " has any body come across such error would be
underestimating".
Well I am getting a very peculiar and unique error "Line 1: Incorrect
syntax near 'Actions'."
Explaining you the scene is the following Stored Proc.
|
by: Penstar |
last post by:
I have a basic Stored proc and keep getting a Syntax error. The SQL works fine, but not in the stored proc.
CREATE DEFINER=`time`@`%` PROCEDURE `spAppendTimesheet`();
BEGIN
UPDATE tempam1t SET tempam1t_eventid=4;
END
Can someone please point out where I am wrong?
Thanks
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |