I may not fully understand your requirement, but either:

(1) A CHECK constraint

or

(2) an index definition

can be created to do what you want to do.

For example, if you just want to make...

I'd flip the check around: check first for = 1 as 'True' else 'False'.  That way NULL will show as false, if a NULL happens to slip in there.

Use the...

You can use ctes, which will create virtual tables, but no actual physical tables:

;WITH TempA AS (
    SELECT
    InvoiceNo, Date,Type,Code
 ...

I would use STUFF for this, as most custom-fitted to do this task:

SELECT s_Result,
    STUFF(s_Result, 1, CASE s_credit_card_type 
     ...

;WITH cte_1 AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY HH ORDER BY HH) AS row_num
    FROM MyTable
)
SELECT 
   ...

There might be some fair overhead to getting the current length of a text column.  If you really have to have that, add a column to...

There might be some fair overhead to getting the current length of a text column.  If you really have to have that, add a column to the...

Actually "<" is NOT a safe way to check for not space.  CR and LF are both not "<" a space.

select case when char(10) > space(1) then 'LF ' else...

References in SQL are to object names.  The code will resolve based on the name.

A rename can't happen while the table is being queried, because a schema lock is in...

I agree.  Why the "need" to jump to such a huge number?

Correct.  You'd have to use decimal(38, 0) or some other larger value type rather than bigint.

So you deliberately set the value so that it would get too large for a bigint ... and that seems to be your error.

Not sure why you felt the need...

Theoretically @@IDENTITY value could be too large for a bigint.  Hard to imagine actually using enough values to go past 19 digits, but it's theoretically possible.

Btw, you should get @@IDENTITY...

Be sure you set and are using the correct variable.  In your original code, you use "@tblName" but you never set the value of that variable.  That's why I used...

You can't use a variable in a DROP TABLE statement in SQL.

Change it to:

SET @dropCommand = N'DROP TABLE dbo.' + @tableName