New answers tagged

2

Two big problems with using functions in a query are that cardinalities are not accurate and that the function code is called once per row. When setting a variable neither of these are a concern. So I would think, generally speaking, there would be no measurable performance impact.


1

If it's possible to add an additional parameter to your stored procedure (PropertyValueIsFunction bit), you could test for that condition and use REPLACE to strip out the quotes. Here's an example: CREATE PROCEDURE [dbo].[sp_set_etl_status] @propertyName VARCHAR(100) ,@propertyValue VARCHAR(MAX) ,@propertyValueIsFunction bit AS DECLARE @query AS ...


3

Assuming the ID in the master table is generated (e.g. because it's defined as generated always as identity or serial) you can do those inserts in a single statement: with new_master as ( insert into master_table (column_one, column_two, column_tree values (1,2,3) returning id --<< makes the generated ID available ), new_t1 as ( insert into ...


0

> Does nombre, apellido and email are NULLable? - Akina > They are not nullable – user198683 CREATE OR REPLACE PROCEDURE actualizarEncargadosP ( OID_EN2 IN encargadosp.oid_en%TYPE, nombre2 IN encargadosp.nombre%TYPE, apellido2 IN encargadosp.apellido%TYPE, ...


4

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code: DECLARE @cmd nvarchar(max) ; SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL ' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS , db = ''' + d....


4

There are several ways to get this that require different input information INFORMATION_SCHEMA: You only need to know the name of the object you want the definition for. SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'MyProcName' Note, this schema is database specific, so you need to be in the context of ...


Top 50 recent answers are included