The IF test in a Stored Procedure is evaluating to true and
false at the same time for FillSchema()
(See result below -- SELECT TOP 2 TOP 100 PERCENT)
Any thoughts are appreciated.
Dave
Information on Problem
===============================
Below is the output from the Fill() and FillSchema(). The commands
were run back-to-back using the exact same SQL Command and DataAdapter.
DataAdapter.Fill()
===============================
DECLARE @request_list table (id uniqueidentifier not null);
INSERT INTO @request_list (id ) SELECT id FROM (SELECT TOP 2
id FROM view_3 top_requests;
DataAdapter.FillSchema()
===============================
Incorrect syntax near the keyword 'SOME'.
DECLARE @request_list table (id uniqueidentifier not null); SOME BOGUS TEXT
INSERT INTO @request_list (id ) SELECT id FROM (SELECT TOP 2 TOP 100 PERCENT
id FROM view_3 ) top_requests;
Code Snip of Stored Procedure that is failing
===============================
IF (@NEW_DEBUG_VAR <> 0)
BEGIN
SET @query = @query + N' SOME BOGUS TEXT '
END
ELSE
BEGIN
SET @query = @query + N' INSERT INTO
@request_list (id ) SELECT id FROM (SELECT'
IF (@top1<> 0 )
BEGIN
SET @query = @query + N' TOP ' + CAST(@top1 AS NVARCHAR(20))
END
ELSE
BEGIN
SET @query = @query + N' TOP 100 PERCENT '
END
END
Product Information
===============================
VS .NET 2003 V7.1.3088
.NET Framework v1.1 v1.1.4322
SQL Server 2000 - SP3 v8.00.761
Database Setup Information
===============================
There is a SQL file for setting up the database, below.
Sorry about the naming convention. I had to change the names of
everything before I was authorized to send it.
Stored Procedure up_sp_1 is the original. I have created up_sp_2
as a subset of up_sp_1 and added some additional debug code. They
both exhibit the same behavior.
Database information
server localhost
database FillSchemaDB
uid sa
pwd <not set>
Demo
===============================
Create a new Windows application and replace the Form1.cs code
with the code included below.
Form1.cs Code
====================================
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace MyNamespace.FillSchema
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
#region Windows Generated Stuff -- No Modifications Here
private System.Windows.Forms.Button button1;
private System.Windows.Forms.TextBox textBox1;
private System.Windows.Forms.TextBox textBox2;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.CheckBox checkBox1;
private System.Windows.Forms.CheckBox checkBox2;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.RadioButton radioButton2;
private System.Windows.Forms.RadioButton radioButton1;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
#endregion
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.textBox2 = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.checkBox1 = new System.Windows.Forms.CheckBox();
this.checkBox2 = new System.Windows.Forms.CheckBox();
this.radioButton2 = new System.Windows.Forms.RadioButton();
this.groupBox1 = new System.Windows.Forms.GroupBox();
this.radioButton1 = new System.Windows.Forms.RadioButton();
this.groupBox1.SuspendLayout();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(32, 568);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "Do Test";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(24, 40);
this.textBox1.Multiline = true;
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(656, 192);
this.textBox1.TabIndex = 1;
this.textBox1.Text = "";
//
// textBox2
//
this.textBox2.Location = new System.Drawing.Point(24, 280);
this.textBox2.Multiline = true;
this.textBox2.Name = "textBox2";
this.textBox2.Size = new System.Drawing.Size(656, 192);
this.textBox2.TabIndex = 2;
this.textBox2.Text = "";
//
// label1
//
this.label1.Location = new System.Drawing.Point(24, 16);
this.label1.Name = "label1";
this.label1.TabIndex = 3;
this.label1.Text = "Fill()";
//
// label2
//
this.label2.Location = new System.Drawing.Point(24, 256);
this.label2.Name = "label2";
this.label2.TabIndex = 4;
this.label2.Text = "FillSchema()";
//
// checkBox1
//
this.checkBox1.Location = new System.Drawing.Point(32, 488);
this.checkBox1.Name = "checkBox1";
this.checkBox1.Size = new System.Drawing.Size(416, 24);
this.checkBox1.TabIndex = 5;
this.checkBox1.Text = "Branch In SQL Server To Add \"SOME BOGUS TEXT\"";
//
// checkBox2
//
this.checkBox2.Location = new System.Drawing.Point(32, 528);
this.checkBox2.Name = "checkBox2";
this.checkBox2.Size = new System.Drawing.Size(336, 24);
this.checkBox2.TabIndex = 6;
this.checkBox2.Text = "Don\'t set @NEW_DEBUG_VAR value";
//
// radioButton2
//
this.radioButton2.Checked = true;
this.radioButton2.Location = new System.Drawing.Point(16, 64);
this.radioButton2.Name = "radioButton2";
this.radioButton2.TabIndex = 7;
this.radioButton2.TabStop = true;
this.radioButton2.Text = "up_sp_2";
this.radioButton2.CheckedChanged += new System.EventHandler(this.radioButton2_CheckedChanged);
//
// groupBox1
//
this.groupBox1.Controls.Add(this.radioButton1);
this.groupBox1.Controls.Add(this.radioButton2);
this.groupBox1.Location = new System.Drawing.Point(480, 488);
this.groupBox1.Name = "groupBox1";
this.groupBox1.TabIndex = 8;
this.groupBox1.TabStop = false;
this.groupBox1.Text = "Run Stored Procedure";
//
// radioButton1
//
this.radioButton1.Location = new System.Drawing.Point(16, 32);
this.radioButton1.Name = "radioButton1";
this.radioButton1.TabIndex = 8;
this.radioButton1.Text = "up_sp_1";
this.radioButton1.CheckedChanged += new System.EventHandler(this.radioButton1_CheckedChanged);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(8, 19);
this.ClientSize = new System.Drawing.Size(704, 604);
this.Controls.Add(this.groupBox1);
this.Controls.Add(this.checkBox2);
this.Controls.Add(this.checkBox1);
this.Controls.Add(this.label2);
this.Controls.Add(this.label1);
this.Controls.Add(this.textBox2);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.button1);
this.Font = new System.Drawing.Font("Microsoft Sans Serif", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.Name = "Form1";
this.Text = "Form1";
this.groupBox1.ResumeLayout(false);
this.ResumeLayout(false);
}
#endregion
#region Radio Button Stuff
private void radioButton1_CheckedChanged(object sender, System.EventArgs e)
{
checkBox1.Enabled = false;
checkBox2.Enabled = false;
checkBox1.Checked = false;
checkBox2.Checked = false;
}
private void radioButton2_CheckedChanged(object sender, System.EventArgs e)
{
checkBox1.Enabled = true;
checkBox2.Enabled = true;
}
#endregion
private void button1_Click(object sender, System.EventArgs e)
{
string s;
DataSet schemaDS = new DataSet();
DataSet fillDS = new DataSet();
SqlCommand cmd = null;
SqlConnection sqlConn;
SqlDataAdapter dataAdapter;
string connstr;
// ***********************************************************
// Create DB Connection
// ***********************************************************
connstr = "server=localhost;database=FillSchemaDB;uid=sa";
sqlConn = new SqlConnection(connstr);
// ***********************************************************
// Create SQL Command
// ***********************************************************
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
if (radioButton2.Checked == true)
cmd.CommandText = "up_sp_2";
else
cmd.CommandText = "up_sp_1";
cmd.Connection = sqlConn;
// Create Command Parameters
cmd.Parameters.Add("@top1", SqlDbType.Int);
cmd.Parameters["@top1"].Value = 2;
cmd.Parameters.Add("@where1", SqlDbType.NVarChar, 3500);
cmd.Parameters["@where1"].Value = " ";
if (radioButton2.Checked == true)
{
cmd.Parameters.Add("@NEW_DEBUG_VAR", SqlDbType.Int);
if (checkBox2.Checked == false)
{
if (checkBox1.Checked == true)
cmd.Parameters["@NEW_DEBUG_VAR"].Value = 1;
else
cmd.Parameters["@NEW_DEBUG_VAR"].Value = 0;
}
}
cmd.Parameters.Add("@rcquery", SqlDbType.NVarChar, 4000);
cmd.Parameters["@rcquery"].Direction = ParameterDirection.Output;
// ***********************************************************
// Create DataAdapter
// ***********************************************************
dataAdapter = new SqlDataAdapter(cmd);
// ***********************************************************
// Clear Text Boxes
// ***********************************************************
textBox1.Text = "";
textBox2.Text = "";
// ***********************************************************
// CALL DataAdapter.Fill() Method
// ***********************************************************
try
{
// Do Fill()
dataAdapter.Fill(fillDS);
}
catch (Exception ex)
{
// Display Error Message
textBox1.Text = ex.Message;
}
finally
{
// Display SQL Command That Was Created In SQL Server
s = cmd.Parameters["@rcquery"].Value.ToString();
textBox1.Text = textBox1.Text + "\r\n\r\n" + s;
s = null;
}
// ***********************************************************
// CALL DataAdapter.FillSchema() Method
// ***********************************************************
try
{
// Do FillSchema()
dataAdapter.FillSchema(schemaDS, SchemaType.Source);
}
catch (Exception ex)
{
// Display Error Message
textBox2.Text = ex.Message;
}
finally
{
// Display SQL Command That Was Created In SQL Server
s = cmd.Parameters["@rcquery"].Value.ToString();
textBox2.Text = textBox2.Text + "\r\n\r\n" + s;
s = null;
}
}
}
}
================================
SQL Code
================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_11]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_11
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_12]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_12
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_14_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_14] DROP CONSTRAINT FK_Table_14_Table_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_15_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_15] DROP CONSTRAINT FK_Table_15_Table_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_20_Table_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_20] DROP CONSTRAINT FK_Table_20_Table_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_key_2_Table_2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_17] DROP CONSTRAINT FK_key_2_Table_2
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_12_Table_3]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_12] DROP CONSTRAINT FK_Table_12_Table_3
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_8_key_1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_8] DROP CONSTRAINT FK_Table_8_key_1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_8_Table_5]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_8] DROP CONSTRAINT FK_Table_8_Table_5
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_7_Table_6]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_7] DROP CONSTRAINT FK_Table_7_Table_6
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_10_Table_9]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_10] DROP CONSTRAINT FK_Table_10_Table_9
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_10_Table_91]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_10] DROP CONSTRAINT FK_Table_10_Table_91
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_11_Table_7]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_11] DROP CONSTRAINT FK_Table_11_Table_7
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_8_Table_7]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_8] DROP CONSTRAINT FK_Table_8_Table_7
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_10_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_10] DROP CONSTRAINT FK_Table_10_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_12_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_12] DROP CONSTRAINT FK_Table_12_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_13_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_13] DROP CONSTRAINT FK_Table_13_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_14_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_14] DROP CONSTRAINT FK_Table_14_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_15_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_15] DROP CONSTRAINT FK_Table_15_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_16_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_16] DROP CONSTRAINT FK_Table_16_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_17_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_17] DROP CONSTRAINT FK_Table_17_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_18_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_18] DROP CONSTRAINT FK_Table_18_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_19_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_19] DROP CONSTRAINT FK_Table_19_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_key_3_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_20] DROP CONSTRAINT FK_key_3_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Table_21_Table_8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table_21] DROP CONSTRAINT FK_Table_21_Table_8
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Function_1]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Function_1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_sp_1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[up_sp_1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_3]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_3]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_4]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_4]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[view_2]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[view_2]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_10]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_10]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_12]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_12]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_13]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_13]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_14]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_14]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_15]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_15]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_16]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_16]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_17]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_17]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_18]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_18]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_19]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_19]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_20]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_20]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_21]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_21]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_11]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_11]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_8]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_8]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_7]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_7]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_1]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_2]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_3]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_4]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_4]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_5]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_5]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_6]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_6]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_9]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table_9]
GO
CREATE TABLE [Table_1] (
[id] [smallint] NOT NULL ,
[code] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[field_1] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table_2] (
[id] [bigint] NOT NULL ,
[name] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table_3] (
[id] [smallint] NOT NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Table_3] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table_4] (
[id] [int] NOT NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_key_1] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table_5] (
[id] [smallint] NOT NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Table_5] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table_6] (
[id] [smallint] NOT NULL ,
[name] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Table_6] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table_7] (
[id] [uniqueidentifier] NOT NULL ,
[field_2] [datetime] NOT NULL ,
[field_3] [smallint] NOT NULL ,
[field_4] [int] NOT NULL ,
[field_5] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[field_6] [smallint] NOT NULL ,
[field_7] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[field_8] [int] NOT NULL ,
[field_9] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[field_10] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[field_11] [uniqueidentifier] NULL ,
CONSTRAINT [PK_Table_7] PRIMARY KEY NONCLUSTERED
(
[id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_7_Table_6] FOREIGN KEY
(
[field_3]
) REFERENCES [Table_6] (
[id]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
CREATE TABLE [Table_8] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[type] [int] NOT NULL ,
[field_12] [datetime] NOT NULL ,
[field_13] [datetime] NOT NULL ,
[field_14] [smallint] NOT NULL ,
CONSTRAINT [PK_Table_8] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_8_Table_7] FOREIGN KEY
(
[reqid]
) REFERENCES [Table_7] (
[id]
) ON DELETE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_8_Table_5] FOREIGN KEY
(
[field_14]
) REFERENCES [Table_5] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_8_key_1] FOREIGN KEY
(
[type]
) REFERENCES [Table_4] (
[id]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
CREATE TABLE [Table_9] (
[id] [int] NOT NULL ,
[name] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Table_9] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table_10] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_15] [int] NULL ,
[field_16] [int] NOT NULL ,
CONSTRAINT [PK_Table_10] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_10_Table_9] FOREIGN KEY
(
[field_15]
) REFERENCES [Table_9] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_10_Table_91] FOREIGN KEY
(
[field_16]
) REFERENCES [Table_9] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_10_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
alter table [dbo].[Table_10] nocheck constraint [FK_Table_10_Table_9]
GO
alter table [dbo].[Table_10] nocheck constraint [FK_Table_10_Table_91]
GO
CREATE TABLE [Table_11] (
[id] [uniqueidentifier] NOT NULL ,
[field_17] [datetime] NOT NULL ,
[field_18] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [IX_Table_11] UNIQUE CLUSTERED
(
[id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_11_Table_7] FOREIGN KEY
(
[id]
) REFERENCES [Table_7] (
[id]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
CREATE TABLE [Table_12] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_19] [smallint] NULL ,
CONSTRAINT [PK_Table_12] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_12_Table_3] FOREIGN KEY
(
[field_19]
) REFERENCES [Table_3] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_12_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
alter table [dbo].[Table_12] nocheck constraint [FK_Table_12_Table_3]
GO
CREATE TABLE [Table_13] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_20] [smallint] NULL ,
[field_21] [smallint] NULL ,
[field_22] [smallint] NULL ,
CONSTRAINT [PK_Table_13] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_13_Table_1] FOREIGN KEY
(
[field_20]
) REFERENCES [Table_1] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_13_Table_11] FOREIGN KEY
(
[field_21]
) REFERENCES [Table_1] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_13_Table_12] FOREIGN KEY
(
[field_22]
) REFERENCES [Table_1] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_13_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
alter table [dbo].[Table_13] nocheck constraint [FK_Table_13_Table_1]
GO
alter table [dbo].[Table_13] nocheck constraint [FK_Table_13_Table_11]
GO
alter table [dbo].[Table_13] nocheck constraint [FK_Table_13_Table_12]
GO
CREATE TABLE [Table_14] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_23] [int] NOT NULL ,
[field_24] [smallint] NOT NULL ,
CONSTRAINT [PK_Table_14] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_14_Table_1] FOREIGN KEY
(
[field_24]
) REFERENCES [Table_1] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_14_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
alter table [dbo].[Table_14] nocheck constraint [FK_Table_14_Table_1]
GO
alter table [dbo].[Table_7] nocheck constraint [FK_Table_7_Table_6]
GO
CREATE TABLE [Table_15] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_25] [int] NOT NULL ,
[field_26] [smallint] NOT NULL ,
[field_27] [smallint] NOT NULL CONSTRAINT [DF_Table_15_field_27] DEFAULT (0),
CONSTRAINT [PK_Table_15] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_15_Table_1] FOREIGN KEY
(
[field_26]
) REFERENCES [Table_1] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_15_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
alter table [dbo].[Table_15] nocheck constraint [FK_Table_15_Table_1]
GO
CREATE TABLE [Table_16] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_28] [int] NOT NULL ,
CONSTRAINT [PK_Table_16] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_16_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
alter table [dbo].[Table_8] nocheck constraint [FK_Table_8_Table_5]
GO
alter table [dbo].[Table_8] nocheck constraint [FK_Table_8_key_1]
GO
CREATE TABLE [Table_17] (
[reqid] [uniqueidentifier] NOT NULL ,
[field_29] [int] NOT NULL ,
[field_30] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[field_31] [bigint] NOT NULL ,
[field_32] [int] NOT NULL ,
[field_33] [int] NOT NULL ,
[field_34] [int] NOT NULL ,
[field_35] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Table_17] PRIMARY KEY CLUSTERED
(
[reqid],
[field_29],
[field_30],
[field_31]
) ON [PRIMARY] ,
CONSTRAINT [FK_key_2_Table_2] FOREIGN KEY
(
[field_31]
) REFERENCES [Table_2] (
[id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_17_Table_8] FOREIGN KEY
(
[reqid],
[field_29]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
alter table [dbo].[Table_17] nocheck constraint [FK_key_2_Table_2]
GO
CREATE TABLE [Table_18] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_36] [int] NOT NULL ,
CONSTRAINT [PK_Table_18] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_18_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
CREATE TABLE [Table_19] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_37] [int] NOT NULL ,
[field_38] [smallint] NOT NULL ,
CONSTRAINT [PK_Table_19] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_19_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
CREATE TABLE [Table_20] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_39] [bigint] NOT NULL ,
[field_40] [smallint] NOT NULL ,
[field_41] [int] NOT NULL ,
[field_42] [int] NOT NULL ,
[field_43] [int] NOT NULL ,
[field_44] [int] NOT NULL ,
CONSTRAINT [PK_key_3] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num],
[field_39]
) ON [PRIMARY] ,
CONSTRAINT [FK_key_3_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_Table_20_Table_1] FOREIGN KEY
(
[field_40]
) REFERENCES [Table_1] (
[id]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
alter table [dbo].[Table_20] nocheck constraint [FK_Table_20_Table_1]
GO
CREATE TABLE [Table_21] (
[reqid] [uniqueidentifier] NOT NULL ,
[seq_num] [int] NOT NULL ,
[field_45] [int] NOT NULL ,
[field_46] [smallint] NOT NULL ,
CONSTRAINT [PK_Table_21] PRIMARY KEY CLUSTERED
(
[reqid],
[seq_num]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table_21_Table_8] FOREIGN KEY
(
[reqid],
[seq_num]
) REFERENCES [Table_8] (
[reqid],
[seq_num]
) ON DELETE CASCADE NOT FOR REPLICATION
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[Function_1] (@reqId uniqueidentifier)
RETURNS int AS
BEGIN
--status constant define
declare @var_1 int
SET @var_1 = 1
declare @var_2 int
SET @var_2 = 2
declare @var_3 int
SET @var_3 = 3
declare @var_4 int
SET @var_4 = 4
--task constant define
declare @var_5 int
SET @var_5 = 1
declare @var_6 int
SET @var_6 = 2
declare @var_7 int
SET @var_7 = 4
declare @var_8 int
SET @var_8 = 8
declare @var_9 int
SET @var_9 = 16
declare @var_10 int
SET @var_10 = 32
declare @var_11 int
SET @var_11 = 64
declare @var_12 int
SET @var_12 = 128
declare @var_13 int
SET @var_13 = 256
declare @var_14 int
SET @var_14 = 512
--get task mask
declare @var_15 int
SELECT @var_15 = field_4 FROM dbo.Table_7 WHERE id = @reqId
declare @var_16 int
--check tasks one by one
IF @var_15 & @var_5 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_5
IF @var_16 <> @var_3 return (@var_1)
END
IF @var_15 & @var_6 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_6
IF @var_16 <> @var_3 return (@var_16)
END
/***
IF @var_15 & @var_7 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_7
IF @var_16 <> @var_3 return (@var_16)
END
***/
IF @var_15 & @var_8 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_8
IF @var_16 <> @var_3 return (@var_16)
END
IF @var_15 & @var_9 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_9
IF @var_16 <> @var_3 return (@var_16)
END
IF @var_15 & @var_10 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_10
IF @var_16 <> @var_3 return (@var_16)
END
IF @var_15 & @var_11 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_11
IF @var_16 <> @var_3 return (@var_16)
END
IF @var_15 & @var_12 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_12
IF @var_16 <> @var_3 return (@var_16)
END
IF @var_15 & @var_13 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_13
IF @var_16 <> @var_3 return (@var_16)
END
IF @var_15 & @var_14 > 0
BEGIN
SET @var_16 = @var_2
SELECT @var_16=field_14 FROM dbo.Table_8 WHERE reqid = @reqId AND type = @var_14
IF @var_16 <> @var_3 return (@var_16)
END
return (@var_3)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.view_1
AS
SELECT id AS reqid, dbo.Function_1(id) AS status
FROM dbo.Table_7
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.view_2
AS
SELECT *
FROM dbo.Table_7
WHERE (id NOT IN
(SELECT id
FROM Table_11))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.view_3
AS
SELECT
dbo.view_2.id,
dbo.view_2.field_2,
dbo.view_2.field_5,
dbo.view_2.field_6,
dbo.view_2.field_7,
dbo.view_2.field_8,
dbo.view_2.field_9,
field_15,
field_19,
field_36,
field_28,
COALESCE(field_20, t20_field_40, t19_field_38, t14_field_24, t15_field_26, t21_field_46, 0) AS field_20,
dbo.view_1.status,
field_16,
field_37,
field_45,
t20_field_40,
field_44,
t19_field_38,
dbo.view_2.field_3,
dbo.view_2.field_4,
field_23,
t14_field_24,
field_25,
t15_field_26,
t21_field_46
FROM dbo.view_2
LEFT OUTER JOIN dbo.view_1 ON dbo.view_2.id = dbo.view_1.reqid
LEFT OUTER JOIN
(SELECT dbo.Table_8.reqid,
max(field_15) AS field_15,
max(field_19) AS field_19,
max(field_36) AS field_36,
max(field_28) AS field_28,
max(field_20) AS field_20,
max(field_16) AS field_16,
max(field_37) AS field_37,
max(field_45) AS field_45,
max(field_23) AS field_23,
max(field_25) AS field_25,
max(field_44) AS field_44,
max(dbo.Table_20.field_40) AS t20_field_40,
max(dbo.Table_19.field_38) AS t19_field_38,
max(dbo.Table_14.field_24) AS t14_field_24,
max(dbo.Table_15.field_26) AS t15_field_26,
max(dbo.Table_21.field_46) AS t21_field_46
FROM dbo.Table_8
LEFT OUTER JOIN dbo.Table_19 ON dbo.Table_8.reqid = dbo.Table_19.reqid AND dbo.Table_8.seq_num=dbo.Table_19.seq_num
LEFT OUTER JOIN dbo.Table_20 ON dbo.Table_8.reqid = dbo.Table_20.reqid AND dbo.Table_8.seq_num=dbo.Table_20.seq_num
LEFT OUTER JOIN dbo.Table_21 ON dbo.Table_8.reqid = dbo.Table_21.reqid AND dbo.Table_8.seq_num=dbo.Table_21.seq_num
LEFT OUTER JOIN dbo.Table_10 ON dbo.Table_8.reqid = dbo.Table_10.reqid AND dbo.Table_8.seq_num=dbo.Table_10.seq_num
LEFT OUTER JOIN dbo.Table_12 ON dbo.Table_8.reqid = dbo.Table_12.reqid AND dbo.Table_8.seq_num=dbo.Table_12.seq_num
LEFT OUTER JOIN dbo.Table_13 ON dbo.Table_8.reqid = dbo.Table_13.reqid AND dbo.Table_8.seq_num=dbo.Table_13.seq_num
LEFT OUTER JOIN dbo.Table_18 ON dbo.Table_8.reqid = dbo.Table_18.reqid AND dbo.Table_8.seq_num=dbo.Table_18.seq_num
LEFT OUTER JOIN dbo.Table_16 ON dbo.Table_8.reqid = dbo.Table_16.reqid AND dbo.Table_8.seq_num=dbo.Table_16.seq_num
LEFT OUTER JOIN dbo.Table_15 ON dbo.Table_8.reqid = dbo.Table_15.reqid AND dbo.Table_8.seq_num=dbo.Table_15.seq_num
LEFT OUTER JOIN dbo.Table_14 ON dbo.Table_8.reqid = dbo.Table_14.reqid AND dbo.Table_8.seq_num=dbo.Table_14.seq_num
GROUP BY dbo.Table_8.reqid ) t ON dbo.view_2.id = t.reqid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.view_4
AS
SELECT dbo.Table_8.*, Trys.define_1 AS define_1
FROM dbo.Table_8 INNER JOIN
(SELECT reqid, field_29, COUNT(*) AS define_1
FROM Table_17
GROUP BY reqid, field_29) Trys ON dbo.Table_8.seq_num = Trys.field_29 AND dbo.Table_8.reqid = Trys.reqid
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[up_sp_1] (
@top1 as int, -- how many requests must return
@where1 as nvarchar(3500), -- the search criteria, for example "WHERE.... ORDER BY field_2 DESC"
-- Added For Debug Of FillSchema
@rcquery as nvarchar(4000) output
)
AS
DECLARE @error_code int
DECLARE @query as nvarchar(4000)
SET @query = N'DECLARE @request_list table (id uniqueidentifier not null);'
SET @query = @query + N' INSERT INTO @request_list (id ) SELECT id FROM (SELECT'
IF (@top1 <> 0 )
SET @query = @query + N' TOP ' + CAST(@top1 AS NVARCHAR(20))
else
SET @query = @query + N' TOP 100 PERCENT '
SET @query = @query + N' id FROM view_3 ' + @where1 + N' ) top_requests;'
SET @query = @query + N' SELECT * FROM view_3 WHERE id in (SELECT id FROM @request_list);'
SET @query = @query + N' SELECT * FROM view_4 WHERE reqid IN (SELECT id FROM @request_list)'
-- Added For Debug Of FillSchema
set @rcquery = @query
select @query as myquery
EXECUTE(@query)
SET @error_code = @@error
IF (@error_code<> 0) GOTO error_handler
RETURN
error_handler:
--raise error
RAISERROR (@error_code,11,1) WITH SETERROR
GO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[up_sp_2] (
@top1 as int, -- how many requests must return
@where1 as nvarchar(3500), -- the search criteria, for example "WHERE.... ORDER BY field_2 DESC"
-- Added For Debug Of FillSchema
@NEW_DEBUG_VAR as int,
@rcquery as nvarchar(4000) output
)
AS
DECLARE @error_code int
DECLARE @query as nvarchar(4000)
SET @query = N'DECLARE @request_list table (id uniqueidentifier not null);'
---
--- NEW DEBUG CODE
---
IF (@NEW_DEBUG_VAR <> 0)
BEGIN
SET @query = @query + N' SOME BOGUS TEXT '
END
ELSE
BEGIN
--
-- ORIGINAL CODE -- NOT DEBUG
--
SET @query = @query + N' INSERT INTO @request_list (id ) SELECT id FROM (SELECT'
IF (@top1<> 0 )
BEGIN
SET @query = @query + N' TOP ' + CAST(@top1 AS NVARCHAR(20))
END
ELSE
BEGIN
SET @query = @query + N' TOP 100 PERCENT '
END
END
--
-- END NEW DEBUG CODE
--
SET @query = @query + N' id FROM view_3 ' + N' ) top_requests;'
-- Added For Debug Of FillSchema
set @rcquery = @query
select @query as myquery
EXECUTE(@query)
SET @error_code = @@error
IF (@error_code<> 0) GOTO error_handler
RETURN
error_handler:
--raise error
RAISERROR (@error_code,11,1) WITH SETERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
========================================================