How to Create View..

Hi,

Frendzzz

--How to Merge Two Table Concurrenlty, and this data Stored in given Views..
--1st create tow table & insert the data given as the following..
--After tat Create as View as Criteria..

create table FRUITS(
Number int identity(1,1),
Name varchar(25),
Rate numeric(10,2),
Data varchar(50)
);

insert into FRUITS values('Apple Fiesta','125.48','Contains FRUIT PRODUCE')

insert into FRUITS values('Celeriac','58.48','Contains FRUIT AND VEGETABLE PRODUCE')
insert into FRUITS values('Carrots Small','458.26','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Exotic Casava','986.25','Contains FRUIT PRODUCE')
insert into FRUITS values('Herbs Lovage','1025.32','Contains FRUIT AND VEGETABLE PRODUCE')
insert into FRUITS values('Oranges Med','362.25','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Apple Fiesta','256.38','Contains FRUIT PRODUCE')
insert into FRUITS values('Pears Furel','569.86','Contains FRUIT PRODUCE')
insert into FRUITS values('Tomato Plum','45.26','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Turnips','563.18','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Oranges Med','68.59','Contains FRUIT AND VEGETABLE PRODUCE')

update Fruits set Data='Flora India.com' where Data='Contains FRUIT AND VEGETABLE PRODUCE'
update Fruits set Data='Dry Scenario' where Data='Contains FRUIT PRODUCE'

select * from FRUITS

Output :-

create table DRYFRUITS(
Number int identity(1,1),
Name varchar(25),
Rate numeric(10,2),
Data varchar(50)
);

insert into DRYFRUITS values('Assorted Mini Dryfruits','630','Flora India.com')

insert into DRYFRUITS values('Assorted Mini Dryfruits','630','Flora India.com')
insert into DRYFRUITS values('1 Kg Almonds','1150','Flora India.com')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','725','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Almonds','575','Flora India.com')
insert into DRYFRUITS values('Roasted Temptation','1250','Flora India.com')
insert into DRYFRUITS values('Unsatiable Desire','1900','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Pistachio','675','Flora India.com')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','850','Dry Scenario')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','650','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Almonds','1025','Dry Scenario')

select * from DRYFRUITS

Output:

-- How to create view ???
-- Syntax of View Click the following Url..
-- http://msdn.microsoft.com/en-us/library/aa258253(v=sql.80).aspx

Create View View_MergeFruits
As
SELECT     TOP (100) PERCENT dbo.DRYFRUITS.Name AS DryFruits, dbo.DRYFRUITS.Rate AS DryFruits_Rate, dbo.FRUITS.Name AS Fruits,
                      dbo.FRUITS.Rate AS Fruits_Rate, dbo.DRYFRUITS.Data
FROM         dbo.DRYFRUITS INNER JOIN
                      dbo.FRUITS ON dbo.DRYFRUITS.Data = dbo.FRUITS.Data
ORDER BY DryFruits, Fruits

--In that view <View_MergeFruits> all data are Secured Stored...
select * from View_MergeFruits

Output:

This is View Data..Stored Properly in View_MergeFruits

Parameterized Stored Procedure

hi frends..

1st create table <_Color> use following Query


create table _Color(
colorid int identity(1,1),
color varchar(10),
colorcodes varchar(7),
name varchar(20)
)

--drop table _Color

insert into _Color values('Yellow','#FFFF00','tshirt')
insert into _Color values('LightBlue','#0000FF','cell')
insert into _Color values('Brown','#804000','pen')
insert into _Color values('Yellow','#FFFF00','waterlemon')
insert into _Color values('LightBlue','#0000FF','ball')
insert into _Color values('Red','#FF0000','door')
insert into _Color values('Brown','#804000','Mobile')
insert into _Color values('Crimson','DC143C','watch')
insert into _Color values('Red','#FF0000','nip')
insert into _Color values('Yellow','#FFFF00','flowers')
insert into _Color values('LightBlue','#0000FF','chair')
insert into _Color values('Brown','#804000','eyes')

select * from _Color

Output :

Procedure Start Here..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks,
-- and literals must be delimited by single quotation marks.
-- When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by
-- single or double quotation marks. If a literal string is delimited by double quotation
-- marks, the string can contain embedded single quotation marks, such as apostrophes.
-- =============================================

