[ptmi1047]

Hi,

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 )
		BEGIN
			
			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

		END		
		
		ELSE
		BEGIN
            SET @Exists = 0
		END
 
		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.

[mishika]

Hello,

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
Mishika

[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).