Web design and hosting, database, cloud and social media solutions that deliver business results
  • Business Solutions
    • Robotic Process Automation
    • Software
    • Database Consultancy Services
      • Data Integration
      • Datawarehouse Services
      • Power BI
      • Server Upgrade and DBA Services
    • Web Site Design Services
      • Logo Design
      • Payment Gateways
      • Web Localisation and Translation
      • Web Site Optimisation
      • Web Site Security
      • Technical Tools
    • Cloud Services
      • Amazon Web Services
      • Google Cloud Services
      • Microsoft Azure
    • Microsoft Office
    • Social Media Management and Advice Services
  • Academy
    • Our Test Environment
    • Learning Databases
      • The Basics
      • Get Open Query
      • SQL Server Data
      • SQL Server Maintenance
      • Using SQL Server Dates
      • Using SQL Server Functions
      • Using SQL Server Pivot-Unpivot
      • Technical Tools
    • Learning Web Design
      • Building Ousia Content Management System
      • Using ASP-NET
      • Using CSS
      • Using JavaScript
    • Learning Cloud and IT Services
      • Task Scheduler Error 2147943645
      • Requesting SSL and Generation of PFX file in OpenSSL Simple Steps
    • Using Social Media
      • Asking for a Google Review
      • Changing a Facebook account from personal to business
      • Choosing where to focus Social Media effort
      • Social Media Image Sizes
      • Using Meta Data to set Social Media Images
  • About Us
    • Blog
      • Building an entry level gaming machine
      • Google Core Update Jan 2020
      • Hot Chilli Internet Closure
      • How To Choose Content For Your Website Adverts Leaflets
      • Preventing Online Scam
      • Skimmers of the gig economy
      • The most annoying things about websites on the Internet
      • Top 5 websites for free Vector Graphics
    • Careers
      • Translator English-Portuguese
      • Translator English-Spanish
    • Portfolio
    • Team
      • Adrian Anandan
      • Ali Al Amine
      • Ayse Hur
      • Chester Copperpot
      • Deepika Bandaru
      • Gavin Clayton
      • Sai Gangu
      • Suneel Kumar
      • Surya Mukkamala
English (EN-GB)English (EN-US)Español (ES)italiano (IT)日本語 (JA)

Stored Procedure Field Generator

Field generator creates a number of code blocks for SQL Server Stored Procedures, insert, update, delete and variables.

Save time by copying relevant parts

Coding can take time, but we realised we use a number of types of code blocks regularly, be it to update a table through a stored procedure, or return data back.

This function below can generate he majority of the fields required to update a table for us, all we need to then do is copy and paste the relevant blocks into a new query window and we are done. The declaration part saves a huge amount of time for us in particular.

This won't be perfect for every situation, and can be adapted to suit your coding style.

SQL

