Mar 22, 2013

An expression of non-boolean type specified in a context where a condition is expected, near '' sql server


If we write non-boolean expression in IF statement, WHERE clause, HAVING clause etc it is comes under syntax error. For example:

DECLARE @Value AS BIT = 1

IF @Value
SELECT 1

Or

DECLARE @Parms AS INTEGER = 10
SELECT * FROM Student WHERE @Parms

If we will execute above queries will get error message:

An expression of non-boolean type specified in a context where a condition is expected, near ''.

Solution: We have to write valid Boolean expression. For example:

DECLARE @Value AS BIT = 1

IF @Value = 1
SELECT 1

Or

DECLARE @Parms AS INTEGER = 10
SELECT * FROM Student WHERE Roll_No > @Parms

Mar 18, 2013

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.


Suppose we have Customer table with following data:

CustID
Emp_Name
1
Scott
2
Greg
3
Davis

If we will execute following sql query:

SELECT
    CustID + 100,
    Emp_Name
INTO #Temp
FROM Customer

Or

SELECT
    CustID + 100 "",
    Emp_Name
INTO #Temp
FROM Customer

Or

SELECT
    CustID + 100 [],
    Emp_Name
INTO #Temp
FROM Customer

We will get error message:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

Cause: In SELECT INTO statement, all columns in the select list must have column alias if it is sub query or derived column. And alias cannot be ''' or []

Solution:

Use columns alias in the derived column CustID + 100. For example:

SELECT
    CustID + 100 AS CustID,
    Emp_Name
INTO #Temp
FROM Customer

Mar 9, 2013

How to debug stored procedures in SSMS (Sql server management studio)

How to debug stored procedures in SSMS (Sql server management studio)

To debug a stored procedures or functions in SSMS (sql server management studio 2008 r2) 

Step 1: Create stored procedure which you want to debug. Suppose I have created following stored procedure:

CREATE PROC TestDebug(
@MaxLimit AS INTEGER
)
AS

DECLARE @Count AS INTEGER

SET @Count = 0

WHILE @Count < @MaxLimit BEGIN

SELECT @Count 
SET @Count = @Count + 2
END

Step 2: To debug this stored procedure, write an execute procedure statement and add breakpoints. As shown in the following figure:



Step 3: Click on debug button or press Alt + F5. Debug button has shown in following screen by read oval.


Step 4: To go next lines press F11 key or click on step into button in SSMS. Control will move to code of stored procedure as shown in the following figure:


Step 5: Repeat the step 4 to go to the next line. Also at bottom in the Locals window you can get values and data type of the local variable.

Mar 4, 2013

Sql server restrict the stored procedure naming convention


We can restrict or forced the users to follow stored procedure naming convention by using policy based management in sql server. I am explaining it by using sql server management studio (SSMS). In this example user must have to keep prefix usp_ in the name of stored procedure in the specific database.

Step 1: Right click on Management -> Policy Management -> Policies and then click on New Policy:

 Step 2: In create New policy window:
Name: We can write any name of policy. In this example we are writing Stored Procedure Naming Rule.
Now we are going to click on new condition in the check condition drop down to add the conditions.


Step 3: In the create new condition window:
Name: We can write any name of the condition. In this example we are keeping Procedure Name Condition.
Facets: Choose stored procedure in the drop down.
Expression:
Field: On which property we want to put condition. In this example we are choosing @Name which implies name of the stored procedure.
Operator: in this example we are choosing LIKE operator.
Value: 'usp_%'


Now click on the OK button.
Step 4: In this previous window that is create new policy window:
Enabled: We are checking it to enable it after its creation.
Evaluation Mode: In this example we are choosing On Change:Prevent. It means this event will occur if user will try to create or alter stored procedure name.


Step 5: This naming conditions is applicable in the all the database. If we want we can put this restriction in the specific database. For this click on the Every drop down for database and a new conditions. It will open new condition window. Write followings:
Field: @Name
Operator: IN
Value: Name of databases. In this example: Array('Exact_help','Employee')


Then click on OK buttons.

Now we are testing it by creating the stored procedure which doesn't follow the naming convention:

CREATE PROCEDURE GetData
AS
SELECT 1

We will get error message like:

Policy 'Stored Procedure Naming Rule' has been violated by ''.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'usp_%''
Policy description: ''
Additional help: '' : ''
Statement: 'CREATE PROCEDURE GetData
AS
SELECT 1'.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65
The transaction ended in the trigger. The batch has been aborted.

Means it is working fine. Now I am creating same stored procedure with correct naming convention:

CREATE PROCEDURE usp_GetData
AS
SELECT 1

It has created successfully. We can also created this policy using transact sql:

DECLARE @object_set_id INT
DECLARE @target_set_id INT

--Creating Policy
EXEC msdb.dbo.sp_syspolicy_add_object_set
@object_set_name=N'Stored Procedure Naming Rule_ObjectSet',
@facet=N'StoredProcedure',
@object_set_id=@object_set_id OUTPUT
--

EXEC msdb.dbo.sp_syspolicy_add_target_set
@object_set_name=N'Stored Procedure Naming Rule_ObjectSet',
@type_skeleton=N'Server/Database/StoredProcedure',
@type=N'PROCEDURE',
@enabled=True,
@target_set_id=@target_set_id OUTPUT
EXEC msdb.dbo.sp_syspolicy_add_target_set_level
@target_set_id=@target_set_id,
@type_skeleton=N'Server/Database/StoredProcedure',
@level_name=N'StoredProcedure',
@condition_name=N'',
@target_set_level_id=0
EXEC msdb.dbo.sp_syspolicy_add_target_set_level
@target_set_id=@target_set_id,
@type_skeleton=N'Server/Database',
@level_name=N'Database',
@condition_name=N'',
@target_set_level_id=0

GO

DECLARE @policy_id INT

EXEC msdb.dbo.sp_syspolicy_add_policy
@name=N'Stored Procedure Naming Rule',
@condition_name=N'Procedure Name Condition',
@execution_mode=1, @is_enabled=True,
@policy_id=@policy_id OUTPUT,
@object_set=N'Stored Procedure Naming Rule_ObjectSet'
GO

--Putting the naming restriction
DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition
@name=N'Procedure Name Condition',
@description=N'',
@facet=N'StoredProcedure',
@expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>LIKE</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>String</TypeClass>
<Name>Name</Name>
</Attribute>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>usp_%</Value>
</Constant>
</Operator>',
@is_name_condition=2,
@obj_name=N'usp_%',
@condition_id=@condition_id OUTPUT

--Putting the database list restriction
DECLARE @condition_id INT
EXEC msdb.dbo.sp_syspolicy_add_condition
@name=N'DbList',
@description=N'',
@facet=N'Database',
@expression=N'<Operator>
<TypeClass>Bool</TypeClass>
<OpType>IN</OpType>
<Count>2</Count>
<Attribute>
<TypeClass>String</TypeClass>
<Name>Name</Name>
</Attribute>
<Function>
<TypeClass>Array</TypeClass>
<FunctionType>Array</FunctionType>
<ReturnType>Array</ReturnType>
<Count>2</Count>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>Exact_Help</Value>
</Constant>
<Constant>
<TypeClass>String</TypeClass>
<ObjType>System.String</ObjType>
<Value>Employee</Value>
</Constant>
</Function>
</Operator>',
@is_name_condition=0,
@obj_name=N'',