Alter PROCEDURE [dbo].[Parametric_Procedure](
@color as varchar(max)
)
with recompile
AS
DECLARE
@strsql nvarchar(max)
BEGIN
SET NOCOUNT ON;

set @strsql = N'select * from _Color '
set @strsql = @strsql + "where color = '" +@color+ "'"
print @strsql

exec sp_executeSql @strSql
END


OUTPUT :

How to write Stored Procedure in SQL SERVER

I will try to write store procedure in sql.It is for begineers purpose.

To begin write away to the procedure you will 1st write CREATE PROCEDURE <PROCEDURE_NAME>

--Here i will try to display simple example of Parametric procedure (SQL SERVER 2005)


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE procedure_first
-- Add the parameters for the stored procedure here
(
@firstname as varchar(max),
@lastname as varchar(max)
)
with recompile
AS
DECLARE
@strsql nvarchar(max)
BEGIN
SET NOCOUNT ON;

set @strsql = N'select * from ex '
set @strsql = @strsql + "where firstname = '" +@firstname+ "' and lastname = '" +@lastname+ "'"

       print @strsql

exec sp_executeSql @strSql
END
GO

--------------------------------------------------

How to Execute?
Fire Following Command.

EXEC dbo.procedure_first 'Sagar','Awale'

Output :

and Message also displayed (plz check next image..)

Subqueries in CASE and BETWEEN Statements in SQL SERVER 2008

Hi Frendz...
Check the Following Queries...
-------------------------------------------------------------
First Creating 2 tables..


CREATE TABLE T1 (A INT, B1 INT, B2 INT)
CREATE TABLE T2 (A INT, B INT)

Insert Values In T1 Table..


insert into T1 values(11,65,55)
insert into T1 values(45,45,6)
insert into T1 values(14,9,98)
insert into T1 values(45,24,31)
insert into T1 values(133,5,4)

SELECT * FROM T1

------------------------------------------------------------
Insert Values In T2 Table..


insert into T2 values(11,55)
insert into T2 values(45,6)
insert into T2 values(14,9)
insert into T2 values(24,31)
insert into T2 values(133,4)

SELECT * FROM T2

-----------------------------------------------------

• Subquery Using BETWEEN Statement

SELECT *

FROM T1

WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2

SELECT *

FROM T1

WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) >= T1.B1 AND

    (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) <= T1.B2

SELECT Q.A, Q.B1, Q.B2

FROM

    (

    SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B

    FROM T1

    ) Q

WHERE SUM_B BETWEEN Q.B1 AND Q.B2

SELECT T1.*

FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q

WHERE Q.SUM_B BETWEEN T1.B1 AND T1.B2

SELECT T1.*

FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q

WHERE T1.A = Q.A AND Q.SUM_B BETWEEN T1.B1 AND T1.B2

All Queries has Same Following Output..

----------------------------------------------------------------

• Subquery Using CASE Statement

SELECT *,

    CASE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A)

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1

SELECT Q.A, Q.B1, Q.B2,

    CASE Q.SUM_B

        WHEN Q.B1 THEN 'B1'

        WHEN Q.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM

    (

    SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B

    FROM T1

    ) Q

SELECT T1.*,

    CASE Q.SUM_B

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q

Output :

----------------------------------------------------

SELECT T1.*,

    CASE Q.SUM_B

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q

WHERE T1.A = Q.A

------------------------------------------

Sub-queries in CASE and BETWEEN Statements in SQL SERVER 2008

Convert Varchar to Numeric in SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER Off
GO
alter FUNCTION [dbo].[Return_Example]
(

@a numeric(18,4),
@b numeric(18,4)
)
RETURNS numeric(18,3)
AS
BEGIN
declare
@division int,
@remainder int,
@Num2 varchar(100),
@c numeric(18,4)
set @division=floor(@a/@b)
set @remainder=@a%@b
--Here output will be a concatenated
set @num2 = cast(@division as varchar) + '.'+ cast(@remainder as varchar)
set @c=cast(@Num2 as numeric(18,3))
RETURN @c
END

-------------- Run As ---------

declare @a as varchar(100)
set @a = dbo.Return_Example(59,18)
print @a



OUTPUT :
3.500