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

Pivot In SQL SERVER

Hi Frendz..

HERE is Final Table #Example & it's Pivot.. 


declare @columnheader varchar(max)

Select @columnheader = COALESCE(@columnheader + ',isnull([' + cast(@FieldName as varchar) + '],0)','isnull([' + cast(@FieldName as varchar)+ '],0)')
FROM #Example

 DECLARE @query VARCHAR(max)
SET @query = 'SELECT * into fianltable FROM #Example
PIVOT
 (
 SUM(Col1)  
 FOR [MergeValue]  IN (' + @columnheader + ')
 )
 AS p order by ' + @columnheader + ''

EXECUTE(@query)


select * from fianltable
drop table fianltable

Display Last 3 Records in Table

--Display Last 3 Records in Table

create table #table
(
no int identity (1,1),
name varchar(20),
addr varchar(100)
)

insert into #table values ('ABC','Pune')
insert into #table values ('XYZ','mumbai')
insert into #table values ('KLM','Satara')
insert into #table values ('OPQR','Hyderabad')
insert into #table values ('JKLM','Sholapur')
insert into #table values ('GHIJ','Pune')
insert into #table values ('RETU','Bombai')

select * from #table

select top 3 * from #table order by no desc

--OUTPUT IS
--
--7 RETU Bombai
--6 GHIJ Pune
--5 JKLM Sholapur

storing the value into a variable within a select query

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
alter PROCEDURE Pr_StoringValue
AS
declare @name varchar(max)
declare @name1 varchar(max)
BEGIN

create table #user(id integer IDENTITY(1,1),
username varchar(max)
)
insert into #user(username) values ('Nitin')
insert into #user(username) values ('Anoop')
insert into #user(username) values ('Sagar')

  select @name = username from #user
  select @name
  update #user set username = 'ABC' where id=3
  select @name1 = username from #user
  select @name1

select * from #user
END
GO

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

Execute Here...

EXEC Pr_StoringValue