Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderators: ArturoRobles, ArturoRobles

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
By ptmi1047
#790840
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.
By mishika
#790842
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
By ptmi1047
#790873
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).

HI Programerboy, Thank you for your advice. I am[…]

Adding CurrentDate to Case Label

Hi, Please check the following link: http://pmlea[…]

How to categories inbox cases

I dont quite know the answer to you question, but […]

Hello My friends, I have a problem and I can not s[…]