"IF" test in SP evals to True and False at same time using DataAdapter.FillSchema()

D

David Elliott

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

========================================================
 
K

Kevin Yu [MSFT]

Hi Dave,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that there might be something wrong with the
stored procedure in your application. If there is any misunderstanding,
please feel free to let me know.

I have checked you code, however, it was too big. Could you please make a
smaller package that can reproduce the problem?

From the code you have provided, you are using SELECT TOP 2 TOP 100
PERCENT. But as far as I know, we could only use on TOP in a SELECT
statement. Could you please check it?

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
D

David Elliott

Kevin,

You said,
"First of all, I would like to confirm my understanding of your issue. From
your description, I understand that there might be something wrong with the
stored procedure in your application. If there is any misunderstanding,
please feel free to let me know."

I believe there is an interaction problem with FillSchema() and a Stored Procedure.

If you were to create a DataAdapter and assign it a Stored Procedure, fill in the parameter
values and direction, with no other changes, you then call
1) Fill()
Everything works fine
2) FillSchema()
An error is produced. The output from the SP is
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;

You said
"From the code you have provided, you are using SELECT TOP 2 TOP 100
PERCENT. But as far as I know, we could only use on TOP in a SELECT
statement. Could you please check it?"

This is the problem that I am explaining. The SQL statement is being constructed in the SP based
on a variable.


Within the Stored Procedure there is an IF block

SET @query = @query + N' INSERT INTO @request_list (id ) SELECT id FROM (SELECT'

<HERE> 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

The line listed as <HERE> evaluate to True and False in the same run of the procedure.
This is IMPOSSIBLE. This is why you get SELECT TOP 2 TOP 100 PERCENT.


You said,
"I have checked you code, however, it was too big. Could you please make a
smaller package that can reproduce the problem? "

I wasn't expecting someone to look at the code in a text editor. I made the program so someone
could step through it because it is rather complex.

Cheers,
Dave
 
K

Kevin Yu [MSFT]

Hi David,

Based on your explanation, I think the problem exists in SQL stored
procedure. Since it has much to do with the database, I suggest you try SQL
Debugging in VS.NET. You can try to step into the stored procedure in SQL
server to check for parameter values.

Here is a link for Debugging in Visual Studio .NET.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechar
t/html/vstchdebugginginvisualstudionet.asp

The following link has some detailed information about how to setup for SQL
debugging and some debugging limitations.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/htm
l/_core_Debugging_SQL.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
D

David Elliott

I couldn't DISAGREE with you more.

You are saying that the SP is the problem. I say it is FillSchema(). If you take the
sample code an strip it to this.

connstr = "server=localhost;database=FillSchemaDB;uid=sa";
sqlConn = new SqlConnection(connstr);

cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "up_sp_2";
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 = " ";


cmd.Parameters.Add("@rcquery", SqlDbType.NVarChar, 4000);
cmd.Parameters["@rcquery"].Direction = ParameterDirection.Output;


dataAdapter = new SqlDataAdapter(cmd);

// **************************************
// THIS WILL SUCCEED
// **************************************
dataAdapter.Fill(fillDS);

// **************************************
// THIS WILL THROW AN EXCEPTION
// **************************************
dataAdapter.FillSchema(schemaDS, SchemaType.Source);


The Fill() will Succeed and the FillSchema() will fail if you run them back to back
or if you like just with the FillSchema.

I will read the information that you provided and attempt to debug the SQL.

The fact of the matter is that
if x is set to a value of 10
and you perform a test of
If (x == 0)
the resultant CAN NOT evaluate to True and False at the same time. This
is the problem regardless of what the parameter values are set to.


Cheers,
Dave
 
D

David Elliott

I am having trouble stepping into the SQL code.

1) Server Explorer
I can Right Click and select "Step into Stored Procedure" and this works fine.
The problem with this is that I NEED to use FillSchema() to reproduce the error.

2) If I drag a DataAdapter on a new Windows Project and set it all up and then call
Fill() or FillSchema(), I can't step into the SQL code. My break point on the SQL
line is a RED Dot with a ? inside it.

