Just another tech site

T-SQL


Just to remender the syntax

Stored Procedure Creation Template script

Example creation of the SP Survey_Check_Triple.
The idea of the SP is Drop the exsiting SP and recreate the new one with the same name.

USE [DatabaseName]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Survey_Check_Triple]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Survey_Check_Triple]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Survey_Check_Triple]
	@FirstName NVARCHAR(MAX),
	@LastName NVARCHAR(MAX),
	@EmailD NVARCHAR(MAX)
AS
BEGIN

	DECLARE @NBR INT;

	SELECT @NBR = COUNT(*) FROM [Survey] WHERE FirstName = @FirstName AND LastName = @LastName AND EmailID = @EmailD;

	RETURN @NBR;
END

Function Creation Template

Example creation of the T-SQL function

USE [DatabaseName] 
GO		  IF EXISTS ( 	SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 	WHERE ROUTINE_NAME = 'F_Name' 	AND ROUTINE_SCHEMA = 'dbo' 	AND ROUTINE_TYPE = 'FUNCTION' ) DROP FUNCTION [dbo].[F_Name]; GO  CREATE FUNCTION [dbo].[F_Name]( 	@From DATETIME, 	@To DATETIME, 	@CurrencyName NVARCHAR(MAX), 	@UserId INT ) RETURNS INT AS BEGIN  	DECLARE @SumAdd INT 	SET @SumAdd = 0; 	RETURN @SumAdd;  END

Call T-SQL Scalar Function in Stored Procedure

Result of the functions can be used as the scalares returned

DECLARE @Balance INT
SET @Balance = 0;

SET @Balance = [dbo].[Function_GetSumAdd] (@UserId, @Date) -
	               [dbo].[Function_GetSumExchanged] (@UserId, @Date) -
	               [dbo].[Function_GetSumLost] (@UserId, @Date) ;

SELECT @Balance;

Call Stored Procedure from another Stored Proceedure

Call a SP

EXEC DSC_Order_Add_CreditCard @orderId, @creditCardValues

Call a SP with an parameter in OUTPUT

EXEC DSC_Order_Add_AddressByValues @shippingAddressValues, @shippingAddressId OUTPUT

Build SQL Query in a Stored Procedure

Code here

Update a Column table Type

This is actually simple but you will lose the data, because you are droping the column.
You also have to check if there are no FK on it.

USE [DatabaseName]
ALTER TABLE table_name
      DROP COLUMN column_name;
      
ALTER TABLE table_name ADD column_name decimal;      
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: