stored-procedures - procedimientos - recorrer array sql server
Cómo pasar el parámetro de cadena con el operador `IN` en el procedimiento almacenado SQL Server 2008 (4)
Así es como lo resolví: Working SQL Fiddle
Primero tengo crear una función que divide el valor de la cadena, es decir, ''1,2,4,5''
Función dividida :
CREATE FUNCTION fn_Split(@text varchar(8000), @delimiter varchar(20) = '' '')
RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
Más tarde en mi consulta uso esa función dividida
declare @startdate1 varchar(100) =''20120201''
declare @enddate1 varchar(100)=''20130601''
declare @dptId varchar(100)=''3,4''
select * from dummy
where DateJoining >=@startdate1 and DateJoining < @enddate1
and departmentID IN (SELECT Value FROM fn_Split(@dptId, '',''));
Tengo un procedimiento almacenado cuando lo ejecuto Obtuve un error
La conversión falló al convertir el valor varchar ''+ @ dptId +'' a tipo de datos int
Recibo DepartmentId
como una cadena de caracteres (1,3,5,77)
y paso esto a mi procedimiento almacenado.
create table dummy (id int,name varchar(100),DateJoining Datetime, departmentIt int)
insert into dummy values (1,''John'',''2012-06-01 09:55:57.257'',1);
insert into dummy values(2,''Amit'',''2013-06-01 09:55:57.257'',2);
insert into dummy values(3,''Naval'',''2012-05-01 09:55:57.257'',3);
insert into dummy values(4,''Pamela'',''2012-06-01 09:55:57.257'',4);
insert into dummy values(5,''Andrea'',''2012-09-01 09:55:57.257'',3);
insert into dummy values(6,''Vicky'',''2012-04-01 09:55:57.257'',4);
insert into dummy values(7,''Billa'',''2012-02-01 09:55:57.257'',4);
insert into dummy values(8,''Reza'',''2012-04-01 09:55:57.257'',3);
insert into dummy values (9,''Jacob'',''2011-05-01 09:55:57.257'',5);
Query intenté:
declare @startdate1 varchar(100) =''20120201''
declare @enddate1 varchar(100)=''20130601''
declare @dptId varchar(100)=''3,4''
select *
from dummy
where DateJoining >= @startdate1 and DateJoining < @enddate1
and departmentIt IN (@dptId);
Intenta usar sp_executesql
como la respuesta. No es el más eficiente, pero funciona
ALTER PROCEDURE [dbo].[uspTestReportData_GetBySerial]
@SerialNumbers nvarchar(200)
AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(200)
set @sql = ''SELECT * from MyTable WHERE Serial_Number in ('' + @SerialNumbers + '')''
execute sp_executesql @sql
END
ALTER PROCEDURE dbo.sp_Custom_Select_ClientVisit
(
@ClientVisitId int = Null,
@ClientId int = Null,
@PersonId int = Null,
@ProductId int = Null,
@VisitDateFrom datetime = Null,
@VisitDateTo datetime = Null,
@eVisitStatusIn varchar(100) = Null,
@eVisitStatus int = Null,
@eStatus int = Null,
@eStatusNot int = Null
)
AS
create table #IDs
(
Id int
)
Declare @delimiter varchar
Set @delimiter = '',''
DECLARE @index int
SET @index = -1
WHILE (LEN(@eVisitStatusIn) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @eVisitStatusIn)
IF (@index = 0) AND (LEN(@eVisitStatusIn) > 0)
BEGIN
INSERT INTO #IDs VALUES (@eVisitStatusIn)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO #IDs VALUES (LEFT(@eVisitStatusIn, @index - 1))
SET @eVisitStatusIn = RIGHT(@eVisitStatusIn, (LEN(@eVisitStatusIn) - @index))
END
ELSE
SET @eVisitStatusIn = RIGHT(@eVisitStatusIn, (LEN(@eVisitStatusIn) - @index))
END
Select
ClientVisit.ClientVisitId, ClientVisit.eStatus,
ClientVisit.VisitTime, ClientVisit.VisitReason,
ClientVisit.eVisitStatus, ClientVisit.VisitSummary,
Client.ClientId, Client.InstituteName,
Client.PersonName as ClientPersonName, Client.eStatus as ClienteStatus,
Person.PersonId, Person.FirstName as ExecutiveFirstName, Person.LastName as ExecutiveLastName,
Person.FirstName + '' '' + Person.LastName as ExecutiveName,
p.ProductId, p.ParentProductId,
p.ProductName, p.Description as ProductDescription,
p.eStatus ProducteStatus,
Case When ClientVisit.eVisitStatus = 1 Then ''Pending''
When ClientVisit.eVisitStatus = 2 Then ''Completed''
When ClientVisit.eVisitStatus = 3 Then ''Cancelled'' End As VisitStatus,
Case When ClientVisit.eStatus = 1 Then ''Active''
When ClientVisit.eStatus = 2 Then ''Deactive''
When ClientVisit.eStatus = 3 Then ''Deleted'' End As Status
From AC_ClientVisit as ClientVisit
INNER Join Com_Client Client On Client.ClientId = ClientVisit.ClientId
INNER Join Com_Person Person On Person.PersonId = ClientVisit.ExecutiveId
INNER Join Com_Product p On p.ProductId = Client.RootProductId
Where
(@ClientVisitId IS NULL OR ClientVisit.ClientVisitId = @ClientVisitId)
AND (@ClientId IS NULL OR Client.ClientId = @ClientId)
AND (@PersonId IS NULL OR Person.PersonId = @PersonId)
AND (@ProductId IS NULL OR p.ProductId = @ProductId)
AND (@VisitDateFrom IS NULL OR @VisitDateFrom <= ClientVisit.VisitTime)
AND (@VisitDateTo IS NULL OR @VisitDateTo >= ClientVisit.VisitTime)
AND (@eVisitStatusIn IS NULL OR ClientVisit.eVisitStatus IN(SELECT i.Id FROM #IDs AS i))
AND (@eVisitStatus IS NULL OR ClientVisit.eVisitStatus = @eVisitStatus)
AND (@eStatus IS NULL OR ClientVisit.eStatus = @eStatus)
AND (@eStatusNot IS NULL OR ClientVisit.eStatus <> @eStatusNot)
RETURN
Simplemente, puede hacer lo siguiente SELECT
:
SELECT M.REG_NO, T.TYPE_ID
FROM MAIN AS M
INNER JOIN CLASSIFICATION AS C
ON M.REG_NO = C.REG_NO
INNER JOIN TYPE AS T
ON T.TYPE_ID = C.TYPE_ID
WHERE ('',''+@Types+'','') LIKE ''%,'' +T.TYPE_ID+ '',%''