If I put my cursor on it the message states,
"The breakpoint will not currently be hit. No symbols have been loaded for this document."

I have turned on SQL debugging in VS -=> Project -=> Properties -=> Configuration Properties
-=> Debugging -=> Enable SQL Debugging = True

Any help would be appreciated.

Dave


I couldn't DISAGREE with you more.

You are saying that the SP is the problem. I say it is FillSchema(). If you take the
sample code an strip it to this.

connstr = "server=localhost;database=FillSchemaDB;uid=sa";
sqlConn = new SqlConnection(connstr);

cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "up_sp_2";
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 = " ";


cmd.Parameters.Add("@rcquery", SqlDbType.NVarChar, 4000);
cmd.Parameters["@rcquery"].Direction = ParameterDirection.Output;


dataAdapter = new SqlDataAdapter(cmd);

// **************************************
// THIS WILL SUCCEED
// **************************************
dataAdapter.Fill(fillDS);

// **************************************
// THIS WILL THROW AN EXCEPTION
// **************************************
dataAdapter.FillSchema(schemaDS, SchemaType.Source);


The Fill() will Succeed and the FillSchema() will fail if you run them back to back
or if you like just with the FillSchema.

I will read the information that you provided and attempt to debug the SQL.

The fact of the matter is that
if x is set to a value of 10
and you perform a test of
If (x == 0)
the resultant CAN NOT evaluate to True and False at the same time. This
is the problem regardless of what the parameter values are set to.


Cheers,
Dave


Hi David,

Based on your explanation, I think the problem exists in SQL stored
procedure. Since it has much to do with the database, I suggest you try SQL
Debugging in VS.NET. You can try to step into the stored procedure in SQL
server to check for parameter values.

Here is a link for Debugging in Visual Studio .NET.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechar
t/html/vstchdebugginginvisualstudionet.asp

The following link has some detailed information about how to setup for SQL
debugging and some debugging limitations.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/htm
l/_core_Debugging_SQL.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
Click to expand...
Click to expand...
 
D

David Elliott

Here is what I did
==================================
Used the program from the link you sent:
http://support.microsoft.com/?id=316549

Click Configuration Properties, and then click to
select the SQL Server Debugging check box.

Changed
HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}\OLEDB_SERVICES
from 0xffffffff to 0xfffffffe
-- Tried to run program didn't solve problem.
-- Rebooted machine, tried again, NO luck.

Tried in both VB.NET and C#.NET, no difference.

Server Explorer
I can Right Click and select "Step into Stored Procedure" and this STILL works fine.
NEED to use FillSchema() to reproduce the error so this won't work.

Still have the (?) for a break point when looking at the SP inside of
Visual Studio and running the program. The message when positioning
my cursor over the breakpoint changed
from
"The breakpoint will not currently be hit. No symbols have been
loaded for this document."
to
"The breakpoint will currently not be hit. Unable to bind
SQL breakpoint at this time. Object containing the breakpoint
not loaded."


Information is returned from SP, here is some of the output
====================================================
Product ordered: Aniseed Syrup
Product ordered: Chartreuse verte
Product ordered: Escargots de Bourgogne


Information from Northwind Properties, viewed from "Server Explorer"
====================================================================
ELLIOTT.Northwind.dbo

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=ELLIOTT;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=ELLIOTT;Use Encryption for Data=False;Tag with column collation when possible=False

Microsoft OLE DB Provider for SQL Server


Software Information:
======================================
Windows 2000 Server (SP4)
VS .NET 2003 ---
SQL Server 2000 (SP3)


Cheers,
Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"IF" test in SP evals to True and False at same time using DataAdapter.FillSchema() 1
SQL Server Error: 8629 2
SqlDataAdapter.Fill throws me an IndexOutOfRangeException 4
Execute File SQL in AdoNET VB 2
MS-SQL AND ACCESS LINKED TABLE PROBLEM (#ERROR FIELDS) (cross) 1
Executing Stored Proc from file in Ado .NET 10
executenonquery returning -1 problem 3
How to export DataTable to SQL Server when the SQL table doesn't exist 0

Top