coalsce not working

mflancour · May 23, 2006

I'm receiving the following error when I attempt to run the query; Implicit conversion of varchar value to varchar cannot be performed because teh collation of the value is unresolved due to a collation conflict.

I've checked the tables and the field types are identical..not sure if that makes a difference or not.

SQLDenis · May 23, 2006

Your collations are not the same
change table1 and table2 to the real table names in the where clause

Code:

SELECT TABLE_NAME,COLUMN_NAME,COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('Table1','Table2')
ORDER BY TABLE_NAME,ORDINAL_POSITION

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

philhege · May 23, 2006

What are the collations on the fields involved? Sounds like they're out of sync.

Show us your statement and the DDL of pertinent fields.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

mflancour · May 23, 2006

SELECT COALESCE (dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode, db

rder_Line_Invoice_Grouped2006.StockCode) AS Expr1,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue + db

rder_Line_Invoice_Grouped2006.NetSalesValue AS Expr5,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced + db

rder_Line_Invoice_Grouped2006.QtyInvoiced AS Expr6,
db

rder_Line_Invoice_Grouped2006.QtyInvoiced, db

rder_Line_Invoice_Grouped2006.NetSalesValue,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue AS Expr7,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced AS Expr8
FROM dbo.ARTRNDETAILGroupedByCustStcdYearMonth FULL OUTER JOIN
db

rder_Line_Invoice_Grouped2006 ON
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode = db

rder_Line_Invoice_Grouped2006.StockCode AND
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.Customer = db

rder_Line_Invoice_Grouped2006.Customer AND
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Month] = db

rder_Line_Invoice_Grouped2006.[Month] AND
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Year] = db

rder_Line_Invoice_Grouped2006.[Year] AND
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced = db

rder_Line_Invoice_Grouped2006.QtyInvoiced AND
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue = db

rder_Line_Invoice_Grouped2006.NetSalesValue

mflancour · May 23, 2006

I can't seem to union the two queries together either.

SQLDenis · May 23, 2006

SELECT TABLE_NAME,COLUMN_NAME,COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('ARTRNDETAILGroupedByCustStcdYearMonth','Order_Line_Invoice_Grouped2006')
ORDER BY TABLE_NAME,ORDINAL_POSITION

then look at these columns
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode , db

rder_Line_Invoice_Grouped2006.StockCode ,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.Customer , db

rder_Line_Invoice_Grouped2006.Customer ,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Month] ,db

rder_Line_Invoice_Grouped2006.[Month] ,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Year] , db

rder_Line_Invoice_Grouped2006.[Year] ,
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced , db

rder_Line_Invoice_Grouped2006.QtyInvoiced

dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue = db

rder_Line_Invoice_Grouped2006.NetSalesValue

Is the collation the same

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

mflancour · May 23, 2006

Wow, way out of my comfort zone.

Well here goes:

dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode,
SQL_Latin1_General_CP1_CI_AS

db

rder_Line_Invoice_Grouped2006.StockCode ,
SQL_Latin1_General_CP850_CI_AS
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.Customer ,
SQL_Latin1_General_CP1_CI_AS

db

rder_Line_Invoice_Grouped2006.Customer ,
SQL_Latin1_General_CP850_CI_AS
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Month] ,
Null

db

rder_Line_Invoice_Grouped2006.[Month] ,
Null
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Year] ,
Null

db

rder_Line_Invoice_Grouped2006.[Year] ,
Null
dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced ,
Null

db

rder_Line_Invoice_Grouped2006.QtyInvoiced
Null

mflancour · May 23, 2006

So, what does all that meen?

SQLDenis · May 23, 2006

what happens now??

Code:

SELECT     COALESCE (dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode, dbo.Order_Line_Invoice_Grouped2006.StockCode) AS Expr1, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue + dbo.Order_Line_Invoice_Grouped2006.NetSalesValue AS Expr5, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced + dbo.Order_Line_Invoice_Grouped2006.QtyInvoiced AS Expr6, 
                      dbo.Order_Line_Invoice_Grouped2006.QtyInvoiced, dbo.Order_Line_Invoice_Grouped2006.NetSalesValue, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue AS Expr7, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced AS Expr8
