Stupid SQL Tricks

From HoboWiki

Contents 1 How to create a check constraint to limit a field to a set of values 2 How to return a list of all the parameters of a stored procedure

Most of these are done using MS SQL Server

[edit] How to create a check constraint to limit a field to a set of values

NOT DONE

SQL Check constraints are objects directly associated with a table, unlike SQL Rules in MS SQL Server (depreciated). To create a constraint you just need to either create or alter an existing table. In this example I am going to alter and existing table to add constraints.

CREATE TABLE [DOC].[TimeDelta](
	[TimeDeltaID] [int] IDENTITY(1,1) NOT NULL,
	[TimeStart] [datetime] NOT NULL,
	[TimeEnd] [datetime] NOT NULL,
	[IsListReceived] [char](1) NOT NULL CONSTRAINT [DF_TimeDelta_IsListReceived]  DEFAULT ('N'),
CONSTRAINT [PK_TimeDelta] PRIMARY KEY CLUSTERED
(
	[TimeDeltaID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [DOC].[TimeDelta]  WITH CHECK ADD  CONSTRAINT [CK_TimeDelta_IsListReceived] CHECK 
(([IsListReceived]='N' OR [IsListReceived]='Y'))
GO
ALTER TABLE [DOC].[TimeDelta] CHECK CONSTRAINT [CK_TimeDelta_IsListReceived]

[edit] How to return a list of all the parameters of a stored procedure

Since I typically use this in a program, I pass the name of the stored procedure into this string with formatting methods. To use this outside of a program, simply replace the {0} with the full name of the stored procedure (including schema).

This SQL will return a list of all the parameters of a stored procedure, including their type and length.

SUBSTRING(SP.name,2,LEN(SP.name)) as 'ParameterName'
, ST.name as 'Type',SP.max_length as 'FieldLength'
FROM 
  sys.parameters as SP
JOIN 
  sys.types as ST
ON SP.system_type_id = ST.system_type_id
JOIN
  sys.procedures AS SPC
ON object_name(SP.object_id) = SPC.name
JOIN
  sys.schemas AS SCH
ON SPC.schema_id = SCH.schema_id
WHERE
  SCH.name + '.' +SPC.name = '{0}'