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:
So the complete prepared statement looks like:
PREPARE stmt FROM @SQL;
DEALLOCATE PREPARE stmt;
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))
#Parameters: bkID ID to find or NULL to create automatically
Seeing as the question has been reopened, I'll post my solution too.
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,
INSERT INTO orders
This would work:
CREATE OR REPLACE FUNCTION yearly_orders(_year int, _order_cat int)
LANGUAGE sql STABLE AS
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;
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:
grant execute on dbo.Proc_Name1 to User_name
grant execute on dbo.Proc_Name2 to ...
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'...
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 ...
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 ...
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 ...
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(...
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.
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(
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 ...
Modify your SP for to look at the SQL text built:
It is clear that you forget to wrap string literal value with single quotes. You must use
SET @quer = concat(
FROM ( SELECT current_Price as price
WHERE location = ''', loc,'''
and current_Price >5000
ORDER BY ...
You can't use prepared statements for LIMIT but you canuse it for location
DROP PROCEDURE IF EXISTS lower10_avg_price ;
CREATE procedure lower10_avg_price (loc varchar(45))
SET @a = (SELECT ceil(COUNT(*)/10) FROM UNION_SALES WHERE location = loc and current_Price >5000);
SET @loc := loc;
SET @quer = concat(