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

  • Thread starter Thread starter David Elliott
  • Start date Start date
D

David Elliott

I originally posted this in microsoft.public.dotnet.framework.adonet
under the title of
"IF" test in SP evals to True and False at same time using DataAdapter.FillSchema()

I wasn't really getting anywhere. I thought I might try in these two to see if anyone else
has any thoughts.
microsoft.public.dotnet.framework
microsoft.public.sqlserver.programming


Thanks,
Dave


=====================================


The IF test in a Stored Procedure is evaluating to true and
false at the same time for Microsoft .NET DataAdapter.FillSchema()
Method. (See result below -- SELECT TOP 2 TOP 100 PERCENT) .

If I use DataAdapter.Fill() instead of DataAdapter.FillSchema(),
everything works just fine. So, I believe that there is some kind of
interaction issue between MS-SQL and MS .NET FillSchema()

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

========================================================
 
The answer was posted in microsoft.public.sqlserver.programming

I verified the problem exists in Query Analyzer using the "SET FMTONLY ON"
Also tested in my code after adding "SET FMTONLY OFF" to the Stored Procedure.
The problem is now resolved.

Cheers,
Dave

========================================================


DataAdapter.FillSchema runs your stored procedrue with the SET FMTONLY ON
option. Which aparently runs both branches of your IF statement looking for
result set metadata to return.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&[email protected]


There's just no reliable way to get the column metadata from a stored
procedure which uses dynamic SQL.

I suggest you use strongly typed datasets which which have the DataTable
metadata stored at design-time. You really shouldn't be discovering your
databases' metadata at runtime anyway.

Either that or point your DataAdapter to a static view or table for
FillSchema, where the view or table has the same row type as the stored
procedure returns. Then then use the stored procedure for Fill.


David
 
Back
Top