Dynamic sql with output parameter in SQL Server

The following query outputs the count of customers belong to code ’65’ to a parameter using a dynamic query.

declare @query nvarchar(1000), @j int, @parmDefinition nvarchar(500);
set @query = N’select @i = count(*) from customer where code = ”65” group by code’
print @query
set @parmDefinition = N’@i int OUTPUT’;
execute sp_executesql @query,@parmDefinition,@i = @j output
select @j

User Defined Table (UDT) types and their usage in procedures

This is a simple example which shows how to create a data type and how it can be used to pass an array of data as an input parameter to a stored procedure.

create table Customer
(
CustomerId int identity(1,1) primary key,
CustomerCode nchar(3),
CustomerName nvarchar(50)
)
go

if type_id(‘udt_Customer’) is not null
drop type udt_Customer;

create type udt_Customer as table
(
CustomerCode nchar(3),
CustomerName nvarchar(50)
);
go

create procedure proc_CustomerInsert
(
@customer udt_Customer readonly
)
as
begin

insert Customer (CustomerCode,CustomerName)
select CustomerCode,CustomerName
from   @customer

select * from Customer

end
go

———
declare @customer udt_Customer

insert @customer(CustomerCode,CustomerName)
select ‘001’,’Ethan Hunt’
union
select ‘002’,’James Bond’

exec proc_CustomerInsert @customer
go

SQL Code Snippets : Search Triggers

List all the triggers belong to a table

select o.name,p.name
from sys.objects o
join sys.objects p on o.parent_object_id = p.object_id
where o.type = ‘TR’
and p.name = ‘table_name’

Find the table a trigger belongs to

select o.name,p.name
from sys.objects o
join sys.objects p on o.parent_object_id = p.object_id
where o.type = ‘TR’
and o.name = ‘trigger_name’

Search the contents of a trigger

select o.name,p.name
from sys.objects o
join sys.objects p on o.parent_object_id = p.object_id
where o.type = ‘TR’
and object_definition(o.object_id) like ‘%search string%’

Create a check constraint in sql server

if not exists
(
select    null
from    sys.objects
where    type_Desc = N’CHECK_CONSTRAINT’
and object_name(parent_object_id) = N’Product’
and object_name(object_id) = N’CK_Product_Constraint’
)
begin
alter table Product
add constraint CK_Product_Constraint check (not(ByInstance = 1 and stock_avail_id <> 1))
end
go

Create default constraint with the check for existence in sql server

if not exists
(
select    null
from    sys.objects
where    type_Desc = N’DEFAULT_CONSTRAINT’
and object_name(parent_object_id) = N’Product’
and object_name(object_id) = N’DF_Product_ByInstance’
)
begin
alter table Product
add constraint DF_Product_ByInstance default 0 for ByInstance
end
go