CREATE PROC GetProcCommands(@Database NVARCHAR(200),@Schema NVARCHAR(200),@Table NVARCHAR(200)) AS BEGINDECLARE @Cols TABLE(ColumnID INT NOT NULL,ColumnName nvarchar(128) NULL,ColumnPrimaryKey BIT NULL,ColumnIdentity BIT NULL,ColumnDataType nvarchar (193) NULL)
DECLARE @SQL NVARCHAR(MAX)='SELECT c.column_id,c.name,is_primary_key,c.is_identity,t.name +(Case t.nameWHEN ''sql_variant'' Then '''' WHEN ''text'' Then '''' WHEN ''ntext'' Then '''' WHEN ''uniqueidentifier'' Then '''' WHEN ''xml'' Then '''' WHEN ''real'' Then ''''WHEN ''int'' Then '''' WHEN ''bigint'' Then '''' WHEN ''smallint'' Then '''' WHEN ''tinyint'' Then '''' WHEN ''money'' THEN '''' WHEN ''float'' THEN ''''WHEN ''datetime'' THEN '''' WHEN ''date'' THEN '''' WHEN ''datetime2'' THEN '''' WHEN ''sysname'' THEN '''' WHEN ''bit'' THEN '''' WHEN ''image'' THEN ''''WHEN ''datetimeoffset'' Then ''('' + Cast( c.scale As varchar ) + '') ''WHEN ''decimal'' Then ''('' + Cast( c.precision As varchar ) + '', '' + Cast( c.scale As varchar ) + '') ''Else Coalesce( ''('' + Case WHEN c.max_length = -1 Then ''MAX'' Else Cast( c.max_length As VarChar) End + '')'' , '''')End) ColumnDataTypeFROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.all_columns cLEFT JOIN (SELECT c.object_id,c.index_id,c.column_id,is_primary_keyFROM '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.indexes iINNER JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.index_columns c ON i.object_id=c.object_id AND i.index_id=c.index_idWHERE i.object_id=OBJECT_ID('''+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+''') AND is_primary_key=1) i ON i.object_id=c.object_id AND i.column_id=c.column_idLEFT JOIN '+ISNULL(NULLIF(@Database+'.','.'),'')+'sys.types t ON t.user_type_id=c.user_type_id AND t.system_type_id=c.system_type_idWHERE c.object_id=OBJECT_ID('''+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+''')'
INSERT INTO @ColsEXEC sp_executesql @SQL
DECLARE @ColumnList_Select NVARCHAR(MAX),@ColumnList_Insert NVARCHAR(MAX),@ColumnList_Update NVARCHAR(MAX),@ColumnList_Update_Where NVARCHAR(MAX),@ColumnList_Stored NVARCHAR(MAX),@ColumnList_Stored_PK NVARCHAR(MAX),@ColumnList_Stored_Insert NVARCHAR(MAX),@ColumnList_Stored_CheckNull NVARCHAR(MAX),@ColumnList_Stored_CheckNotNull NVARCHAR(MAX)SELECT @ColumnList_Select = COALESCE(@ColumnList_Select+',','')+ColumnName,@ColumnList_Stored=COALESCE(@ColumnList_Stored+',','')+'@'+ColumnName+' '+ColumnDataTypeFROM @ColsSELECT @ColumnList_Insert=COALESCE(@ColumnList_Insert+',','')+ColumnName,@ColumnList_Stored_Insert=COALESCE(@ColumnList_Stored_Insert+',','')+'@'+ColumnName FROM @ColsWHERE ColumnIdentity=0 SELECT @ColumnList_Update=COALESCE(@ColumnList_Update+',','')+ColumnName+'=@'+ColumnNameFROM @ColsWHERE ColumnIdentity=0 AND ISNULL(ColumnPrimaryKey,0)=0SELECT @ColumnList_Update_Where=COALESCE(@ColumnList_Update_Where+' AND ','')+ColumnName+'=@'+ColumnName,@ColumnList_Stored_PK=COALESCE(@ColumnList_Stored_PK+',','')+'@'+ColumnName+' '+ColumnDataTypeFROM @Cols WHERE ISNULL(ColumnPrimaryKey,0)=1SELECT @ColumnList_Stored_CheckNotNull=COALESCE(@ColumnList_Stored_CheckNotNull+' AND ','')+'@'+ColumnName+' IS NOT NULL',@ColumnList_Stored_CheckNull=COALESCE(@ColumnList_Stored_CheckNull+' AND ','')+'@'+ColumnName+' IS NULL'FROM @ColsWHERE ISNULL(ColumnPrimaryKey,0)=1SELECT 'Create Proc Upd' FieldType,@ColumnList_Stored Fields,'CREATE PROC [Upd_'+@Table+']('+@ColumnList_Stored+') AS BEGIN' CommandUNION SELECT 'Create Proc Del' FieldType,@ColumnList_Stored_PK Fields,'CREATE PROC [Del_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' CommandUNIONSELECT 'Create Proc Get' FieldType,@ColumnList_Stored_PK Fields,'CREATE PROC [Get_'+@Table+']('+@ColumnList_Stored_PK+') AS BEGIN' CommandUNION SELECT 'Declare Variables' FieldType,@ColumnList_Stored Fields,'DECLARE '+@ColumnList_Stored+'' CommandUNIONSELECT 'Select' FieldType,@ColumnList_Select Fields,'SELECT '+@ColumnList_Select CommandUNIONSELECT 'Insert Fields' FieldType,@ColumnList_Insert Fields,'INSERT INTO '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'')+'('+@ColumnList_Insert+')' CommandUNIONSELECT 'Insert Variables' FieldType,@ColumnList_Stored_Insert Fields,'SELECT '+@ColumnList_Stored_Insert CommandUNIONSELECT 'Update' FieldType,@ColumnList_Update Fields,'SET '+@ColumnList_Update CommandUNIONSELECT 'Where Key=Variable' FieldType,@ColumnList_Update_Where Fields,'WHERE '+@ColumnList_Update_Where CommandUNIONSELECT 'From' FieldType,'' Fields,'FROM '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') CommandUNION SELECT 'Update Table' FieldType,'' Fields,'UPDATE '+ISNULL(NULLIF(@Database+'.','.'),'')+ISNULL(NULLIF(@Schema+'.','.'),'')+''+ISNULL(NULLIF(@Table+'',''),'') CommandUNION SELECT 'Check Not Null' FieldType,'' Fields,'IF '+@ColumnList_Stored_CheckNotNull+' BEGIN' CommandUNIONSELECT 'Check Null' FieldType,'' Fields,'IF '+@ColumnList_Stored_CheckNull+' BEGIN' CommandENDGO

Test it

CREATE TABLE ProcCommandsTest(TestID INT IDENTITY(1,1) CONSTRAINT PK_TestID PRIMARY KEY,TestCol1 NVARCHAR(MAX),TestCol2 DECIMAL(18,2))GOEXEC GetProcCommands '','' ,'ProcCommandsTest'

Output

FieldTypeFieldsCommand
Check Not Null IF @TestID IS NOT NULL BEGIN
Check Null IF @TestID IS NULL BEGIN
Create Proc Del@TestID intCREATE PROC [Del_ProcCommandsTest](@TestID int) AS BEGIN
Create Proc Get@TestID intCREATE PROC [Get_ProcCommandsTest](@TestID int) AS BEGIN
Create Proc Upd@TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) CREATE PROC [Upd_ProcCommandsTest](@TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) ) AS BEGIN
Declare Variables@TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) DECLARE @TestID int,@TestCol1 nvarchar(MAX),@TestCol2 decimal(18, 2) 
From FROM ProcCommandsTest
Insert FieldsTestCol1,TestCol2INSERT INTO ProcCommandsTest(TestCol1,TestCol2)
Insert Variables@TestCol1,@TestCol2SELECT @TestCol1,@TestCol2
SelectTestID,TestCol1,TestCol2SELECT TestID,TestCol1,TestCol2
UpdateTestCol1=@TestCol1,TestCol2=@TestCol2SET TestCol1=@TestCol1,TestCol2=@TestCol2
Update Table UPDATE ProcCommandsTest
Where Key=VariableTestID=@TestIDWHERE TestID=@TestID

Save time by copying relevant parts

Hope this helps you out, happy coding!

Was this helpful?

Please note, this commenting system is still in final testing.
Copyright Claytabase Ltd 2020

Registered in England and Wales 08985867

Site Links

RSSLoginLink Cookie PolicySitemap

Social Media

facebook.com/Claytabaseinstagram.com/claytabase/twitter.com/Claytabaselinkedin.com/company/claytabase-ltd

Get in Touch

+15125961417info@claytabase.comClaytabase USA, 501 Congress Avenue, Suite 150, Austin, Texas, 78701, United States

Partnered With

The settings on this site are set to allow all cookies. These can be changed on our Cookie Policy & Settings page.
By continuing to use this site you agree to the use of cookies.
Ousia Logo
Logout
Ousia CMS Loader