Click here to Skip to main content
15,911,306 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I need to show data with same parameter suppose some ID. it will return data in multiple tables as ID has different values.

What I have tried:

I tried to search on Google there is something tvp and comma separated methods.
But i unable to implement that as i am new to that concept.
Posted
Updated 7-Jan-19 22:14pm
Comments
CHill60 7-Jan-19 6:34am    
Your question is not at all clear. Show some sample data and expected results. Also post the code you have already tried. Avoid use of comma separated variables/columns.

Take a look at the IN keyword in SQL
https://www.w3schools.com/sql/sql_in.asp
 
Share this answer
 
First thing i make fun in sql:

USE [ZAB_HCTMS]
GO
/****** Object:  UserDefinedFunction [dbo].[LCNos]    Script Date: 1/8/2019 1:09:54 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  UserDefinedTableType [dbo].[oldTVP_ConsignmentNoteLineItems]    Script Date: 1/7/2019 1:00:53 PM ******/

ALTER FUNCTION [dbo].[LCNos]
(    
      @LCNo NVARCHAR(MAX),
      @Comma CHAR(1)
)
RETURNS @Output TABLE (
      ID NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@LCNo, LEN(@LCNo) - 1, LEN(@LCNo)) <> @Comma
      BEGIN
            SET @LCNo = @LCNo + @Comma
      END

      WHILE CHARINDEX(@Comma, @LCNo) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Comma, @LCNo)

            INSERT INTO @Output(ID)
            SELECT SUBSTRING(@LCNo, @StartIndex, @EndIndex - 1)

            SET @LCNo = SUBSTRING(@LCNo, @EndIndex + 1, LEN(@LCNo))
      END

      RETURN
END
then sp for this:

-- EXEC GetLCsDetails '2188,2196,2201'

alter PROCEDURE GetLCsDetails
      @LCNos VARCHAR(100)
AS
BEGIN
      SELECT lr.*, lr.PayBalance 'TotalPayBalance', s.Name 'SuppName', 
      cb.AdvancePayment, s.[Address] 'Address', 
      s.PinCode, pod.SupplierBillNo

      FROM LorryChallans lr  

      left join ConsignmentBookings cb on lr.ConsignmentBookingID = 
      cb.ConsignmentBookingID
      left join Suppliers s on cb.VehicleSupplierID = s.SupplierID
      left join POD pod on lr.ConsignmentBookingID = pod.ConsignmentBookingID

      WHERE 

      lr.LorryChallanNumber       
      IN( SELECT CAST(ID AS INTEGER) FROM dbo.LCNos(@LCNos, ',') )

END
and finally passing string comma separated ids from jquery:

    function chk()
    {
        $('.Checkbox:checked').map(function () {
            return $(this).attr('data-lcno');
        }).get().join(',')
    }      
data-lcno is data attribute to checkboxlist.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900