Database

Count number of words in a MySQL column

This is a general requirement to count number of words in a column of a MySQL database table. But MySQL does not have any built in function to count number of words as it generally has for number of purposes.

But you can do it with a simple trick e.g. you … Read More

Bind multiple rows value in a single row

Following example demonstrate that how you can bind multiple rows value in a single row. Through this query you got comma (,) separated value in a single row.   
 
 
Example:-
 
declare @a varchar(500)
set @a = ‘ ‘
select @a = @a + cast(id as varchar(20)) + ‘,’ from dbo.TR_UserBasic
if len(@a)>1
set @a= left(@a,len(@a)-1)
select case … Read More

How execute store procedure with C#.net

Create store procedure in sql query analyser

CREATE PROCEDURE sp_chkadmin
@login_id varchar(50),
@password varchar(50)
as begin
select * from where login_id = @login_id and password =@password
end

GO

protected void butlogin_ServerClick1(object sender, EventArgs e)
{

string username = txtlogin.Value.ToString().Replace(“‘”, “””);
string password = txtpassword.Value.ToString().Replace(“‘”, “””);
int isUserExist=0;
isUserExist = IsUserExist(username, password);
if (isUserExist == 1)
{
Session["admin"] = username;
Response.Redirect(“next.aspx”);
}
else
{
lblhead.Text = “Please Enter Correct Username or Password … Read More

How use trigger in sql

Example of Delete trigger

CREATE trigger [TRIGGER_Deletesubcategory] ON dbo.category

FOR delete

AS

declare @nmid int

select @nmid=sno from deleted

delete from subcategory where cat_id= @nmid

There will be two table one is category and second one is subcategory and the primery key of category table will be sno and the this will foreign key(cat_id) for subcategory table.

Return Multi Row value in a single column with comma seprated

Return Multi Row value in a single column with comma seprated using sql
I want Return output like 1,2,3,4,5 using sql.
There is one table in which has column
id name
1 Dilip
2 Dinesh
3 Varun
4 Dhanpat
5 Renuka

solution:
declare @a varchar(500)
set @a = ‘ ‘
select @a = @a + cast(id as varchar(20)) + ‘,’ from tablename
if … Read More