Page 1 of 1


Posted: Tue May 16, 2017 11:37 pm
by ptmi1047

I created a trigger to execute stored procedure (update).

$query = "execute SP_UpdateBazaarSeq '$Store','$ReqDate'";
$result = executeQuery($query, $db);
Code: Select all
--Stored Procedure
IF EXISTS (SELECT store_code FROM [MDS]..ms_Bazaar_Seq
						WHERE store_code = @store_code and year = @year )
			SET @Exists = 1

			CREATE TABLE [MDS]..temp_ms_Bazaar_Seq1(
				[store_code] [char](3) NOT NULL,
				[year] [char](4) NOT NULL,
				[sequence] [int]NOT NULL

			INSERT INTO [MDS]..temp_ms_Bazaar_Seq1
			SELECT TOP 1 store_code, year, sequence from [MDS]..ms_Bazaar_Seq where store_code = @store_code and year = @year order by sequence DESC  

			UPDATE [MDS]..ms_Bazaar_Seq
			SET sequence = b.sequence + 1
			FROM [MDS]..ms_Bazaar_Seq a INNER JOIN [MDS]..temp_ms_Bazaar_Seq1 b
			ON a.store_code = b.store_code and a.year = b.year
			WHERE a.store_code = @store_code
			AND a.year = @year

			DROP TABLE [MDS]..temp_ms_Bazaar_Seq1

            SET @Exists = 0
		RETURN @Exists	
When I debug, I got error : mssql_free_result() expects parameter 1 to be resource, boolean given. How to solved this error.

Thank you.

Re: mssql_free_result()

Posted: Wed May 17, 2017 3:20 am
by mishika

Which database are you using? MS SQL or MY SQL

If you are using MY SQL, the code in the trigger to execute the trigger should be like the following:
Code: Select all
$Store = //appropriate value;
$ReqDate = //appropriate value;
@@sql = "CALL SP_UpdateBazaarSeq($Store ,$ReqDate)";
@@result = mysql_query(@@sql);
This code works for me to successfully execute a procedure in MY SQL.
Also, you can check the values of @@sql and @@result in the debug section.

If you are using MS SQL, you can try the following code:
Code: Select all
$Store = //appropriate value;
$ReqDate = //appropriate value;
@@sql = "EXEC SP_UpdateBazaarSeq $Store ,$ReqDate";
@@result = mssql_query(@@sql);
I don't have the MS SQL database, so I have not tested this code. Please try this and let me know if this solves your problem.
Also, I think the line of code IF EXISTS in your Procedure must be written inside BEGIN section.

Hope this helps

Best Regards

Re: mssql_free_result()

Posted: Thu May 18, 2017 1:43 am
by ptmi1047
Hi Mishika,

I'm using MSSQL and I already tried your code. I got another error "mssql_query() expects parameter 2 to be resource, string given".
The value of @@result is Array(). I think the issue is in Array whilst it should be a parameter (var/int).