FROM         dbo.ARTRNDETAILGroupedByCustStcdYearMonth FULL OUTER JOIN
                      dbo.Order_Line_Invoice_Grouped2006 ON 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode = dbo.Order_Line_Invoice_Grouped2006.StockCode COLLATE  SQL_Latin1_General_CP1_CI_AS AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.Customer = dbo.Order_Line_Invoice_Grouped2006.Customer COLLATE  SQL_Latin1_General_CP1_CI_AS AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Month] = dbo.Order_Line_Invoice_Grouped2006.[Month] AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Year] = dbo.Order_Line_Invoice_Grouped2006.[Year] AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced = dbo.Order_Line_Invoice_Grouped2006.QtyInvoiced AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue = dbo.Order_Line_Invoice_Grouped2006.NetSalesValue

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

mflancour · May 23, 2006

same error

SQLDenis · May 23, 2006

Looks like they are incompatible

IF (SELECT 'hi there' COLLATE SQL_Latin1_General_CP1_CI_AS)
= (SELECT 'Hi There' COLLATE SQL_Latin1_General_CP850_CI_AS)
PRINT 'Strings are equal'
ELSE
PRINT 'Strings are unequal'

this will throw this error
Server: Msg 449, Level 16, State 1, Line 1
Collation conflict caused by collate clauses with different collation 'SQL_Latin1_General_CP1_CI_AS' and 'SQL_Latin1_General_CP850_CI_AS'.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

mflancour · May 23, 2006

I dont understand what the problem is...can you elaborate in lamens terms for me?

SQLDenis · May 23, 2006

okay try this, i just did a test on my PC

Code:

SELECT     COALESCE (dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode, dbo.Order_Line_Invoice_Grouped2006.StockCode) AS Expr1, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue + dbo.Order_Line_Invoice_Grouped2006.NetSalesValue AS Expr5, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced + dbo.Order_Line_Invoice_Grouped2006.QtyInvoiced AS Expr6, 
                      dbo.Order_Line_Invoice_Grouped2006.QtyInvoiced, dbo.Order_Line_Invoice_Grouped2006.NetSalesValue, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue AS Expr7, 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced AS Expr8
FROM         dbo.ARTRNDETAILGroupedByCustStcdYearMonth FULL OUTER JOIN
                      dbo.Order_Line_Invoice_Grouped2006 ON 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.StockCode COLLATE  SQL_Latin1_General_CP1_CI_AS = dbo.Order_Line_Invoice_Grouped2006.StockCode COLLATE  SQL_Latin1_General_CP1_CI_AS AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.Customer COLLATE  SQL_Latin1_General_CP1_CI_AS = dbo.Order_Line_Invoice_Grouped2006.Customer COLLATE  SQL_Latin1_General_CP1_CI_AS AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Month] = dbo.Order_Line_Invoice_Grouped2006.[Month] AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.[Year] = dbo.Order_Line_Invoice_Grouped2006.[Year] AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.QtyInvoiced = dbo.Order_Line_Invoice_Grouped2006.QtyInvoiced AND 
                      dbo.ARTRNDETAILGroupedByCustStcdYearMonth.NetSalesValue = dbo.Order_Line_Invoice_Grouped2006.NetSalesValue

Not sure If I missed a column

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

mflancour · May 23, 2006

still have the same error

SQLDenis · May 23, 2006

mmmm, works for me
run this and let me know what happens
BTW what version of sql server are you running?

Code:

create table Testcollate (Col1 varchar(20) collate SQL_Latin1_General_CP850_CI_AS,
Col2 varchar(20) collate SQL_Latin1_General_CP1_CI_AS)
insert into Testcollate values('hello','Hello')

--will fail
select * from Testcollate t1 join Testcollate t2 on t1.col1 =t2.col2


--success
select * from Testcollate t1 join Testcollate t2 on t1.col1 =t2.col2 collate SQL_Latin1_General_CP850_CI_AS
--success
select * from Testcollate t1 join Testcollate t2 on t1.col1  collate SQL_Latin1_General_CP1_CI_AS =t2.col2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

mflancour · May 23, 2006

Thank you sooo much for all your help... I'm afraid I will need to creat a work around (create a table with the results of one of the queries) for now as I need to get this report out for tomorrow. If I have time, I'll revisit this and try to figure it out.

SQLBill · May 23, 2006

As SQLDenis said...what version of SQL Server do you have?

Run this and let us know the result:

Code:

SELECT ServerProperty('Edition'),
       ServerProperty('ProductLevel'),
       ServerProperty('ProductVersion')

