October 4, 2007 at 7:13 am
I am getting an issue when I am using a stored proc from a view.
I am to returning values, one being a varchar(10).
When I run the view, the values for this column and all others are returned fine.
When I run the stored procedure, the following error is shown:
Conversion failed when converting the varchar value 'CH' to data type int.
All other values return fine bar this one column and as I said its already a varchar in the table so I don't know why sql server (2005) thinks I want to convert it, I don't and at no point have tried to.
Any ideas much appreciated...
October 4, 2007 at 8:17 am
Sounds like a problem inside the code of the stored procedure. Can you post it?
Assuming a straight SELECT query, at no point should there be a conversion from anything to anything. Something else is going on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2007 at 8:29 am
Yeah, here it is, thanks.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Messages_Select]
@User_ID int,
@PageIndex int,
@PageSize int,
@AllowedMessages varchar(50),
@SortBy varchar(50)
WITH EXECUTE AS CALLER
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET DATEFORMAT DMY
declare @sql varchar(max)
declare @PageIndexStr varchar(50)
declare @PageSizeStr varchar(50)
declare @User_IDStr varchar(50)
set @PageIndexStr = cast(@PageIndex as varchar(50))
set @PageSizeStr = cast(@PageSize as varchar(50))
set @User_IDStr = cast(@User_ID as varchar(50))
set @sql =
'
WITH MSHWithRowNumbers AS
('
if (@SortBy='')
set @sql = @sql + 'SELECT ROW_NUMBER() OVER (ORDER BY MSH7_DateTimeOfMessage DESC, RecipientEntity ASC) AS RowNumber, UNPROC_GP.*'
else
SELECT ROW_NUMBER() OVER (ORDER BY ' + @SortBy + ') AS RowNumber, UNPROC_GP.*'
FROM HL7_vw_Messages_GP as UNPROC_GP
Where
(
(UNPROC_GP.[Recipient_User_ID] = ' + cast(@User_ID as varchar(50)) + ' or UNPROC_GP.[Sender_User_ID] = ' + cast(@User_ID as varchar(50)) + ')
OR
(
UNPROC_GP.[Recipient_User_ID] in (select AccessingUser from HL_User_Access where [User_ID] = ' + cast(@User_ID as varchar(50)) + ')
OR
UNPROC_GP.[Sender_User_ID] in (select AccessingUser from HL_User_Access where [User_ID] = ' + cast(@User_ID as varchar(50)) + ')
)
)
AND (Viewed = 0 AND Printed=0 AND Exported=0)
AND (ShowInUnprocessed = 1)
'
if (@AllowedMessages!='')
set @sql = @sql + ' and(Not Message_Type_ID IN ('+@AllowedMessages+'))'
)
SELECT * FROM MSHWithRowNumbers
WHERE RowNumber BETWEEN ' + @PageIndexStr + ' AND (' + @PageIndexStr + ' + '+@PageSizeStr+') - 1
'
exec (@Sql)
END
HL7_vw_Messages_GP is the view I'm pulling it from and all works fine here.
October 4, 2007 at 9:00 am
Hmmm. Not an easy one. I looked through the code, I generated the query. Everything looks ok, assuming the data types in the tables are as presented, with one possible issue. The parameter @AllowedMessages I'm assuming, because of how it's used in the query, is a comma deliminited list of numbers? Because it's a nvarchar, I was able to pass it a string value. Just a possibility. From what I can see, that'd be the first thing I checked.
Other than that... I confess to being stumped.
Does it always error no matter what? Does the view convert anything from an int to a string? I ask because, it's possible that when the view is expanded as part of the query, it might not include that conversion? Just guessing on that one.
You might try what I did, which is let the proc generate the select statement and output just that. Then you take the statement and verify what was generated.
Sorry I'm not being helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 4, 2007 at 9:47 am
Hi, I got that sorted. Sorry, I kind of lead you up the garden path, not purposely of course!
I thought the view worked, well, it did but I was 'unionising'
two views that both worked individually.
The union view didn't work rather than the stored proc.
One view was getting values for the column in question, the other would never get any values for that column, which was expected, so I had set it to always equal null.
What I had was null as colname in second view, I needed cast(null as varchar) as colname, this sorted the problem.
Your advice did help, as a result I started looking at what else was converted etc. and noticed the other nulls were all casted.
Thank you.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply