Simple Cursor Programme

Hi all,
Here is a simple sample for cursor.

declare testCur CURSOR For //Cursor declaring for

select top 10 * from tblUsers

Open testCur //Open the cursor

Fetch next from testCur //Fetch the first record

while @@FETCH_STATUS = 0

Fetch next from testCur //Fetch the next record while status = 0

CLOSE testCur //Close cursor

deallocate testCur //Deallocate the cursor.

...S.VinothkumaR.

Grant permission to all stored procedures.

Hi all,

I am continuing my posts here for a long gape...

Here is a query for granting permission to all stored procedures in a database. Check it out...


create PROCEDURE stpGrantPermissionToAllSP @user sysname
AS
SET NOCOUNT ON
-- 1 - Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)

-- 2 - Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)

-- 3 - Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT u.[Name], o.[Name]
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'P'
AND o.[Name] NOT LIKE 'dt_%'

-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop
WHILE @MAXOID > 0
BEGIN

-- 6 - Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID

-- 7 - Build the string
SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

-- 8 - Execute the string
-- SELECT @CMD1

-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END

-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
GO
exec stpGrantPermissionToAllSP 'username'

...S.VinothkumaR.

DataTable,DataRow,DataView sorting.

Hi all,

Just check the data sorting by using dataview in below.


SqlConnection sql=new SqlConnection("Connection String");
SqlCommand cmd=new SqlCommand("SampleProcedure",sql);
cmd.CommandType=CommandType.StoredProcedure;
SqlDataAdapter da=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds);
if(ds.Tables[0].Rows.Count>0)
{
DataTable dt=new DataTable();
dt.Columns.Add("Score",typeof(double));
dt.Columns.Add("Name",typeof(string));
DataRow dr;
foreach(DataRow drow in ds.Tables[0].Rows)
{
dr = dt.NewRow();
dr["Score"] = drow ["intScore"];
dr["Name"]=drow ["strGameName"];
dt.Rows.Add(dr);
}
DataView dv=dt.DefaultView;
dv.Sort="Score desc";
DataGrid drg=new DataGrid();
drg.DataSource=dv; //drg is DataGrid
drg.DataBind();
this.Controls.Add(drg);
}

...S.VinothkumaR

Double Formatting.

Here is sample for double formatting by using C#.

Double dblScore = 2.0;
string str = dblScore.ToString("#,##0.0;(#,##0.0);00.0");

...S.Vinothkumar.

Check the valid Email Address using JavaScript

Hi all,

Use the following code for check the valid email address.

var filterEmail = /^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z])+\.)+([a-zA-Z]{2,4})+$/;
if(filterEmail.test(Email))//Check the Email Format.
{
//..........
}

...S.VinothkumaR.

Check the phone number for valid using JavaScript

Hi all,

Use the following code for checking the valid phone number.

var filter = /^([0-9])+$/;
if (filter.test(textValue))
{
// ...Code here
}

...S.VinothkumaR.

Checking the Name for valid Characters in JavaScript

Hi all,

Use the following code for check the valid characters in name field.

var filter = /^([a-zA-Z',.\0-9 ])+$/;
if (filter.test(textValue))
{
// ...Code here
}

...S.VinothkumaR.