-SQLBill

Posting advice: FAQ481-4875

mflancour · May 24, 2006

Standard Edition RTM 8.00.194

SQLBill · May 24, 2006

That is the 'out of the box' version. You should be having problems. There are SOOOOOOOO many bugs with 194. Go to Microsoft's web site, search for SQL Server SP3a, download it and apply it NOW.

That might solve your problem.

BTW-you are very vulnerable to the SLAMMER worm that targets SQL Server. SP3a was the patch that protected against it.

-SQLBill

Posting advice: FAQ481-4875

nixie2 · Jun 25, 2006

Hi again Denis, thanks for sending me to this thread.
I think this thread was the one which triggered me to contact you.

The thing is, I know a little about T-SQl, but not that much. So, I've got this stored procedure, someone else made and I tried to modify it.

I originally did this (sorry for the amount of code):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[ListSearchZMVoorzieningen]

@CSVInstellingID varchar(1000) = '',
@InstellingNaam varchar(25) = '',
@PlaatsNaam varchar(25) = '',
@SorteerKolom varchar(25) = '',
@SorteerVolgorde varchar(4) = '',
@NetwerkID int = Null,
@InstellingSoortCatWeergave int = 1

as
begin
set nocount on

IF @NetwerkID = -1
BEGIN
select
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
st.InstellingSoortNaam as InstellingSoort,
bi.BasisIndelingNaam as BasisIndeling,
lo.LocatieBezoekPlaatsnaam as Plaats,
i.InstellingNaam as InstellingNaam,
lo.LocatiehoofdNetwerkid as Netwerkid
from
ZMProduct pr
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)
where
pr.ProductPublicatieStatus = 1
and st.InstellingSoortCategorie = @InstellingSoortCatWeergave
and ((@CSVInstellingID = '') or (st.InstellingSoortID in (Select convert(int,Value) from dbo.Split(@CSVInstellingID,','))))
and ((@InstellingNaam = '') or (i.InstellingNaam like '%' + @InstellingNaam + '%'))
and ((@PlaatsNaam = '') or (lo.LocatieBezoekPlaatsnaam like '%' + @PlaatsNaam + '%'))

order by

case @SorteerVolgorde
when 'DESC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
end
end DESC,
case @SorteerVolgorde
when 'DESC' then
case @SorteerKolom
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end DESC,

case @SorteerVolgorde
when 'ASC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
end
end ASC,
case @SorteerVolgorde
when 'ASC' then
case @SorteerKolom
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end ASC
end
ELSE
BEGIN
select
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
st.InstellingSoortNaam as InstellingSoort,
bi.BasisIndelingNaam as BasisIndeling,
lo.LocatieBezoekPlaatsnaam as Plaats,
i.InstellingNaam as InstellingNaam,
lo.LocatiehoofdNetwerkid as Netwerkid
from
ZMProduct pr
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)

where
pr.ProductPublicatieStatus = 1
and st.InstellingSoortCategorie = @InstellingSoortCatWeergave
and ((@CSVInstellingID = '') or (st.InstellingSoortID in (Select convert(int,Value) from dbo.Split(@CSVInstellingID,','))))
and ((@InstellingNaam = '') or (i.InstellingNaam like '%' + @InstellingNaam + '%'))
and ((@PlaatsNaam = '') or (lo.LocatieBezoekPlaatsnaam like '%' + @PlaatsNaam + '%'))
and ((lo.LocatiehoofdNetwerkid = @NetwerkID) or EXISTS (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = pr.ProductID AND ZMNetwerkPZProduct.NetwerkPZID = @netwerkid))
order by

case @SorteerVolgorde
when 'DESC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
end
end DESC,
case @SorteerVolgorde
when 'DESC' then
case @SorteerKolom
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end DESC,

case @SorteerVolgorde
when 'ASC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
end
end ASC,
case @SorteerVolgorde
when 'ASC' then
case @SorteerKolom
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end ASC
END

end
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Now at the front end of the website, when I try to sort on Plaatsnaam, it would give an error:

"Conversion failed when converting the varchar value 'Breda' to data type int."

So I figured the SP should be:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[ListSearchZMVoorzieningen]

@CSVInstellingID varchar(1000) = '',
@InstellingNaam varchar(25) = '',
@PlaatsNaam varchar(25) = '',
@SorteerKolom varchar(25) = '',
@SorteerVolgorde varchar(4) = '',
@NetwerkID int = Null,
@InstellingSoortCatWeergave int = 1

