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 :