sproc fails when run through PHP

[Sulman]

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

[Sulman]

Thinking about it, I wonder if it has anything to do with single quotes being returned in some of the fields that is messing up PHP?

 

If so how would I change the sproc...?

 

Thanks

[corbin]

In MSSQL, + is strictly a math function I think....

 

Try googling around for how to concatenate strings in mssql.