SQL add filter only if a variable is not null

You can translate your requirement into : SELECT route_id [ROUTE_ID] FROM route_master(NOLOCK) WHERE route_ou = 2 AND (@l_s_query is null OR route_query = @l_s_query) AND lang_id = 1 OPTION (RECOMPILE) The OPTION (RECOMPILE) is optional but can give better execution plans at the expense of extra compilation time as discussed in the canonical article on … Read more

Getting Hibernate and SQL Server to play nice with VARCHAR and NVARCHAR

public class SQLServerUnicodeDialect extends org.hibernate.dialect.SQLServerDialect { public SQLServerUnicodeDialect() { super(); registerColumnType(Types.CHAR, “nchar(1)”); registerColumnType(Types.LONGVARCHAR, “nvarchar(max)” ); registerColumnType(Types.VARCHAR, 4000, “nvarchar($l)”); registerColumnType(Types.VARCHAR, “nvarchar(max)”); registerColumnType(Types.CLOB, “nvarchar(max)” ); registerColumnType(Types.NCHAR, “nchar(1)”); registerColumnType(Types.LONGNVARCHAR, “nvarchar(max)”); registerColumnType(Types.NVARCHAR, 4000, “nvarchar($l)”); registerColumnType(Types.NVARCHAR, “nvarchar(max)”); registerColumnType(Types.NCLOB, “nvarchar(max)”); registerHibernateType(Types.NCHAR, StandardBasicTypes.CHARACTER.getName()); registerHibernateType(Types.LONGNVARCHAR, StandardBasicTypes.TEXT.getName()); registerHibernateType(Types.NVARCHAR, StandardBasicTypes.STRING.getName()); registerHibernateType(Types.NCLOB, StandardBasicTypes.CLOB.getName() ); } }

How to automatically generate unique id in SQL like UID12345678?

The only viable solution in my opinion is to use an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value a computed, persisted column to convert that numeric value to the value you need So try this: CREATE TABLE dbo.tblUsers (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY … Read more

pyodbc.connect() works, but not sqlalchemy.create_engine().connect()

A Pass through exact Pyodbc string works for me: import pandas as pd from sqlalchemy import create_engine from sqlalchemy.engine import URL connection_string = ( r”Driver=ODBC Driver 17 for SQL Server;” r”Server=(local)\SQLEXPRESS;” r”Database=myDb;” r”Trusted_Connection=yes;” ) connection_url = URL.create( “mssql+pyodbc”, query={“odbc_connect”: connection_string} ) engine = create_engine(connection_url) df = pd.DataFrame([(1, “foo”)], columns=[“id”, “txt”]) pd.to_sql(“test_table”, engine, if_exists=”replace”, index=False)

Implementing one-to-zero-or-one relation in SQL Server

The 1-0..1 relation in your database is directly visible. It is built between Course and OnlineCourse tables where Course is principal in relation (1) and OnlineCourse is dependent with FK configured on CourseID. FK is also PK of the OnlineCourse = it must be unique and because of that it is 0..1. Database “always” uses … Read more

Sql query for tree table

Expanding on a_horse_with_no_name’s answer, this show how to use SQL Server’s implementation of recursive CTE (recursive single-record cross apply) in combination with row_number() to produce the exact output in the question. declare @t table(id int,parentId int,name varchar(20)) insert @t select 1, 0 ,’Category1′ insert @t select 2, 0, ‘Category2’ insert @t select 3, 1, ‘Category3’ … Read more

Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?

Maybe I don’t understand the question, but wouldn’t this do it? (you must know what you want back) INSERT INTO some_table (…) VALUES (…) RETURNING some_column_a, some_column_b, some_column_c, … INTO :out_a, :out_b, :out_c, … @Vincent returning bulk collect into for multi-row insert works only in conjunction with forall (in another words if you insert from … Read more

Cannot delete rows from a temporal history table

If you make the DELETE dynamic, your stored procedure will successfully ALTER the table, DELETE the records in question, and then ALTER it back. CREATE PROCEDURE [dbo].[OrderHistoryDelete] (@Id UNIQUEIDENTIFIER) AS BEGIN DECLARE @sql VARCHAR(MAX) BEGIN TRANSACTION ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = OFF ) SET @sql=”DELETE FROM [dbo].[OrderHistory] WITH (TABLOCKX) WHERE [Id] = “” … Read more