Sql Server Stored Procedures Templates For Select , insert , update , Search and how to generate all db Basic Procedures using CS2GEN Tool

if you need generation tools for sql server db you can use the following component CS2GEN

http://cs2gen.codeplex.com/releases/view/5488

download the latest version  .exe file 

you can easily connect to your db and generate c# ORM and sql server script file for stored procedures

after finish installation you will get the following screen 

choose db from left menu open database 

enter you db connection information

enter you output directory to save your file

open the folder to get your files 

you will find ready ORM Desktop sample or Web Application in c# you can use it as database Layer 

and also you will find the .sql file related your database .

Open the script file .sql in management studio and choose stored Procedures you need to apply to your database to safe your time.

if you need to use c# ORM as db layer you should execute whole the file to keep stored procedures by name (don't change the name of stored procedures) if you will use the generated code .

----------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

Manually Create Stored 

first we will create table called Users table  (run the following script to new database to create it )

USE [YourDBName]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Users](

[UserID] [int] IDENTITY(1,1) NOT NULL,

[UserFullName] [nvarchar](150) NOT NULL,

[LoginName] [varchar](50) NULL,

[LoginPassword] [varchar](50) NULL,

[Email] [varchar](100) NULL,

[Mobile] [varchar](15) NULL,

[Extension] [varchar](6) NULL,

[phone] [varchar](15) NULL,

[FaxNo] [varchar](15) NULL,

[Disabled] [bit] NULL,

[PrimaryGroup] [int] NULL,

[PrimaryProfile] [int] NULL,

[CreateDate] [datetime] NULL,

[CreatedBy] [int] NULL,

[LastLoginTime] [datetime] NULL,

[LastUpdateTime] [datetime] NULL,

[DegreeID] [int] NULL,

[themesColor] [nvarchar](50) NULL,

 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 

(

[UserID] 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 [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Degrees] FOREIGN KEY([DegreeID])

REFERENCES [dbo].[Degrees] ([DegreeID])

GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Degrees]

GO

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Groups] FOREIGN KEY([PrimaryGroup])

REFERENCES [dbo].[Groups] ([GroupID])

GO

ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Groups]

GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Disabled]  DEFAULT ((0)) FOR [Disabled]

GO

----------------------------------------------------------

 now we will create stored procedures for the table
1-insert Procedure
CREATEPROCEDURE [dbo].[InsertUser]
   @UserFullName nvarchar(150)
   ,@LoginName varchar(50)
   ,@LoginPassword varchar(50)
   ,@Email varchar(100)
   ,@Mobile varchar(15)
   ,@Extension varchar(6)
   ,@Phone varchar(15)
   ,@FaxNo varchar(15)
   ,@Disabled bit
   ,@PrimaryGroup int
   ,@PrimaryProfile int
   ,@CreatedBy int
   ,@DegreeID int
as
begin
declare @flag char(1)
set @flag= '0'
IF NOT EXISTS (select * from Users where  UserFullName = @UserFullName)
BEGIN
 
INSERT INTO [Users]
           ([UserFullName]
      ,[LoginName]
      ,[LoginPassword]
      ,[Email]
      ,[Mobile]
      ,[Extension]
      ,[phone]
      ,[FaxNo]
      ,[Disabled]
      ,[PrimaryGroup]
      ,[PrimaryProfile]
      ,[CreateDate]
      ,[CreatedBy]
      ,[DegreeID])
         
     VALUES
           (@UserFullName
           ,@LoginName
           ,@LoginPassword
           ,@Email
           ,@Mobile
           ,@Extension
           ,@phone
           ,@FaxNo
           ,@Disabled
           ,@PrimaryGroup
           ,@PrimaryProfile
           ,GETDATE()
           ,@CreatedBy
           ,@DegreeID)
set @flag = '1'
end
select @flag
end

------------------------------------------------------------------------------------
update stored procedure

CREATE  procedure [dbo].[UpdateUser]
@UserID int
   ,@UserFullName nvarchar(150)
   ,@LoginName varchar(50)
   ,@LoginPassword varchar(50)
   ,@Email varchar(100)
   ,@Mobile varchar(15)
   ,@Extension varchar(6)
   ,@Phone varchar(15)
   ,@FaxNo varchar(15)
   ,@Disabled bit
   ,@PrimaryGroup int
   ,@PrimaryProfile int
   ,@DegreeID int

as
begin

