How to pass a value or no value inside a T-SQL or stored Procedure

How to pass a value  or no value inside a T-SQL or stored Procedure

Let's say we have a table called state which hold three column (StateId, StateCode and StateName). If you don't have this table in your test database, go ahead and make one.

CREATE TABLE [dbo].[State]
(
StateCodeID int IDENTITY(1,1),
StateCode varchar(2) NOT NULL,
[State] varchar(25) NOT NULL
);

Let's insert some data in this table. You can copy these script and insert into your table.

Insert INTO State VALUES
('AK', 'Alaska')
,('AL', 'Alabama')
,('AR', 'Arkansas')
,('AZ', 'Arizona')
,('CA', 'California')
,('CO', 'Colorado')
,('CT', 'Connecticut')
,('DC', 'Dist. of Columbia')
,('DE', 'Delaware')
,('FL', 'Florida')
,('GA', 'Georgia')
,('HI', 'Hawaii')
,('IA', 'Iowa')
,('ID', 'Idaho')
,('IL', 'Illinois')
,('IN', 'Indiana')
,('KS', 'Kansas')
,('KY', 'Kentucky')
,('LA', 'Louisiana')
,('MA', 'Massachusetts')
,('MD', 'Maryland')
,('ME', 'Maine')
,('MI', 'Michigan')
,('MN', 'Minnesota')
,('MO', 'Missouri')
,('MS', 'Mississippi')
,('MT', 'Montana')
,('NC', 'North Carolina')
,('ND', 'North Dakota')
,('NE', 'Nebraska')
,('NH', 'New Hampshire')
,('NJ', 'New Jersey')
,('NM', 'New Mexico')
,('NV', 'Nevada')
,('NY', 'New York')
,('OH', 'Ohio')
,('OK', 'Oklahoma')
,('OR', 'Oregon')
,('PA', 'Pennsylvania')
,('RI', 'Rhode Island')
,('SC', 'South Carolina')
,('SD', 'South Dakota')
,('TN', 'Tennessee')
,('TX', 'Texas')
,('UT', 'Utah')
,('VA', 'Virginia')
,('VT', 'Vermont')
,('WA', 'Washington')
,('WI', 'Wisconsin')
,('WV', 'West Virginia')
,('WY', 'Wyoming');

So this script should enter 51 rows in our table.


Coming to the point, lets say someone ask you write a stored procedure where he need stateCodeID and State value if user enter statecode, if not he want to see all the values.

Let's go ahead write this script

CREATE PROCEDURE [dbo].[usp_GetStateInformation] 
(
@Statecode VARCHAR(2) = NULL
)
AS
Begin
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SELECT STATECODE
,[STATE]
FROM [STATE]
WHERE STATECODE = ISNULL(@Statecode, StateCode)
ORDER BY StateCode
END;


"ISNULL(@Statecode, StateCode)" this is important function

What ISNULL do is that if there is no value in our variable @StateCode, it will pass null value in select query and it will return all the rows.

Now try executing this stored procedure as

Exec usp_GetStateInformation 'TX'

In this you will get statecode and state value only for 'TX'

And like this

Exec usp_GetStateInformation

In this you will get all the statecode and state value for all rows (51)

SQL: Intersect, Except and Union

Let's say we have Table A with ID Column  and its values are (1,2,3,4,5) and Table B with its ID Column and values (3,4,5,6,7).

Let's say we want to write different queries giving us something like this:
Find me all the value which common in both table? (3,4,5)

Find me all unique value in both table? (1,2,3,4,5,6,7)

Find me all values which are unique in table A and that are also not in Table B (1,2)

Find me all values which are unique in both Table (1,2,6,7)

Fire your SQL Server and let's create these table and insert data in them and see how it work.


CREATE Table TableA
(ID int);
GO
INSERT INTO TableA VALUES (1),(2),(3),(4),(5);
GO
CREATE Table TableB
(ID int);
GO
INSERT INTO TableB VALUES (3),(4),(5),(6),(7);

