New answers tagged

1

Doh, I looked at numerous examples online, and these were pretty much the same as I used in my example, however I did think I was missing something, then I found another example which proved that I was missing: EXECUTE stmt; So the complete prepared statement looks like: PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt;


0

First of all never use variable names that are the same as column names, the databases can get confused. Second you can use doppel quotes and single quotes, to achieve strings CREATe PROCEDURE abc(IN _biPK varchar(10),_vcTag varchar(10)) BEGIN #Procedure: insertMetaTagIfNotPresent #Parameters: bkID ID to find or NULL to create automatically # ...


0

In workbench you find all to stored procedures, when you make a right cick on stored procedures. In your case use Alter Stored procedures It is basically all the same for view tables columns etc.


0

Seeing as the question has been reopened, I'll post my solution too. 'orders' Table CREATE TABLE orders ( id INT NOT NULL, code INT NOT NULL, service_id INT NOT NULL, status CHARACTER VARYING(50), creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, status_date TIMESTAMP, created_user INT ); Records INSERT INTO orders (...


2

This would work: CREATE OR REPLACE FUNCTION yearly_orders(_year int, _order_cat int) RETURNS int LANGUAGE sql STABLE AS $func$ SELECT count(*)::int FROM orders o WHERE o.status = 'Requested' AND o.creation >= to_timestamp(_year::text, 'YYYY') AND o.creation < to_timestamp((_year + 1)::text, 'YYYY') AND o.code = _order_cat; $func$; The ...


1

Yes, it is possible. First you need to add that login as a user in that database without any database role assigned except public role(no server role as well). Once login is added as a user in the database, you need to execute explicit grant permission as below: use db_name go grant execute on dbo.Proc_Name1 to User_name go grant execute on dbo.Proc_Name2 to ...


1

Bu default, a user don't have any premissions. So, all you have to do is to grant the permissions you want: GRANT EXEC ON proname TO usrname GRANT SELECT ON tablename TO username I don't understand what you mean by "tables to be fed". If a proc operates on a table (SELECT, INSERT ...) then it is enough to have EXEC permissions on the proc, you don'...


3

If calling the procedure begins an implicit transaction, then shouldn't a faulting step in the proc roll back all changes being effected by the proc No. Even if you have SET IMPLICIT_TRANSACTIONS ON (and most likely you don't, and shouldn't), executing / calling a stored procedure does not begin a transaction. If I remember correctly, that is how PostgreSQL ...


15

Transactions don't automatically roll back on error--that's not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make that happen. As you mention, you can make that happen through TRY...CATCH, which gives you the most control over if and how you can rollback. It sounds like you ...


1

Here are some steps to troubleshoot the situation. After executing them you're probably be able to identify what's causing your problem. Execute one of them at a time to eliminate the possible problems: Replace @attach_query_result_as_file = 1, for @attach_query_result_as_file = 'Danchat',. The idea is to cause an error to see if causes the job to fail or ...


0

this query will search in contracts_view and check if the new contract start date between any other active contracts: declare @d as date = '2025-04-22' IF EXISTS (SELECT * FROM contracts_view where contracts_view.cust_id=123456789 and @d >= contracts_view.date_start and @d <= contracts_view.date_end and contracts_view.shop_id = (SELECT STRING_AGG(...


0

Another latest way: SHOW CREATE PROCEDURE <procedure_name> ;


-1

please verify if your database mail service is properly configured and check it with the "send test email..." and look into the log for detalis. Usually the mail server has to be configured to accept relay from your sql.


5

Another idea using Extended Events. Create an extended events session to catch errors CREATE EVENT SESSION [Track_Errors] ON SERVER ADD EVENT sqlserver.error_reported( ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.is_system,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text)) ...


5

When my stored procedures run other stored procedures, how can I find out which stored procedure's code generated the error? In your CATCH block, you can use ERROR_PROCEDURE() to get the name of the procedure or trigger where the error occurred. If you simply use THROW in your CATCH block in the inner procedures, then your outer procedure can access these ...


5

Use @@procid to get the currently executing routine and object_name() to convert the id to a name Hth


1

Modify your SP for to look at the SQL text built: fiddle It is clear that you forget to wrap string literal value with single quotes. You must use SET @quer = concat( 'SELECT ROUND(avg(prices.price),2) FROM ( SELECT current_Price as price FROM UNION_SALES WHERE location = ''', loc,''' and current_Price >5000 ORDER BY ...


0

You can't use prepared statements for LIMIT but you canuse it for location DROP PROCEDURE IF EXISTS lower10_avg_price ; DELIMITER $$ CREATE procedure lower10_avg_price (loc varchar(45)) BEGIN SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000); SET @loc := loc; SET @quer = concat( 'SELECT ROUND(avg(prices....


Top 50 recent answers are included