recorrer procedimientos procedimiento pasar parametros array almacenados almacenado stored-procedures sql-server-2008-r2 parameterized

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.

FIDDLE SQL

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+ '',%''