Find me all the value which common in both table? (3,4,5)

To find values in both table, take a look at the picture above. The question ask: what are the values which are common to both table? What word comes to your mind? Intersect? right.. there is keyword "INTERSECT" in sql language.

Try this


SELECT * FROM TableA
INTERSECT
SELECT * FROM TableB;

Result

ID

3

4

5

Find me all unique value in both table? (1,2,3,4,5,6,7)

To find value which are unique in both table, take a look at picture above again. think about it. programming and database is all about logic.

Select * from Table A

Union

Select * from Table B

Result

ID

1

2

3

4

5

6

7

If you do UNION ALL, we will get repeat value of common number in both table like this.

ID

1

2

3

4

5

3

4

5

6

7

Find me all values which are unique in table A and that are also not in Table B (1,2)

In this case we have to use EXCEPT keyword.

SELECT * FROM TableA

EXCEPT

SELECT * FROM TableB;

Result

ID

1

2

Find me all values which are unique in both Table (1,2,6,7)

For this query we have to really think hard.. think about UNION and EXCEPT combining somehow?

SELECT * FROM TableA

Union 

Select * FROM TableB

EXCEPT

SELECT * FROM TableA

INTERSECT

SELECT * FROM TableB;

Result

ID

1

2

6

7

Hope this help you.

Few things to remember about these queries are:

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

1.   The number and the order of the columns must be the same in all queries.

2.   The data types must be compatible.

IF you don't follow this rule, will will get error message.

Cheers!!!

SubQuery and JOIN: A beginner concept

Microsoft define subquery  as "A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. "

Let's look at an example of this subquery and how can we avoid subquery all together by using JOIN functionality.

For this example I am going to use AdventureWork database.

We are going to look at some sales table and show you how you can avoid subquery by using JOIN.

Lets say we want to look at SalesOrderID, SalesOrderDate and Maximum UnitPrice from our sales table
(SalesOrderDetail and SalesOrderHeader ).

So first write down what we want.

--Select SalesOrderID, SalesOrderDate and Maximum UnitPrice from Table SalesOrderDetail and Table SalesOrderHeader 


So let's go ahead and write on query using subquery



SELECT  Ord.SalesOrderID
,Ord.OrderDate
,(
SELECT max(OrdDet.UnitPrice)
FROM AdventureWorks.Sales.SalesOrderDetail OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID
) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord;


Now let's try writing down similar query which will give the same result.


SELECT  Ord.SalesOrderID
,Ord.OrderDate
,max(OrdDet.UnitPrice) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader Ord
 JOIN AdventureWorks.Sales.SalesOrderDetail OrdDet ON Ord.SalesOrderID = OrdDet.SalesOrderID
GROUP BY Ord.SalesOrderID
,Ord.OrderDate;

If possible try avoiding use of subquery in your T-SQL.

Here is execution plan for the 2 queries.

SQL Error while Inserting Image...one of those error

When we try to insert image into a sql table, sometime we get error like this.


Msg 491, Level 16, State 1, Line 4
A correlation name must be specified for the bulk rowset in the from clause.

So let's look at a simple SQL statement.

Suppose we have a simple table consisting of ImageID, ImageName, and Image itself column.

Let's create a simple table and we will go through it.

Create Table ImageTable
(ImageID int IDENTITY(1,1),
ImageName varchar(100),
Imagefile IMAGE
)

Let's make sure that we have right table.

SELECT * FROM ImageTable

Now that we have table ready to store or save image, let go ahead and write a sql statement



Insert INTO dbo.ImageTable
(ImageName,Imagefile)
SELECT  'MyPhoto',* FROM OPENROWSET(BULK N'C:\Data\HeaderImage\Header_A.png', SINGLE_BLOB)

You were expecting that this should have insert a row in our table with the image..!!! Right but you got the error.