UPDATE [Users]

      set [UserFullName] = @UserFullName
      ,[LoginName] = @LoginName
      ,[LoginPassword] = @LoginPassword
      ,[Email] = @Email
      ,[Mobile] = @Mobile
      ,[Extension] = @Extension
      ,[phone] = @Phone
      ,[FaxNo] = @FaxNo
      ,[Disabled] = @Disabled
      ,[PrimaryGroup] = @PrimaryGroup
      ,[PrimaryProfile] = @PrimaryProfile
      ,[LastUpdateTime] = GETDATE()
      ,[DegreeID] = @DegreeID
 WHERE  UserID= @UserID

end

------------------------------------------------------------------------------------------------
delete Users
Create PROCEDURE [dbo].[DeleteUserInfo]
@ID int
as
begin





Declare @flag as int
set @flag=1
begin try
Delete FROM Group_Users where Group_Users.UserID = @ID
Delete FROM [Users] where UserID=@ID

end try
begin catch
set @flag = 0
end catch
select @flag


end
-------------------------------------------------------------------------------------------
search Users By Full Name
Create PROCEDURE [dbo].[UserSEARCH]
@name nvarchar(50)
as
begin
select  [UserID]
      ,[UserFullName]
      ,[LoginName]
      ,[LoginPassword]
      ,[Email]
      ,[Mobile]
      ,[Extension]
      ,[phone]
      ,[FaxNo]
      ,[Disabled]
      ,[PrimaryGroup]
      ,[PrimaryProfile]
      ,[CreateDate]
      ,[CreatedBy]
      ,[LastLoginTime]
      ,[LastUpdateTime]
      ,Degrees.[DegreeID]
       ,Degrees.DegreeName as DegreeName
  FROM [Users] inner join [Degrees]
on Degrees.DegreeID = Users.DegreeID

       where LoginName like '%'+@name+'%' or UserFullName like '%'+@name+'%'
end

if you need to search table all columns and need to depends on passed query regardless of if the parameter passed is null or not passed at all
here's example to handle good search form stored procedure .

create the following table
USE [HOSPITAL_DB]
GO

/****** Object:  Table [dbo].[Doc_Servers]    Script Date: 01/23/2014 09:25:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Doc_Servers](
[DocServerID] [int] IDENTITY(1,1) NOT NULL,
[Location] [varchar](250) NOT NULL,
[Disabled] [bit] NULL,
[LibraryName] [nvarchar](100) NOT NULL,
[LibraryDesc] [nvarchar](300) NULL,
[IsPrivateLibrary] [bit] NULL,
 CONSTRAINT [PK_Doc_Servers] PRIMARY KEY CLUSTERED
(
[DocServerID] 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 [dbo].[Doc_Servers] ADD  CONSTRAINT [DF_Doc_Servers_Disabled]  DEFAULT ((1)) FOR [Disabled]
GO

ALTER TABLE [dbo].[Doc_Servers] ADD  CONSTRAINT [DF_Doc_Servers_IsPrivateLibrary]  DEFAULT ((0)) FOR [IsPrivateLibrary]
GO

-----------------------------------------------------------------------------------------

search Procedure
ALTER PROCEDURE [dbo].[Doc_Servers_Search]
@DocServerID int = NULL,
    @LibraryName nvarchar(100) = NULL
 
 
   AS
BEGIN
   SELECT [DocServerID],[Location],[Disabled],[LibraryName],[LibraryDesc]
FROM [Doc_Servers] T
    WHERE (@DocServerID IS NULL     OR T.DocServerID = @DocServerID)
    and
          (@LibraryName IS NULL     OR T.LibraryName like '%'+@LibraryName+'%')
    and   (IsPrivateLibrary IS NULL OR IsPrivateLibrary <> 1)
                   
       
END

the previous syntax is very good for search if you have multiple columns and need to generate one procedure to search any of these columns so if the parameter passed to procedure it will check it's value condition else it will ignore filter condition and set default parameter value to null so this is good way to handle stored procedure for search structure for any table for you .

to execute and test procedure use the following script

exec Doc_Servers_Search  null, null

exec Doc_Servers_Search  null, ''

exec Doc_Servers_Search  null, 'Lib'

very important note if you need your varchar fields to be english Case sensitive or Insensitive

To create your search Case Sensetive use the following sample 

(@LibraryName IS NULL     OR T.LibraryName like '%'+@LibraryName+'%' Collate Latin1_General_CS_AI) 

to create your search Case insensitive 

(@LibraryName IS NULL     OR T.LibraryName like '%'+@LibraryName+'%' Collate Latin1_General_CI_AI) 

Example to test the two collation

begin 

if ('a'='A' Collate Latin1_General_CI_AI) 

    print'same 1'

else

    print 'different 1'

if ('a'='A' Collate Latin1_General_CS_AI) 

    print'same 2'

else

    print 'different 2'

end

-----------------------------------------------------------------------------------------------

i hope this post help you to create your own insert , update , delete , search Templates .