Sep 30, 2010

Stored Procedure(SP) Vs User Defined Functions(UDF) + SQL SERVER

1. Procedure can return zero or n values whereas function can return one value which is mandatory.
2. Procedures can have input,output parameters for it whereas functions can have only input parameters.
3. Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
4. Functions can be called from procedure whereas procedures cannot be called from function.
5. Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6. We can go for transaction management in procedure whereas we can't go in function.
7. Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

Sep 9, 2010

How to specify table name dynamically in a SQL statement + SQL SERVER 2008

Dynamic SQL :
Using a dynamic sql is quite comman in sql statement. I had a situation where
i have to pass the table file as a argument to a Stored Procudure and depending 
upon the argument i have to execute the Select statement.

Say for eg.
argument @Table = dbo.Customers
But this doesnt work.
So when you are in a need to specify any of the objects like table or column etc
we need to use dynamic SQL using exec method. Below is an example

DECLARE @TableName varchar(50)
SET @TableName ='dbo.Customers'
DECLARE @SQL varchar(max)
SET @SQL = 'SELECT * FROM ' +@TableName // dynamic table name

Sep 8, 2010

How to find the intersected rows of a spatial Index using POLYGON object + Spatial Queries

Below is the sample stored procedure used to find the 
rows that are intersected with the POLYGON object
[Created using Coordinates list].

CREATE PROCEDURE [dbo].[GetIntersectingRows] 
-- Add the parameters for the stored procedure here
@Coordinates varchar(max)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

Declare @AOIPolygon geography
set @AOIPolygon = geography::STPolyFromText('POLYGON(('+@Coordinates+'))', 4326);

SELECT ID,Quad_Name,State from dbo.USCollared250K_Index 
WITH (INDEX(geom_sidx)) WHERE
@AOIPolygon.STIntersects(geom) = 1;

@Coordinates - comma separated pairs of coordinates like
eg. -104.864472 39.764004,-104.864472 39.853945,-105.057976 39.853945,
-105.057976 39.764004,-104.864472 39.764004

How to open a pop up window with specified properties[height,width,position and no menubar,no resizable]. + Javascript

Below is the javascript method that opens a popup without scrolbar or menubar or resizable etc

function openQuadPopup(val) {           
            var width = screen.width - 100;
            var height = screen.height - 100;
            var leftPos = (screen.width - width - 30) / 2;
            var topPos = (screen.height - height - 30) / 2;
            myWindow ='TestPage.aspx?ImagePath=' + val, 'NameForPopup', 'menubar=0,resizable=0,width=' + width + ',height=' + height + "'");

            myWindow.moveTo(leftPos, topPos);          

To include scroll bars use'TestPage.aspx', '','scrollbars=yes,width=300,height=300')