So before we look into this, let's give a alias name to our sql statement.


Insert INTO dbo.ImageTable
(ImageName,Imagefile)
SELECT  'MyPhoto',* FROM OPENROWSET(BULK N'C:\Data\HeaderImage\Header_A.png', SINGLE_BLOB) A;

Now try to run this script.

It worked!!! Right.

Well lets now try to understand why we got the error in the first place.

Openrowset is looking for an alias in our first sql statement and it cannot find one, so it is throwing an error message "A correlation name must be specified for the bulk rowset in the from clause."

In our case, generally speaking its a good practice to give alias to all our tables. Like

Select * from Customer C;
Select C.CustomerID, C.CustomerFirstName, etc From Customer C.

So here all we have to do is give an alias to openrowset 'A'

:)

Kumar

Procedure Error Logging method

Imagine you run 1000's of stored procedure every day in your organization through various applications. How do we keep an eye on them?

To do this, lets create a table where we log procedure causing failure.

--Create Error Log Table to save Error Information

CREATE TABLE [dbo].[ProcedureErrorLog](
LogDate     SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
DatabaseID INT,
DatabaseName varchar(100),
ProcedureName VARCHAR(400),
ErrorNumber [int] NOT NULL,
ErrorMessage [varchar](4000) COLLATE Latin1_General_BIN NULL,
ErrorSeverity [smallint] NOT NULL,
ErrorState [smallint] NOT NULL DEFAULT ((1)),
ErrorLine [int] NOT NULL DEFAULT ((0)),
UserName [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
HostName [varchar](128) COLLATE Latin1_General_BIN NOT NULL DEFAULT (''),
TimeStamp datetime NOT NULL,
)
GO

CREATE CLUSTERED INDEX LogDate ON ProcedureErrorLog(LogDate);

GO

Go ahead and create a stored procedure which store all the information in our log table. You can customized to include more information about this event.


CREATE procedure dbo.usp_GLC_StoredProcedureErrorLog AS
-- Declaration statements
DECLARE @LogDate DateTime
DECLARE @DatabaseID    INT
DECLARE @DatabaseName varchar(100)
DECLARE @ObjectID    INT
DECLARE @ProcedureName  VARCHAR(400)
DECLARE @ErrorNumber int
DECLARE @ErrorMessage varchar(4000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
DECLARE @ErrorProcedure varchar(200)
DECLARE @ErrorLine int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @TimeStamp datetime

-- Initialize variables
SELECT @LogDate = GETDATE(),
@DatabaseID = COALESCE(@DatabaseID, DB_ID()),
@DatabaseName = DB_NAME(),
@ProcedureName = COALESCE
  (
   QUOTENAME(DB_NAME(@DatabaseID)) + '.'
   + QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectID, @DatabaseID))
   + '.' + QUOTENAME(OBJECT_NAME(@ObjectID, @DatabaseID)),
   ERROR_PROCEDURE()
  ),
@ErrorNumber = isnull(error_number(),0),
@ErrorMessage = isnull(error_message(),'NULL Message'),
@ErrorSeverity = isnull(error_severity(),0),
@ErrorState = isnull(error_state(),1),
@ErrorLine = isnull(error_line(), 0),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@TimeStamp = GETDATE();

-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ProcedureErrorLog (LogDate, DatabaseID,DatabaseName, ProcedureName, ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine, UserName, HostName, [TimeStamp])

SELECT @LogDate, @DatabaseID, @DatabaseName, @ProcedureName, @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine,@UserName, @HostName, @TimeStamp


---Test this procedure



CREATE PROCEDURE dbo.spTest AS
BEGIN TRY

SELECT 1/0

END TRY

BEGIN CATCH

EXEC dbo.usp_GLC_StoredProcedureErrorLog

END CATCH

Exec dbo.spTest

--
SELECT * FROM ProcedureErrorLog