How to programattically check if null value exists in database table (using stored procedure)?
I know it's possble in the Query Analyzer (see last SQL query batch statements)?
But how can I pass null value as parameter to the database stored procedure programattically using C#?
Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:
SQL Queries:
Expand|Select|Wrap|Line Numbers
- USE [master]
- GO
- IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
- DROP DATABASE [ExampleDatabase];
- GO
- CREATE DATABASE [ExampleDatabase];
- GO
- USE [ExampleDatabase];
- GO
- IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
- DROP TABLE dbo.ExampleTable;
- GO
- CREATE TABLE
- dbo.ExampleTable
- (
- ID INT IDENTITY(1,1) NOT NULL,
- UserID INT NULL,
- Name NVARCHAR(50) NULL,
- DateOfBirth DATETIME,
- IsActive BIT,
- Phone NVARCHAR(50) NULL,
- Fax NVARCHAR(50) NULL,
- CONSTRAINT PK_ID PRIMARY KEY(ID),
- CONSTRAINT UNIQUE_Phone UNIQUE(Phone),
- CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
- CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
- );
- GO
- INSERT INTO dbo.ExampleTable
- (
- UserID,
- Name,
- DateOfBirth,
- IsActive,
- Phone,
- Fax
- )
- VALUES
- (
- 1,
- 'Bill',
- '12-31-2000',
- 'False',
- '12345678',
- '12345678'
- );
- GO
- INSERT INTO dbo.ExampleTable
- (
- UserID,
- Name,
- DateOfBirth,
- IsActive
- )
- VALUES
- (
- 2,
- 'Larry',
- '12-31-2005',
- 'True'
- );
- GO
- SELECT * FROM ExampleTable;
- GO
- IF EXISTS(SELECT NAME FROM SYS.PROCEDURES WHERE NAME = N'CheckForeignKeyFax')
- DROP PROCEDURE dbo.CheckForeignKeyFax;
- GO
- CREATE PROCEDURE dbo.CheckForeignKeyFax
- (
- @Fax NVARCHAR(50)
- )
- AS
- DECLARE @ResultFax INT
- IF EXISTS
- (
- SELECT
- NULL
- FROM
- dbo.ExampleTable WITH (UPDLOCK)
- WHERE
- ISNULL(Phone, 'NULL') = ISNULL(@Fax, 'NULL')
- --(Phone IS NULL AND @Fax IS NULL) OR (@Fax = Phone)
- )
- BEGIN SELECT @ResultFax = 0 END
- ELSE BEGIN SELECT @ResultFax = -1 END
- RETURN @ResultFax
- GO
- DECLARE @ReturnValue INT
- EXEC @ReturnValue = CheckForeignKeyFax @Fax = '12345678'
- SELECT ReturnValue=@ReturnValue;
- GO
- DECLARE @ReturnValue INT
- EXEC @ReturnValue = CheckForeignKeyFax @Fax = NULL
- SELECT ReturnValue=@ReturnValue;
- GO
Expand|Select|Wrap|Line Numbers
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Linq;
- using System.Text;
- using System.Windows.Forms;
- using System.Data.SqlClient;
- namespace WindowsFormsApplication1
- {
- public partial class Form1 : Form
- {
- public Form1()
- {
- InitializeComponent();
- }
- private string ConnectionString
- {
- get
- {
- return @"Persist Security Info=False;Data Source=.\SQLEXPRESS;User ID=sa;Password=asdfglkjh;Initial Catalog=master;";
- }
- }
- private int CheckFax(string fax)
- {
- int Result = -1;
- try
- {
- using (SqlConnection ConnectionSql = new SqlConnection(ConnectionString))
- {
- using (SqlCommand CommandSql = new SqlCommand("ExampleDatabase.dbo.CheckForeignKeyFax"))
- {
- CommandSql.CommandType = CommandType.StoredProcedure;
- CommandSql.Parameters.Add(new SqlParameter("@Fax", fax));
- SqlParameter ParameterSql = new SqlParameter("@ReturnValue", DbType.Int32);
- ParameterSql.Direction = ParameterDirection.ReturnValue;
- CommandSql.Parameters.Add(ParameterSql);
- ConnectionSql.Open();
- CommandSql.Connection = ConnectionSql;
- CommandSql.ExecuteScalar();
- Result = Int32.Parse(CommandSql.Parameters["@ReturnValue"].Value.ToString());
- ConnectionSql.Close();
- }
- }
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.ToString());
- }
- return Result;
- }
- private void button1_Click(object sender, EventArgs e)
- {
- MessageBox.Show(CheckFax(textBox1.Text).ToString());
- }
- }
- }
Please help!