as
begin
set nocount on

IF @NetwerkID = -1
BEGIN
select
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
st.InstellingSoortNaam as InstellingSoort,
bi.BasisIndelingNaam as BasisIndeling,
lo.LocatieBezoekPlaatsnaam as Plaats,
i.InstellingNaam as InstellingNaam,
lo.LocatiehoofdNetwerkid as Netwerkid
from
ZMProduct pr
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)
where
pr.ProductPublicatieStatus = 1
and st.InstellingSoortCategorie = @InstellingSoortCatWeergave
and ((@CSVInstellingID = '') or (st.InstellingSoortID in (Select convert(int,Value) from dbo.Split(@CSVInstellingID,','))))
and ((@InstellingNaam = '') or (i.InstellingNaam like '%' + @InstellingNaam + '%'))
and ((@PlaatsNaam = '') or (lo.LocatieBezoekPlaatsnaam like '%' + @PlaatsNaam + '%'))

order by

case @SorteerVolgorde
when 'DESC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
end
end DESC,
case @SorteerVolgorde
when 'DESC' then
case @SorteerKolom
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end DESC,

case @SorteerVolgorde
when 'ASC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
end
end ASC,
case @SorteerVolgorde
when 'ASC' then
case @SorteerKolom
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end ASC
end
ELSE
BEGIN
select
pr.ProductID as ProductID,
pd.ProductDetailsAantalPlaatsen as AantalPlaatsen,
pd.ProductDetailsAantalPlaatsenVrij as PlaatsenVrij,
st.InstellingSoortNaam as InstellingSoort,
bi.BasisIndelingNaam as BasisIndeling,
lo.LocatieBezoekPlaatsnaam as Plaats,
i.InstellingNaam as InstellingNaam,
lo.LocatiehoofdNetwerkid as Netwerkid
from
ZMProduct pr
inner join ZMInstellingSoort st on (pr.ProductInstellingSoortID = st.InstellingSoortID)
inner join ZMBasisIndeling bi on (pr.ProductBasisIndelingID = bi.BasisIndelingID)
inner join ZMLocatie lo on (pr.ProductLocatieID = lo.LocatieID)
left join ZMProductDetails pd on (pr.ProductProductDetailsID = pd.ProductDetailsID)
inner join ZMInstelling i on (lo.LocatieInstellingID = i.InstellingID)

where
pr.ProductPublicatieStatus = 1
and st.InstellingSoortCategorie = @InstellingSoortCatWeergave
and ((@CSVInstellingID = '') or (st.InstellingSoortID in (Select convert(int,Value) from dbo.Split(@CSVInstellingID,','))))
and ((@InstellingNaam = '') or (i.InstellingNaam like '%' + @InstellingNaam + '%'))
and ((@PlaatsNaam = '') or (lo.LocatieBezoekPlaatsnaam like '%' + @PlaatsNaam + '%'))
and ((lo.LocatiehoofdNetwerkid = @NetwerkID) or EXISTS (select * from ZMNetwerkPZProduct Where ZMNetwerkPZProduct.ProductID = pr.ProductID AND ZMNetwerkPZProduct.NetwerkPZID = @netwerkid))
order by

case @SorteerVolgorde
when 'DESC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
end
end DESC,
case @SorteerVolgorde
when 'DESC' then
case @SorteerKolom
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end DESC,

case @SorteerVolgorde
when 'ASC' then

case @SorteerKolom
when 'INSTELLING' then i.InstellingNaam
when 'INSTELLINGSOORT' then st.InstellingSoortNaam
when 'PLAATSNAAM' then lo.LocatieBezoekPlaatsnaam
end
end ASC,
case @SorteerVolgorde
when 'ASC' then
case @SorteerKolom
when 'AANTALPLAATSEN' then pd.ProductDetailsAantalPlaatsen
when 'PLAATSENVRIJ'then pd.ProductDetailsAantalPlaatsenVrij
end
end ASC
END

end
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

But then when I try to run this, SQL Server says:

Msg 457, Level 16, State 1, Procedure ListSearchZMVoorzieningen, Line 18
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Line 18 is:

begin

So since my t-sql is no good, can someone please try and explain what this all means....

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

coalsce not working

MIS

Programmer

Programmer

MIS

MIS

Programmer

MIS

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

MIS

MIS

MIS

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor