tag:blogger.com,1999:blog-18831143678905861572024-02-20T03:35:53.052-08:00SQL Server SolutionsSQL Server 2000/2005Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-1883114367890586157.post-19820551250948804192012-09-10T02:58:00.000-07:002012-09-10T03:02:07.557-07:00Create Generic Template to Create Store Procedure<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
This store procedure will be used to create a generic SP for insert, update,
delete, select, select all statement. This will create a store procedure
script for the given table and SPType (Insert, Update, Select, SelectAll,
Delete).<br />
<br />
<strong>Copy the following Store Procedure:</strong></div>
<blockquote>
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/*<br />
Created By: Lakhan Pal<br />
Created On: 07/02/2012<br />
Purpose: To Create Store Procedure Template for Given Table for <br />
Insert, Update, Select, SelectAll, And Delete Script<br />
User Just need to Pass Following Arguments<br />
(1) @TableName VARCHAR(100)<br />
(2) @AuthorName VARCHAR(100)<br />
(3) @SPType VARCHAR(15) (Insert, Update, Select, SelectAll, Delete)<br />
(4) @Comment VARCHAR(500) [Optional]<br />
Execution Statement:<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Insert'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Update'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Select'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'SelectAll'<br />
EXEC uspCreateGenericTemplateForSP 'tblUsers', 'Lakhan Pal', 'Delete'<br />
*/<br />
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CREATE</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">PROCEDURE</span></span><span style="font-size: x-small;"> uspCreateGenericTemplateForSP</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(<br />
</span></span><span style="font-size: x-small;">@TableName </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">100</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;">@AuthorName </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">100</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;">@SPType </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">15</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;">@Comment </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">500</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)=NULL</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
)<br />
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS<br />
BEGIN<br />
DECLARE</span>
</span><span style="font-size: x-small;"> @MaxID </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">4000</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DECLARE</span>
</span><span style="font-size: x-small;"> @IsIdentityExist </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">Int</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">4000</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DECLARE</span>
</span><span style="font-size: x-small;"> @ScriptTable </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">Table</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">ID </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IDENTITY</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">1</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">1</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DECLARE</span>
</span><span style="font-size: x-small;"> Script </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">5000</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><span style="font-size: x-small;"> RecordType </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">Char</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">1</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SET</span>
</span><span style="font-size: x-small;"> @IsIdentityExist </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">0 </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span>
</span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">''</span></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span>
</span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">''</span></span><br />
<span style="color: red; font-size: x-small;"></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF </span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@Comment </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">IS</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NULL)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> @Comment </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' To Create script for '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @SPType </span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' Record into Table '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @TableName</span><br />
<br />
<span style="font-size: x-small;">
</span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* To Check the Identity Column */<br />
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span>
</span><span style="font-size: x-small;"> @IsIdentityExist </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">COUNT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Name</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span>
</span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">1</span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">
</span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* Comment Section - Start Here */</span>
</span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'/*'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">) </span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Created By: '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @AuthorName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Created On: '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;"> <span style="color: magenta;"><span style="color: magenta;">CONVERT</span></span><span style="color: grey;"><span style="color: grey;">(</span></span><span style="color: blue;"><span style="color: blue;">VARCHAR</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>10<span style="color: grey;"><span style="color: grey;">),</span></span> <span style="color: magenta;"><span style="color: magenta;">GETDATE</span></span><span style="color: grey;"><span style="color: grey;">(),</span></span> 103<span style="color: grey;"><span style="color: grey;">))</span></span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Purpose: '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @Comment</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'*/'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* Comment Section - End Here */</span>
</span><br />
<span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'CREATE PROCEDURE usp'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;">@SPType </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'_'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @TableName </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'('</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* If Identity Column Exists */<br />
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF </span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@IsIdentityExist</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">></span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">BEGIN</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> RecordType</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' @'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'P'</span></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">Sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">COLUMNS</span></span><span style="font-size: x-small;"> C </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">TYPES</span></span><span style="font-size: x-small;"> T </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Object_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">) </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">1</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">END</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
IF</span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@SPType </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Insert'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">OR</span></span><span style="font-size: x-small;"> @SPType </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Update'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">BEGIN</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* Parameter Section - Start Here */</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> RecordType</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' @'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'P'</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">Sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">COLUMNS</span></span><span style="font-size: x-small;"> C </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">TYPES</span></span><span style="font-size: x-small;"> T </span><br />
<span style="font-size: x-small;"> <span style="color: blue;"><span style="color: blue;">ON</span></span> C<span style="color: grey;"><span style="color: grey;">.</span></span>System_Type_ID<span style="color: grey;"><span style="color: grey;">=</span></span> T<span style="color: grey;"><span style="color: grey;">.</span></span>System_Type_ID </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Object_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">0 </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">NOT</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">IN</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">173</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 175</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 239</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 231</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">165</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 167</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">106</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 108</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">UNION</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' @'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'('</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CONVERT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><span style="font-size: x-small;">C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">MAX_Length</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">', '</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CONVERT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><span style="font-size: x-small;">C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">Precision</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">')'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'P'</span></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">Sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">COLUMNS</span></span><span style="font-size: x-small;"> C </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">TYPES</span></span><span style="font-size: x-small;"> T </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Object_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">0 </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">IN</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">106</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 108</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">UNION</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' @'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">Name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'('</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CONVERT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">10</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">),</span></span><span style="font-size: x-small;">C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">MAX_Length</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">')'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'P'</span></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">Sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">COLUMNS</span></span><span style="font-size: x-small;"> C </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">TYPES</span></span><span style="font-size: x-small;"> T </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Object_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> C</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">0 </span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> T</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">System_Type_ID </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">IN</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">173</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 175</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 239</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 231</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">165</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 167</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* Parameter Section - End Here */</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">END</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SELECT</span>
</span><span style="font-size: x-small;"> @MaxID </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">MAX</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">ID</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> @ScriptTable </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> RecordType</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'P'</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> UPDATE</span>
</span><span style="font-size: x-small;"> @ScriptTable </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> Script </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> ID</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">@MaxID</span><span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">')'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'AS'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' BEGIN'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* Query Section - Start Here */<br />
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF</span>
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@SPType </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Insert'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">BEGIN</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
@paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'@'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">0</span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SET</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' INSERT INTO '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @TableName </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' ('</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">')'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' VALUES ('</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">')'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> END</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
ELSE</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@SPType </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Update'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> BEGIN</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SELECT</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' = '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'@'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">0</span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' = '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'@'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> WHERE </span></span><span style="font-size: x-small;"></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">1</span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> IF </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@IsIdentityExist</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">></span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SET</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'WHERE '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> ELSE</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SET</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">''</span></span><br />
<span style="color: red; font-size: x-small;"></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' UPDATE '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' SET '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> END</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
ELSE</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@SPType</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Select'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">BEGIN</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)AND</span></span><span style="font-size: x-small;"> is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">0</span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' = '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'@'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">1</span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> IF </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@IsIdentityExist</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">></span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SET</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'WHERE '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> ELSE</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SET</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">''</span></span><br />
<span style="color: red; font-size: x-small;"></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' SELECT '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' FROM '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> END</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
ELSE</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@SPType</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'SelectAll'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> BEGIN</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SELECT</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @ColList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;"> <span style="color: blue;"><span style="color: blue;">SET</span></span> @ColList <span style="color: grey;"><span style="color: grey;">=</span></span> <span style="color: magenta;"><span style="color: magenta;">SUBSTRING</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>@ColList<span style="color: grey;"><span style="color: grey;">,</span></span>0<span style="color: grey;"><span style="color: grey;">,</span></span><span style="color: magenta;"><span style="color: magenta;">Len</span></span><span style="color: grey;"><span style="color: grey;">(</span></span>@ColList<span style="color: grey;"><span style="color: grey;">))</span></span></span><br />
<span style="color: grey; font-size: x-small;"></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' SELECT '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @ColList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' FROM '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> END</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
ELSE</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@SPType </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'Delete'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> BEGIN</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SELECT</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' = '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'@'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> name </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">','</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">columns</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> WHERE</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">object_id</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> is_identity </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;">1</span><br />
<span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">IF </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@IsIdentityExist</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">></span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SET</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'WHERE '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">0</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">Len</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">@paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> ELSE</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SET</span></span><span style="font-size: x-small;"> @paramList </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">''</span></span><br />
<span style="color: red; font-size: x-small;"></span><br />
<span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' DELETE FROM '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @TableName</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> @paramList</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> END</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"> /* Query Section - End Here */</span>
</span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> INSERT</span>
</span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">INTO</span></span><span style="font-size: x-small;"> @ScriptTable</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">Script</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VALUES</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' END'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> SELECT</span>
</span><span style="font-size: x-small;"> Script </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> @ScriptTable</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">END</span></span>
</blockquote>
</div>
Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-23453044483219609632009-11-21T11:59:00.000-08:002009-11-21T11:59:19.640-08:00Query in SQL Server to get the Parameter list of given Store ProcedureWrite a Query in SQL Server to get the Parameter list of given Store Procedure.<br />
<br />
Suppose we want to get the name of the parameter for the Store Procedure<br />
Course_Insert_sp then folllowing needs to be executed.<br />
<blockquote>SELECT * FROM sys.parameters D INNER JOIN Sys.Objects O ON O.object_id=D.object_id WHERE O.name='Course_Insert_sp'<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-72778816112183902012009-11-21T11:58:00.000-08:002009-11-21T11:58:18.919-08:00Write a SQL Statement to Get the Definition of the Store ProceduresSQL Statement to Get the Definition of the System Store Procedures.<br />
<blockquote>SELECT definition FROM sys.system_sql_modules<br />
</blockquote>SQL Statement to Get the Definition of the User defined Store Procedures.<br />
<blockquote>SELECT definition FROM sys.sql_modules<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-2772493747314753872009-11-21T11:54:00.001-08:002009-11-21T11:54:52.784-08:00Query to Get the Forgien Key Name Their Primary Table and Referrence TableFollowing Query will give you the List of Forgien Key in a Database with Their Primary and Referrence Tables Name.<br />
<br />
Sample Query:<br />
<blockquote>SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F<br />
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id<br />
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id<br />
</blockquote>If you want to get forgien key for a particular table then you can use like this:<br />
<blockquote>SELECT F.name AS ForgienKey,O2.name AS ParentTable,O3.name AS RefTable FROM sys.foreign_keys F<br />
INNER JOIN Sys.Objects O2 ON O2.object_id=F.parent_object_id<br />
INNER JOIN Sys.Objects O3 ON O3.object_id=F.referenced_object_id<br />
Where O3.name='Tbl_User'<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-40108902585386987702009-11-21T11:53:00.000-08:002009-11-21T11:53:13.533-08:00Query to get the name of the identity column for a given tableQuery to get the name of the identity column for a given table.<br />
To Get the Identity column name for Tbl_Customer we need to execute the following query.<br />
<blockquote>SELECT D.name AS ColumnName, O.name AS TableName<br />
FROM sys.identity_columns D INNER JOIN Sys.Objects O ON O.object_id=D.object_id<br />
Where O.name='Tbl_Customer<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-81078664013925032512009-11-21T11:51:00.000-08:002009-11-21T11:51:55.984-08:00Query to Get the dependencies for given Table NameFollowing Query is used to get the dependencies for the given Table.<br />
Suppose we need to check the dependencies for the table tbl_Customer<br />
<br />
Sample Query:<br />
<blockquote>DECLARE @Objectname varchar(100)<br />
SET @Objectname='tbl_Customer'<br />
<br />
SELECT O1.name,O2.name FROM sys.sql_dependencies D INNER JOIN Sys.Objects O1 ON O1.object_id=D.object_id<br />
INNER JOIN Sys.Objects O2 ON O2.object_id=D.referenced_major_id<br />
WHERE O1.name=@Objectname OR O2.name=@Objectname<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-61977340467434919522009-10-27T09:35:00.000-07:002009-10-27T09:35:34.603-07:00Interview Questions<b>(1) How To Update Description Value for a Column in Table using SQL Command?</b><br />
We can Update Description to Column using sp_updateextendedproperty System Store Procedure.<br />
Sample Command to Update Description for Column in a Table:<br />
<blockquote style="color: #38761d;">EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'<br />
</blockquote><br />
<b>(2) How To Delete Description Value for a Column in Table using SQL Command?</b><br />
We can Delete Description from Column using sp_dropextendedproperty System Store Procedure.<br />
Sample Command to Delete Description from Column in a Table:<br />
<blockquote style="color: #38761d;">EXEC sys.sp_dropextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'<br />
</blockquote><br />
<b>(3) How we can add Description to the Column using Sql Command?</b><br />
We can Add Description to Column using sp_addextendedproperty System Store Procedure.<br />
Sample Command to Insert Description for Column in a Table:<br />
<blockquote style="color: #38761d;">EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'My Description for Column Here' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MyTableName', @level2type=N'COLUMN',@level2name=N'ColumnName'<br />
</blockquote><br />
<b>(4) How To Get Description Value for all Column in Table using SQL Command?</b><br />
To Get Description of Columns we need to use system function sys.fn_listextendedproperty.<br />
Command To Get Description Data for all Columns:<br />
<blockquote style="color: #38761d;">SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',<br />
'dbo', 'TABLE', 'YourTable Name Here', 'COLUMN', NULL)<br />
</blockquote><br />
<b>(5) How To Get Description Value for Single Column in Table using SQL Command?</b><br />
To Get Description of Single Column we need to use system function sys.fn_listextendedproperty. we need to pass the column Name is this case.<br />
Command To Get Description Data for Single Columns:<br />
<blockquote style="color: #38761d;">SELECT * FROM fn_listextendedproperty(NULL, 'SCHEMA',<br />
'dbo', 'TABLE', 'Table Name Here', 'COLUMN', 'Column Name Here')<br />
</blockquote><br />
<b>(6) How We can get the DB name using SQL Command?</b><br />
Following is the Command to get the DB name using Command analyzer<br />
<blockquote style="color: #38761d;">SELECT DB_NAME()<br />
</blockquote><br />
<b>(7) What is the use of Set NOCOUNT ON;?</b><br />
By Default When we execute any command it return us the number of record affected. if we don't want to return the number of records affected then we can use <br />
<div style="color: #38761d;">SET NOCOUNT ON;<br />
</div>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-4549944881084150572009-10-27T09:17:00.001-07:002009-10-27T09:20:31.934-07:00Store Procedure To Create Script for Create TableFollowing is the store procedure to create script for create table. this will create script for all constraint, description of columns, and index etc.<br />
<br />
<b>Store Procedure Script:</b><br />
<blockquote style="color: #38761d;">ALTER PROCEDURE sp_CreateTableScript <br />
( <br />
@TableName SYSNAME, <br />
@IncludeConstraints BIT = 1, <br />
@IncludeIndexes BIT = 1, <br />
@NewTableName SYSNAME = NULL, <br />
@UseSystemDataTypes BIT = 0 <br />
) <br />
AS <br />
BEGIN <br />
SET NOCOUNT ON; <br />
DECLARE @MainDefinition TABLE <br />
( <br />
FieldValue NVARCHAR(500) <br />
) <br />
DECLARE @DBName SYSNAME <br />
DECLARE @ClusteredPK BIT <br />
DECLARE @TableSchema NVARCHAR(255) <br />
SET @DBName = DB_NAME(DB_ID()) <br />
SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName) <br />
DECLARE @ShowFields TABLE <br />
( <br />
FieldID INT IDENTITY(1,1), <br />
DatabaseName VARCHAR(100), <br />
TableOwner VARCHAR(100), <br />
TableName VARCHAR(100), <br />
FieldName VARCHAR(100), <br />
ColumnPosition INT, <br />
ColumnDefaultValue VARCHAR(100), <br />
ColumnDefaultName VARCHAR(100), <br />
IsNullable BIT, <br />
DataType VARCHAR(100), <br />
MaxLength INT, <br />
NumericPrecision INT, <br />
NumericScale INT, <br />
DomainName VARCHAR(100), <br />
FieldListingName VARCHAR(110), <br />
FieldDefinition CHAR(1), <br />
IdentityColumn BIT, <br />
IdentitySeed INT, <br />
IdentityIncrement INT, <br />
IsCharColumn BIT <br />
) <br />
DECLARE @HoldingArea TABLE <br />
( <br />
FldID SMALLINT IDENTITY(1,1), <br />
Flds VARCHAR(4000), <br />
FldValue CHAR(1) DEFAULT(0) <br />
) <br />
DECLARE @PKObjectID TABLE <br />
( <br />
ObjectID INT <br />
) <br />
DECLARE @Uniques TABLE <br />
( <br />
ObjectID INT <br />
) <br />
DECLARE @HoldingAreaValues TABLE <br />
( <br />
FldID SMALLINT IDENTITY(1,1), <br />
Flds VARCHAR(4000), <br />
FldValue CHAR(1) DEFAULT(0) <br />
) <br />
DECLARE @Definition TABLE <br />
( <br />
DefinitionID SMALLINT IDENTITY(1,1), <br />
FieldValue NVARCHAR(500) <br />
) <br />
INSERT INTO @ShowFields <br />
( <br />
DatabaseName, <br />
TableOwner, <br />
TableName, <br />
FieldName, <br />
ColumnPosition, <br />
ColumnDefaultValue, <br />
ColumnDefaultName, <br />
IsNullable, <br />
DataType, <br />
MaxLength, <br />
NumericPrecision, <br />
NumericScale, <br />
DomainName, <br />
FieldListingName, <br />
FieldDefinition, <br />
IdentityColumn, <br />
IdentitySeed, <br />
IdentityIncrement, <br />
IsCharColumn <br />
) <br />
SELECT <br />
DB_NAME(), <br />
TABLE_SCHEMA, <br />
TABLE_NAME, <br />
COLUMN_NAME, <br />
CAST(ORDINAL_POSITION AS INT), <br />
COLUMN_DEFAULT, <br />
dobj.name AS ColumnDefaultName, <br />
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, <br />
DATA_TYPE, <br />
CAST(CHARACTER_MAXIMUM_LENGTH AS INT), <br />
CAST(NUMERIC_PRECISION AS INT), <br />
CAST(NUMERIC_SCALE AS INT), <br />
DOMAIN_NAME, <br />
COLUMN_NAME + ',','' AS FieldDefinition, <br />
CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn, <br />
CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed, <br />
CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement, <br />
CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn <br />
FROM <br />
INFORMATION_SCHEMA.COLUMNS c <br />
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name <br />
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name <br />
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name <br />
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D' <br />
WHERE c.TABLE_NAME = @TableName <br />
ORDER BY <br />
c.TABLE_NAME, c.ORDINAL_POSITION <br />
SELECT TOP 1 @TableSchema = TableOwner <br />
FROM @ShowFields <br />
INSERT INTO @HoldingArea (Flds) VALUES('(') <br />
INSERT INTO @Definition(FieldValue) <br />
VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @TableSchema + '.' + @TableName END) <br />
INSERT INTO @Definition(FieldValue) <br />
VALUES('(') <br />
INSERT INTO @Definition(FieldValue) <br />
SELECT <br />
CHAR(10) + FieldName + ' ' + <br />
CASE <br />
WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN <br />
DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END <br />
ELSE UPPER(DataType) + <br />
CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END + <br />
CASE WHEN IdentityColumn = 1 THEN <br />
' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END + <br />
CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END + <br />
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN <br />
'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END <br />
END + <br />
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END <br />
FROM @ShowFields <br />
IF @IncludeConstraints = 1 <br />
BEGIN <br />
INSERT INTO @Definition(FieldValue) <br />
SELECT <br />
',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')' <br />
FROM <br />
( <br />
SELECT <br />
ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name, <br />
REVERSE(SUBSTRING(REVERSE(( <br />
SELECT cp.name + ',' <br />
FROM <br />
sys.foreign_key_columns fkc <br />
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id <br />
WHERE fkc.constraint_object_id = fk.object_id <br />
FOR XML PATH('') <br />
)), 2, 8000)) ParentColumns, <br />
REVERSE(SUBSTRING(REVERSE(( <br />
SELECT cr.name + ',' <br />
FROM <br />
sys.foreign_key_columns fkc <br />
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id <br />
WHERE fkc.constraint_object_id = fk.object_id <br />
FOR XML PATH('') <br />
)), 2, 8000)) ReferencedColumns <br />
FROM sys.foreign_keys fk <br />
) a <br />
WHERE ParentObject = @TableName <br />
INSERT INTO @Definition(FieldValue) <br />
SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints <br />
WHERE OBJECT_NAME(parent_object_id) = @TableName <br />
INSERT INTO @PKObjectID(ObjectID) <br />
SELECT DISTINCT <br />
PKObject = cco.object_id <br />
FROM <br />
sys.key_constraints cco <br />
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id <br />
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id <br />
WHERE <br />
OBJECT_NAME(parent_object_id) = @TableName AND <br />
i.type = 1 AND <br />
is_primary_key = 1 <br />
INSERT INTO @Uniques(ObjectID) <br />
SELECT DISTINCT <br />
PKObject = cco.object_id <br />
FROM <br />
sys.key_constraints cco <br />
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id <br />
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id <br />
WHERE <br />
OBJECT_NAME(parent_object_id) = @TableName AND <br />
i.type = 2 AND <br />
is_primary_key = 0 AND <br />
is_unique_constraint = 1 <br />
SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END <br />
INSERT INTO @Definition(FieldValue) <br />
SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN <br />
' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END <br />
WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' + <br />
REVERSE(SUBSTRING(REVERSE(( <br />
SELECT <br />
c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' <br />
FROM <br />
sys.key_constraints ccok <br />
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id <br />
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id <br />
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id <br />
WHERE <br />
i.object_id = ccok.parent_object_id AND <br />
ccok.object_id = cco.object_id <br />
FOR XML PATH('') <br />
)), 2, 8000)) + ')' <br />
FROM <br />
sys.key_constraints cco <br />
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID <br />
LEFT JOIN @Uniques u ON cco.object_id = u.objectID <br />
WHERE <br />
OBJECT_NAME(cco.parent_object_id) = @TableName <br />
END <br />
INSERT INTO @Definition(FieldValue) <br />
VALUES(')') <br />
INSERT INTO @Definition(FieldValue) <br />
VALUES('GO') <br />
IF @IncludeIndexes = 1 <br />
BEGIN <br />
INSERT INTO @Definition(FieldValue) <br />
SELECT <br />
ISNULL(('CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' + <br />
REVERSE(SUBSTRING(REVERSE(( <br />
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' <br />
FROM <br />
sys.index_columns sc <br />
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id <br />
WHERE <br />
OBJECT_NAME(sc.object_id) = @TableName AND <br />
sc.object_id = i.object_id AND <br />
sc.index_id = i.index_id <br />
ORDER BY index_column_id ASC <br />
FOR XML PATH('') <br />
)), 2, 8000)) + ')' ),'') <br />
FROM sys.indexes i <br />
WHERE <br />
OBJECT_NAME(object_id) = @TableName <br />
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1 <br />
AND is_unique_constraint = 0 <br />
AND is_primary_key = 0 <br />
END <br />
INSERT INTO @Definition(FieldValue) <br />
SELECT 'EXEC sys.sp_addextendedproperty @name=N'''+name+''', @value=N'''+CONVERT(varchar(100),value)+''' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+@TableName+''', <br />
@level2type=N''COLUMN'',@level2name=N'''+objName+''''<br />
FROM ::fn_listextendedproperty(NULL, 'SCHEMA',<br />
'dbo', 'TABLE', @TableName, 'COLUMN', NULL) WHERE CONVERT(varchar(100),value)<>'' <br />
<br />
INSERT INTO @MainDefinition(FieldValue) <br />
SELECT FieldValue FROM @Definition <br />
ORDER BY DefinitionID ASC <br />
SELECT * FROM @MainDefinition <br />
END <br />
</blockquote><br />
How to execute the above store procedure and get the create table script:<br />
<blockquote style="color: #38761d;">EXEC sp_CreateTableScript 'TableName'<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-2855353251750476402009-10-27T09:04:00.001-07:002009-10-27T09:06:46.723-07:00Create Script File For Each Store Procedure And Save into Seperate SQL File For EachWith the help of <b>sqlcmd utility</b> we can save the script of store procedure in separate file for each store procedure.<br />
<br />
Name of the file will be same as the name of the store procedure.<br />
<b>Sample Code to Create Script and Save</b>:<br />
<blockquote style="color: #38761d;">select N'sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext ' + name + N' " -h-1 -k 1 >> c:\DB\sp\' + name + N'.sql' from sys.objects where type='P'<br />
</blockquote><br />
When you will execute the above command the following output will be generated based on the number of store procedure in your database:<br />
<br />
<blockquote style="color: #38761d;">sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_Authenticate_sp " -h-1 -k 1 >> c:\DB\sp\User_Authenticate_sp.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext User_GetPassword_sp " -h-1 -k 1 >> c:\DB\sp\User_GetPassword_sp.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_upgraddiagrams " -h-1 -k 1 >> c:\DB\sp\sp_upgraddiagrams.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagrams " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagrams.sql<br />
sqlcmd -U sa -P sa -S localhost -d DataBaseName -Q "exec sp_helptext sp_helpdiagramdefinition " -h-1 -k 1 >> c:\DB\sp\sp_helpdiagramdefinition.sql<br />
</blockquote><br />
Save these output data into a batch file execute this batch file. this will save the store procedure script on <b>C:\DB\sp</b>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-62425565066481842092009-10-05T06:07:00.000-07:002009-10-05T06:07:43.256-07:00Parse XML Data in SQL Server 2005 without using OPENXML MethodIn <a href="http://lakhangarg.blogspot.com/search/label/SQL%20Server">SQL Server 2005</a> we can parse the XML Data without using OPENXML Methods that was used in SQL Server 2000.<br />
There are few methods defined in <a href="http://lakhangarg.blogspot.com/search/label/SQL%20Server">SQL Server 2005</a> for XML DataType like:<br />
<ul><li>nodes</li>
<li>query</li>
<li>value</li>
</ul><span style="font-weight: bold;">Sample Code:</span><br />
<blockquote style="color: black;">DECLARE @XmlData xml<br />
SET @XmlData='<Library><br />
<Subject name="ASP.NET"><br />
<Book ID="1"><br />
<Author>Lakhan Pal Garg</Author><br />
<Title>ASP.NET Tips</Title><br />
<Price>$100</Price><br />
</Book><br />
<Book ID="2"><br />
<Author>Lakhan Pal Garg</Author><br />
<Title>SQL Server Tips</Title><br />
<Price>$90</Price><br />
</Book><br />
</Subject><br />
<Subject name="XML"><br />
<Book ID="3"><br />
<Author>Peter</Author><br />
<Title>XSLT Tutorial</Title><br />
<Price>$140</Price><br />
</Book><br />
<Book ID="4"><br />
<Author>Rihana</Author><br />
<Title>XML Parsing in SQL Server</Title><br />
<Price>$120</Price><br />
</Book><br />
</Subject><br />
</Library>'<br />
<br />
select R.i.value('@ID', 'varchar(30)') [BookID],<br />
R.i.query('Author').value('.', 'varchar(30)') [Author],<br />
R.i.query('Title').value('.', 'varchar(30)') [Title],<br />
R.i.query('Price').value('.', 'varchar(30)') [Price] <br />
from @XmlData.nodes('/Library/Subject/Book') R(i)<br />
</blockquote><br />
In the above Select Statement we have used <span style="color: black;">@XmlData.nodes and this will return a node list we used the Alias for this "R" and i is the </span>index of the node. now to read the value of a attribute we can use R.i.value('@ID','INT') [BookID] here BookID is Alias name for column. and to read the value of an element that is child of Book we need to write like this R.i.query('Author').value('.','varchar(30)') [AuthorName] Author is the name of Child element of Book.Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com2tag:blogger.com,1999:blog-1883114367890586157.post-63894693325437432892009-10-05T06:05:00.000-07:002009-10-05T06:05:26.857-07:00Create XML Data From SQL Server Using For XML ExplicitThere are different ways to get data in <a href="http://lakhangarg.blogspot.com/search/label/XML">XML</a> format from<a href="http://lakhangarg.blogspot.com/search/label/SQL%20Server"> SQL Server</a> like:<br />
<ul><li>For XML Auto</li>
<li>For XML RAW</li>
<li>For XML Explicit</li>
</ul>But in the first and second method we can't customize the format of <a href="http://lakhangarg.blogspot.com/search/label/XML">XML</a>. if we need customize XML then we need to use "For XML Explicit Method" With the Help of "For <a href="http://lakhangarg.blogspot.com/search/label/XML">XML</a> Explicit" we can create XML in the required format.<br />
<br />
'Tag' and 'Parent' Column are used to determine the hierarchy of xml.<br />
Now this code will generate a parent node name root and Session as Child of Root.<br />
<br />
[Session!2!ID] this will create an attribute of Session Node Named ID.<br />
and [Session!2!Notes!element] this will create a Child element of Session Named Notes.<br />
<blockquote><span style="font-weight: bold;">Sample Code:</span><br />
<div style="color: black;">SELECT 1 AS Tag, <br />
</div><div style="color: black;"> NULL AS Parent,<br />
</div><div style="color: black;"> NULL AS [Session!2!ID],<br />
</div><div style="color: black;"> NULL AS [Session!2!Notes!element],<br />
</div><div style="color: black;"> 0 AS [root!1!Customer!hide]<br />
</div><div style="color: black;">UNION ALL<br />
</div><div style="color: black;">SELECT 2 AS Tag, <br />
</div><div style="color: black;"> 1 AS Parent,<br />
</div><div style="color: black;"> Session.SessionID AS [Session!2!ID],<br />
</div><div style="color: black;"> Session.Notes AS [Session!2!Notes!element],<br />
</div><div style="color: black;"> 1 AS [root!1!Customer!hide]<br />
</div><div style="color: black;">FROM tbl_Session AS Session WHERE Session.SessionID<20<br />
</div><div style="color: black;">FOR XML EXPLICIT<br />
</div></blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-29288458716569590132009-09-26T03:22:00.000-07:002009-09-26T03:22:38.083-07:00Get Total Number of a Particular Day between Two DatesFrom this Set of Query we can find out the occurance of a particular day between two given dates.<br />
<br />
if you want to get calculate total no of sat then set @DayNumber=7 and for Sun Set @DayNumber=1, Mon @DayNumber=2 and so on..<br />
<br />
<blockquote>DECLARE @Count int, @Startdate Datetime,@EndDate datetime, @DayNumber int<br />
SET @Count=0<br />
SET @Startdate=GETDATE()-1000<br />
SET @EndDate=GETDATE()<br />
SET @DayNumber=7<br />
DECLARE @StartDay Int<br />
<br />
WHILE(@EndDate>@Startdate)<br />
BEGIN<br />
SET @StartDay=DATEPART(dw, @Startdate)<br />
if(@StartDay=7)<br />
BEGIN<br />
SET @Count=@Count+1<br />
SET @Startdate=@Startdate+7<br />
END<br />
else<br />
BEGIN<br />
SET @StartDay=@DayNumber-@StartDay<br />
SET @Startdate=@Startdate+@StartDay<br />
END<br />
END<br />
SELECT @Count<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-16670915465657176702009-09-25T23:33:00.001-07:002009-09-25T23:33:55.079-07:00Store Procedure to be Used in Custom PaggingWith the help of the below set of query we can get the number of record that we want to show to user.<br />
<br />
For this we need to pass two parameters. first is the @RecordsToPick (Number of Records that you want to picjk for the page) and the second is @PageNumber (Page number for which you want to get the records from Database.)<br />
<br />
<blockquote>USE DBName<br />
GO<br />
DECLARE @RecordsToPick smallint, @PageNumber smallint<br />
SET @RecordsToPick = 10<br />
SET @PageNumber = 2<br />
<br />
DECLARE @StartRow INT<br />
DECLARE @EndRow INT<br />
SET @StartRow = ((@PageNumber-1) * @RecordsToPick)<br />
SET @EndRow = @StartRow + @RecordsToPick<br />
<br />
SELECT * FROM ( SELECT UserName,UserID,City,State,Country ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber<br />
FROM as_TblMembers) As AliasName WHERE RowNumber > @StartRow AND RowNumber <= @EndRow
GO
</blockquote><br />
In the above store procedure first we will get the @StartRow and @EndRow to get the number of first record and last record respectively.<br />
<blockquote>ROW_NUMBER()OVER(ORDER BY UserID) AS RowNumber<br />
</blockquote>ROW_NUMBER() will assign a unique number to each query order by UserID. with the help of it is easy for us to get the required result.Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0tag:blogger.com,1999:blog-1883114367890586157.post-45765925006953436982009-09-23T13:06:00.000-07:002009-09-26T03:28:55.450-07:00Create Web ServiceMS SQL Server 2005 provides us functionality to create web service using HTTP Endpoints. First of all you need to create HTTP ENDPOINT only then HTTP/SOAP can access SQL Server.<br />
<br />
<b>Different values that can be used to create web service are:</b><br />
<b>State </b>can have the following values<br />
<ul><li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">STARTED Means Listening and Responding</span></li>
<li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">DISABLED Means Neither Listening nor Responding </span></li>
<li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">STOPPED Means Listening But Not Responding</span></li>
</ul><span style="color: #333333; font-family: Arial; font-size: medium;"><span style="font-size: 14px; line-height: 22px;">HTTP or TCP can be used as transport protocol. AS HTTP Can have following arguments.</span></span><br />
<ul><li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">Path: virtual path on the web server where the webservice will reside.</span></li>
<li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">Authentication: it can be of three types</span> <br />
<br />
<ul><li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">INTEGRATED: Most Secure</span></li>
<li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">DIGEST: Not Secure</span></li>
<li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">BASIC: Least Secure</span><br />
</li>
</ul><br />
</li>
<li><span style="color: #333333; font-family: Arial; font-size: medium;"><span style="font-size: 14px; line-height: 22px;">Ports: can be of two types</span></span> <br />
<br />
<ul><li><span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">CLEAR: (HTTP - port 80 by default)</span></li>
<li><span style="color: #333333; font-family: Arial; font-size: medium;"><span style="font-size: 14px; line-height: 22px;">SSL: (HTTPS - port 443 by default) </span></span><br />
</li>
</ul><br />
</li>
<li><span style="color: #333333; font-family: Arial; font-size: medium;"><span style="font-size: 14px; line-height: 22px;">Site: Name of the server where service is running</span></span></li>
</ul>Arguments of For SOAP:<br />
<ul><li>WEBMETHOD: Name of the Webmethod</li>
<li>NAME: write name of the store procedure here</li>
<li>BATCHES: can be of two types <br />
<br />
<ul><li>ENABLED means<span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;"> multiple SOAP request/response message pairs can be identified as part of a single SOAP session</span></li>
<li>DISABLED means only <span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">SOAP request/response message pairs can be identified as part of a single SOAP session</span><br />
</li>
</ul><br />
</li>
<li>DATABASE : Write name of the database</li>
<li>WSDL: <span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">Specify how the Document Generation Will Occur</span> <br />
<br />
<ul><li>DEFAULT means response to the request will be generated using default format.</li>
<li>NONE means <span style="color: #333333; font-family: Arial; font-size: 14px; line-height: 22px;">no response is generated or returned for query.</span><br />
</li>
</ul><br />
</li>
<li>NAMESPACE: URL of the web service</li>
</ul><blockquote>CREATE ENDPOINT wsUserData<br />
STATE= STARTED<br />
AS HTTP<br />
(<br />
PATH = '/myWebservices'<br />
AUTHENTICATION= INTEGRATED,<br />
PORTS= CLEAR,<br />
SITE='localhot'<br />
)<br />
FOR SOAP<br />
(<br />
WEBMETHOD 'GetUserData'<br />
(NAME='myDB.dbo.GetUserData_sp'),<br />
BATCHES= DISABLED,<br />
WSDL= DEFAULT,<br />
DATABASE = 'myDB',<br />
NAMESPACE= 'http://localhost/myWebservices'<br />
)<br />
</blockquote>Lakhan Pal Garghttp://www.blogger.com/profile/03990328532714851479noreply@blogger.com0