Jump to content

sproc fails when run through PHP


Sulman

Recommended Posts

Hi all,

 

I have a stored procedure that runs perfectly well through SQL Server Management Studio but when I use it through PHP it fails with the follwing error:

 

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in c:\Inetpub\wwwroot\xxxxx\xxxx\functions.php on line 617

 

I am running the sproc like so:

function getClubsWithAvaialableFixtures()
{
$query="EXEC [dbo].[spAllFixtures]";	
$result=mssql_query($query);  //LINE 617
return $result;	
}

 

Now this is telling me that a comma somewhere is messing things up. The sproc uses a comma as a delimiter so i thought perhaps there is a comma in one of the fields being returned that messes it up. But after removing the commas I see the same error.

 

I then modified the sproc and changed the comma to a pipe (|). When running it throuhg PHP it then gives me this error :

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near '|'. (severity 15) in c:\Inetpub\wwwroot\xxxxx\xxxx\functions.php on line 617

 

So it is clearly this line that is messing things up.

 

I did not write the sproc and I'm not really an SQL guru but if any one can point me in the right direction I would really appreciate it!

 

Thanks.

 

SPROC:

CREATE Procedure spAllFixtures

As
set nocount on

DECLARE @WhatClub int
DECLARE @NumFound int
DECLARE @SQLcommand varchar(1024)
DECLARE @NoFixtureList varchar(1024)

DECLARE cur_find_fixtures CURSOR LOCAL FAST_FORWARD FOR SELECT ClubID FROM tblClubs

OPEN cur_find_fixtures

FETCH FROM cur_find_fixtures INTO @WhatClub

WHILE (@@FETCH_STATUS <> -1) 
BEGIN 

	EXEC @NumFound =dbo.spNumAvailableFixtures @WhatClub

	IF @NumFound = 0 
		SELECT @NoFixtureList  = COALESCE(@NoFixtureList + ',', '') + CONVERT(varchar(12), @WhatClub) --THIS IS THE LINE MESSING THINGS UP!!!

	FETCH FROM cur_find_fixtures INTO @WhatClub
END 

CLOSE cur_find_fixtures
DEALLOCATE cur_find_fixtures

SET @SQLcommand = 'SELECT ClubID, OrganisationID FROM tblClubs '
IF @NoFixtureList <> '' SET @SQLcommand = @SQLcommand + 'WHERE ClubID NOT IN  (' + @NoFixtureList + ');'
EXECUTE(@SQLcommand)

return 
GO

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.