sql asp

Can you please show your exec statement?

>WHERE     (tbltransactions.transactionid IN (@transactionstring))
with this as parameter
@transactionstring nvarchar(255)

will not return the expected results.
you will need to change the WHERE into this:
>WHERE     (tbltransactions.transactionid IN (SELECT value FROM dbo.ParmsToList(@transactionstring)))

after having added the following function:

CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          ) 
     DECLARE @Value varchar(30), @Pos int 
     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1) 
     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1) 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  

Select allOpen in new window

now, can you show the code that runs this procedure, anyhow? you will need a quote around it:
exec up_TransmitConfirmation '16,17,19,20,25,30,32,33,34'

See: https://www.experts-exchange.com/questions/21658716/Pass-Variable-with-multiple-values-separated-by-columns-to-stored-procedure.html
Please change your WHERE statement as shown after the function in the code box below
This will work if you're using SQL Server 2000/2005/2008

CREATE   function F_QuickTable_From_List
      ( @StringList varchar(8000),@Delimiter varchar(10)=',' )
RETURNS @table_var TABLE
      (       Position int identity(1,1) ,       ListItem varchar(8000)      )
with schemabinding
AS
BEGIN
      declare @TempString varchar(8000) ; set @TempString = @StringList
      declare @commaposition smallint
      declare @v varchar(8000)
      
      while len(@TempString) > 0
            begin
                  set @commaposition = charindex(@Delimiter,@TempString)
                  if @commaposition > 0
                        begin
                              set @v = ltrim(rtrim(left(@TempString,@commaposition-len(@Delimiter))))
                              set @TempString = substring(@TempString,@commaposition+len(@Delimiter),8000)
                        end
                  else
                        begin
                              set @v = ltrim(rtrim(@TempString))
                              set @TempString = ''
                        end
                  INSERT INTO @table_var (ListItem)values (@v)
            END
      return
END
GO
 
WHERE     (tbltransactions.transactionid IN ( select ListItem from dbo.F_QuickTable_From_List(@transactionstring, ',' ) ))

Select allOpen in new window

looks like angel beat me to it with a similar solution

Hello riskyricky1972,

angelIII is right, you have t ouse quotes.

BUT, if you are expecting to pass comma delimited IDs as string into "IN (...)" statement, you are wrong. There are two options:

1) Build a SQL statement dynamically

EXEC ('SELECT     tbltransactions.transactionid, tbltransactions.batchnumber, tbltransactions.partno, tbltransactions.transactionprice, tbltransactions.transactionstatusid,
                      tbltransactions.datecreated, tbltransactions.memberid, tbltransactions.remark, tbltransactions.colorid, tbltransactions.quantity,
                      tbltransactions.manufacturerid, tbltransactions.producttypeid, tbltransactions.transactionid AS Expr1, tblcolor.color,
                      tblmanfacturers.manufacturercompany, tblmembers.firstname, tblmembers.lastname, tblmembers.middlename, tblproducttypes.producttypedesc
FROM         tbltransactions INNER JOIN
                      tblcolor ON tbltransactions.colorid = tblcolor.colorid INNER JOIN
                      tblmanfacturers ON tbltransactions.manufacturerid = tblmanfacturers.manufacturerid INNER JOIN
                      tblmembers ON tbltransactions.memberid = tblmembers.memberid INNER JOIN
                      tblproducttypes ON tbltransactions.producttypeid = tblproducttypes.producttypeid
WHERE     (tbltransactions.transactionid IN (' + @transactionstring + '))')

2) Create a splitter function, for example like this:
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-29-udf_Txt_SplitTAB.htm

Then your SELECT wuold look like this:

      SELECT     tbltransactions.transactionid, tbltransactions.batchnumber, tbltransactions.partno, tbltransactions.transactionprice, tbltransactions.transactionstatusid,
                      tbltransactions.datecreated, tbltransactions.memberid, tbltransactions.remark, tbltransactions.colorid, tbltransactions.quantity,
                      tbltransactions.manufacturerid, tbltransactions.producttypeid, tbltransactions.transactionid AS Expr1, tblcolor.color,
                      tblmanfacturers.manufacturercompany, tblmembers.firstname, tblmembers.lastname, tblmembers.middlename, tblproducttypes.producttypedesc
FROM         tbltransactions INNER JOIN
                      tblcolor ON tbltransactions.colorid = tblcolor.colorid INNER JOIN
                      tblmanfacturers ON tbltransactions.manufacturerid = tblmanfacturers.manufacturerid INNER JOIN
                      tblmembers ON tbltransactions.memberid = tblmembers.memberid INNER JOIN
                      tblproducttypes ON tbltransactions.producttypeid = tblproducttypes.producttypeid
WHERE     (tbltransactions.transactionid IN (SELECT dbo.udf_Txt_SplitTAB(@transactionstring)))





Regards,

Rimvis

I appreciated all of your helps here. And I will review the codes in this weekend for sure.
By the way, I am little confused what you all provided in stored procedure.
Rimvis: can you re-post the revised one you have with completed working code?
Sorry I am new for this sql stuff...