validation (Repeation) in SP
CREATE PROCEDURE USP_ValidateMemberNew_suresh
@FirstName NVARCHAR(100),
@LastName NVARCHAR(100),
@DateofBirth DATETIME,
@EMail NVARCHAR(MAX),
-- @AlternateEmail VARCHAR(200),
@Mobile NVARCHAR(40)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Val varchar(50);
set @Val='';
IF EXISTS
(
SELECT 1
FROM Member
WHERE Mobile = @Mobile
)
BEGIN
--SELECT -3 As Msg; -- IF USER WITH MOBILE ALREADY EXISTS.
--SET @Val =@Val + -3;
SET @Val =cast(@Val as varchar(5)) + cast('#' as varchar(2))+ cast('-3' as varchar(2));
END
IF EXISTS
(
SELECT 1
FROM Member
WHERE EMail = @EMail
)
BEGIN
--SELECT -2 As Msg; -- IF USER WITH SAME EMAIL ID EXISTS.[EmailId 1 exist]
--SET @Val =@Val + -2;
if(@Val<>'')
begin
SET @Val =cast(@Val as varchar(10)) + cast('#' as varchar(2))+ cast('-2' as varchar(2));
end
else
begin
SET @Val =cast('#' as varchar(2))+cast(-2 as varchar(2))
end
-- print @Val
END
IF EXISTS
(
SELECT 1
FROM Member
WHERE FirstName = @FirstName And LastName = @LastName
And DateofBirth = @DateofBirth
)
BEGIN
--SELECT -1 As Msg; -- IF USER WITH SAME NAME AND DOB ALREADY EXISTS.
--SET @Val =@Val + -1;
if(@Val<>'')
begin
SET @Val =cast(@Val as varchar(15)) + cast('#' as varchar(2))+ cast('-1' as varchar(2));
end
else
begin
SET @Val =cast('#' as varchar(2))+cast(-1 as varchar(2))
end
END
if(@Val='')
BEGIN
SELECT (cast('#' as varchar(2))+ cast('1' as varchar(5))) As Msg; -- SUCCESS
END
ELSE
BEGIN
select @Val as Msg;
END
SET NOCOUNT OFF;
END
atareader dr.GetOrdinal("name")
Insert Query
Insert Query without Columns name INSERT INTO table_name VALUES (value1, value2, value3,...)
Insert Query with Columns name INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
Update Query
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
Delete Query
DELETE FROM table_name WHERE some_column=some_value
Select Query
SELECT column_name(s) FROM table_name
SELECT * FROM [table_name]
select 1 from [TABLE] where Id='1' --> return 1 if any row present
o/p
1
1
1
| How to select Randomly N Rows in SQL | |
| select top 2 * from SecurityQuestionAnswer order by newid() | ->newid() is a random function |
| And in MySQL rand() | |
| Top in SQL | |
| Limit in MySQL | |
| Checking The null in query | Update Student set FirstName=isnull(@FName,FirstName) where ID='1'; |
| Delete Duplicate Entry in table | delete from CheckEmp where AutoID not in (select min(AutoID) from CheckEmp group by Names) |
| Look for more | |
| http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table | |
| AutoIncrement by taking 1st 2 character of a name |
declare @vara varchar(10) print @vara
O/P 001 |
| For Sr.No along with O/P |
Select Row_Number() over (order by MemberID) as Srno,* from Member SELECT FName, UId, (SELECT COUNT(*) FROM Kpi_Users i WHERE o.UId >= i.UId) row_num |
| cast(<DataField Name> as <DataType) | |
|
emp table with |
SELECT ID, COUNT(*) CountofProductID FROM emp WHERE ID BETWEEN 5 and 10 GROUP BY ID ORDER BY CASE WHEN ID = 10 THEN 1 WHEN id =7 THEN 2 ELSE 0 END |
| For getting identity column value | select IDENT_CURRENT ('xusers') |
Creating Temp table in sql
create table #DelTbl
(
PPID int Identity(1,1)
,ProjName varchar(MAX)
)
declare @pcid int
declare @cid cursor
set @cid = cursor for
select Pro_Id from Kpi_ProjectDetail
open @cid
fetch next
from @cid into @pcid
while @@FETCH_STATUS=0
BEGIn
insert into #DelTbl
select Pro_Id from Kpi_ProjectDetail where Pro_Id=@pcid
fetch next from @cid into @pcid
END
select * from #DelTbl
drop table #DelTbl
Update record of one table with other table data
UPDATE product_option_value JOIN option_value
product_option_value.option_value_id = option_value.option_value_id
SET product_option_value.image= option_value.image
