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.
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)
Microsoft SQL Server 2005
Last Comment
johnkainn
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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"
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)
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you all very much for your help.
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?
Open in new window