asked on

Conversion failed when converting the nvarchar value 'One' to data type int.

I have 4 tables
1) test_t1. Columns: t1Id and wId.
2) test_t2. Columns: t2Id and wId.
3) test_w. Columns: wId and Word. A table with words that the first two tables refer to.
4) test_P. Columns: PId, t1Id, t2Id.
I created a stored procedures, that gets everything from test_P table and should also get Word from test_w table. I tried to make a subquery, but I get following error:
Msg 245, Level 16, State 1, Procedure test_TestGet1, Line 12
Conversion failed when converting the nvarchar value 'One' to data type int.

ALTER PROCEDURE [dbo].[test_TestGet1]
	(
	@PId int,
	@t1Id int,
	@t2Id int
	)
AS
	SELECT * FROM test_P p
	JOIN test_t1 a
	ON p.t1Id=a.t1Id
	JOIN test_t2 b
	ON p.t2Id=b.t2Id
	WHERE @PId=1 AND @t1Id=(SELECT w.word FROM test_w w WHERE w.wId=a.wId AND a.t1Id=@t1Id) AND @t2Id=(SELECT w.word FROM test_w w WHERE w.wId=b.wId AND b.t2Id=@t2Id)

Open in new window

Daniel Wilson

Is w.word an nvarchar type? If so, that would cause the error.

Subselects are valid, but I don't think this query is doing what you want ... even if it would compile.

How about this JOIN solution?

ALTER PROCEDURE [dbo].[test_TestGet1]
        (
        @PId int,
        @t1Id int,
        @t2Id int
        )
AS
        SELECT p.*, a.*, b.*,
        W_A.word as WordA, W_B.word as WordB
        FROM test_P p
        JOIN test_t1 a
        ON p.t1Id=a.t1Id
        JOIN test_t2 b
        ON p.t2Id=b.t2Id
        Inner Join test_w W_A on W_A.wID = @t1ID
        Inner Join test_w W_B on W_B.wID = @t2ID
        WHERE @PId=1

Open in new window

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnkainn

ASKER

w.word is nvarchar type. This inner Join doesn't work in my case. I need to match wId in test_t1 to wId in test_w.
I tried the cast but did not succeed. Maybe I did it wrong. Could you please change the subquery I sent and add the casting?
Thanks

SOLUTION

Daniel Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

The problem seems to be @t1Id=(SELECT w.word
because @t1Id is int and w.word is nvarchar, so think you want a.wid = (select w.wid
but you are not returning any values from test_w.

so, think what you might be looking for is something like :

ALTER PROCEDURE [dbo].[test_TestGet1]
      (
      @PId int,
      @t1Id int,
      @t2Id int
      )
AS
      SELECT * FROM test_P p
      JOIN test_t1 a ON p.t1Id=a.t1Id
      JOIN test_w aw ON a.wId=aw.wId
      JOIN test_t2 b ON p.t2Id=b.t2Id
      JOIN test_w bw ON b.wId=bw.wId

johnkainn

ASKER

I went over my code and the only parameter I need is PId. So following is my revised code with the casting. If I execute I do not get anything returned. For PId=1, I should get returned
PId=1, T1Id=1, Word(t1)="Two", T2Id=2, Word(t2)="One"
Here is what is in my tables.
test_P (row1) : PId=1, t1Id=1, t2Id=1
test_t1(row1): t1Id=1, wId=2
test_t2(row1):t2Id=1, wId=1
test_w:(row1): wId=1, word="One"
(row2):wId=2,word="Two"

CREATE PROCEDURE [dbo].[test_TestGet3]
	(
	@PId int
	)
AS
	SELECT * FROM test_P p
	JOIN test_t1 a
	ON p.t1Id=a.t1Id
	JOIN test_t2 b
	ON p.t2Id=b.t2Id
	WHERE @PId=p.PId AND cast(p.t1Id as nvarchar(100))=(SELECT w.word FROM test_w w WHERE w.wId=a.wId) AND cast(p.t2Id as nvarchar(100))=(SELECT w.word FROM test_w w WHERE w.wId=b.wId)

Open in new window

Mark Wills

have you looked at my code yet ?

ALTER PROCEDURE [dbo].[test_TestGet1]
(
@PId int,
@t1Id int,
@t2Id int
)
AS
SELECT * FROM test_P p
JOIN test_t1 a ON p.t1Id=a.t1Id
JOIN test_w aw ON a.wId=aw.wId
JOIN test_t2 b ON p.t2Id=b.t2Id
JOIN test_w bw ON b.wId=bw.wId

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnkainn

ASKER

Thank you all very much for your help.