Multivaled stored procedures

select * from CrmLead
go

declare @country varchar(100)
select
crmlead_id,Display_Name,Company,Lead_Source
,ISNULL(Address1,'')+' '+ISNULL(Address2,'')as address,city,state,Country
from CrmLead
where Country=@country

go
---padding zero's
select RIGHT('0000'+ CONVERT(varchar,crmlead_id),10) from CrmLead
go
-----------------------------------
go
use reddeppa_test
select * from CrmLead
GO
---------------------------------------------
/************************************************************
EXEC USP_CRMLEAD '1,2,3'
PASSING THE MULTIVALUED PARAMETER RECORDS
***************************************************************/
ALTER PROCEDURE USP_CRMLEAD (@CRMLEAD_ID VARCHAR(100))
AS
BEGIN
DECLARE @PMAINQUERY NVARCHAR(MAX)
DECLARE @PWHERECONDITION NVARCHAR(MAX)
SET @PMAINQUERY='SELECT '+'crmlead_id,Display_Name,Company,Lead_Source
,ISNULL(Address1,'''')+''''+ISNULL(Address2,'''')as address,city,state,Country
from CrmLead'
SET @PWHERECONDITION=' WHERE CRMLEAD_ID IN ('+ @CRMLEAD_ID +')'
SET @PMAINQUERY=@PMAINQUERY+''+@PWHERECONDITION
PRINT @PMAINQUERY
EXEC SP_EXECUTESQL @PMAINQUERY
END
GO
/********************************************************************
SELECT * FROM CRMLEAD
EXEC USP_CRMLEADVARCHARTYPE 'Web,Purchased List'
*****************************************************************/
CREATE PROCEDURE USP_CRMLEADVARCHARTYPE(@LEADSOURCE NVARCHAR(200))
AS
BEGIN
DECLARE @PMAINQUERY NVARCHAR(MAX)
DECLARE @PWHERECONDITION NVARCHAR(MAX)
---FIND THE LENGHT OF THE LEADSOURCE
IF( LEN(@LEADSOURCE) >0 AND CHARINDEX(@LEADSOURCE, ',')=1)
BEGIN
SET @LEADSOURCE = '@LEADSOURCE'
END
ELSE
BEGIN
IF(LEN(@LEADSOURCE) >0 )
BEGIN
SET @LEADSOURCE = '''' + REPLACE(@LEADSOURCE, ',', ''',''') + ''''
END
ELSE
BEGIN
SET @LEADSOURCE = 'c'
END
END

SET @PMAINQUERY='SELECT '+'crmlead_id,Display_Name,Company,Lead_Source
,ISNULL(Address1,'''')+''''+ISNULL(Address2,'''')as address,city,state,Country
from CrmLead'
SET @PWHERECONDITION='WHERE LEAD_SOURCE IN ('+@LEADSOURCE+')'
SET @PMAINQUERY=@PMAINQUERY+' '+@PWHERECONDITION
PRINT @PMAINQUERY
EXEC SP_EXECUTESQL @PMAINQUERY
END