error - converting nvarchar to int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

error - converting nvarchar to int

Author

Topic

BCJ
Starting Member

42 Posts

Posted - 2008-07-09 : 12:24:19

ALTER PROCEDURE currentRpt
@Sid int,
@BeginDate datetime,
@EndDate datetime,
@Tid int
AS

select subtaskdescription, fiscalyearcq, fiscalquartercq, probablereservecq, crmcomment, a.createdby,
case recordtype when 'A' THEN 'ATT'
when 'M' THEN 'MS' END as RecordType
from t1 a
inner join t2 c on a.tid = c.tid
inner join t3 d on a.sbtid = d.sbtid
inner join t4 b on a.stid = b.stid
inner join t5 e on b.sttid = e.sttid
where (a.sid in(@Sid)
OR (-8 in (@Sid) AND e.sttCode = 'A')
OR (-7 in (@Sid) AND e.sttCode = 'P')
OR (-6 in (@Sid) AND e.SttCode = 'I')
OR (-5 in (@Sid) AND e.SttCode = 'C')
OR (-9 in (@Sid) AND e.SttCode IS NOT NULL))
AND (a.tid = @Tid OR @Tid = -9)

when running the report i'm getting
error converting nvarchar to int

and , i have changed the datatype of @Sid to nvarchar and the there was no error , but no data also.

Any help appreciated. thanks.

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 12:53:12

Which fields in t1,2,3,4,5 are nvarchar fields? hard to diagnose this one without the structure under it. Sounds to me though like you're trying to join an int datatype to an nvarchar and the implied conversion isn't working out.

Mike
"oh, that monkey is going to pay"

BCJ
Starting Member

42 Posts

Posted - 2008-07-09 : 13:35:40

All the id fields used to join are int, here is the error , conversion failed when converting nvarchar value(@Sid) or '(4,5)' to data type int.
when i'm passing multiple values through this parameter(@sid) it happens, if only one value then there is no error. but ,this parameter field is a multi value field. thanks.

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-09 : 13:45:29

You would need to make this dynamic sql to work. The problem is that when you add the comma in ('4,5' you are no longer creating a valid integer value which SID is declared as.
create table #temp(sid int)

insert into #temp(sid)
values(4)
insert into #temp(sid)
values(5)

declare @sid varchar(20)
declare @mysql varchar(1000)
set @sid='4,5'
select @mysql='select * from #temp
where [sid] in(' + @sid + ')'
select @mysql

Mike
"oh, that monkey is going to pay"

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-09 : 14:16:38

To me your where condition predicates are incorrect.
example: Why do you have (-8 in (@Sid) AND e.sttCode = 'A'). Shouldn't this be a field reference and not just a number by itself.

Dallr

BCJ
Starting Member

42 Posts

Posted - 2008-07-09 : 16:37:35

Thanks for your help, need little more explanation,

where i have to write this temporary table and related stuff, is it in the existing procedure ?

here is my procedcure,

USE [CRM]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE currentRpt
@Sid int,
@BeginDate datetime,
@EndDate datetime,
@Tid int
AS

select description, fiscalyear, fiscalquarter,, probableres, comment,
case recordtype when 'A' THEN 'ATT'
when 'M' THEN 'MS' END as RecordType
from t1 a
inner join t2 c on a.tid = c.tid
inner join t3 d on a.sbtid = d.sbtid
inner join t4 b on a.stid = b.stid
inner join t5 e on b.sttid = e.sttid
where (a.sid in(@Sid)
OR (-8 in (@Sid) AND e.sttCode = 'A')
OR (-7 in (@Sid) AND e.sttCode = 'P')
OR (-6 in (@Sid) AND e.SttCode = 'I')
OR (-5 in (@Sid) AND e.SttCode = 'C')
OR (-9 in (@Sid) AND e.SttCode IS NOT NULL))
AND (a.tid = @Tid OR @Tid = -9)
-----------------
so my question is, how can i insert each value of Sid into #temp(sid) table and pass each value into the in(@Sid) for validation.
as per my understanding i'm getting all the sid values '(4,5)' in one variable.. if you can show me the structure of the entire procedure
how it should be. sorry for asking more on this after your good explanation.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-09 : 17:21:14

Hi there.

You'd do something like this...


ALTER PROCEDURE currentRpt
	@Sid VARCHAR(1000),
	@BeginDate datetime,
	@EndDate datetime,
	@Tid int
AS

DECLARE @sql VARCHAR(4000)
	
SET @sql = '
SELECT
	description
	, fiscalyear
	, fiscalquarter
	, probableres
	, comment
	, case recordtype
		when ''A'' THEN ''ATT''
		when ''M'' THEN ''MS''
	END as RecordType
from
	t1 a
	inner join t2 c on a.tid = c.tid
	inner join t3 d on a.sbtid = d.sbtid
	inner join t4 b on a.stid = b.stid
	inner join t5 e on b.sttid = e.sttid
where
	(
		a.sid in (' + @Sid + ')
		OR (-8 in (' + @Sid + ') AND e.sttCode = ''A'')
		OR (-7 in (' + @Sid + ') AND e.sttCode = ''P'')
		OR (-6 in (' + @Sid + ') AND e.SttCode = ''I'')
		OR (-5 in (' + @Sid + ') AND e.SttCode = ''C'')
		OR (-9 in (' + @Sid + ') AND e.SttCode IS NOT NULL)
	)
	AND (
		a.tid = ' + CAST(@Tid AS VARCHAR(30)) + '
		OR
		' + CAST(@Tid AS VARCHAR(30)) + ' = -9
	)'
	
EXEC (@sql)

So that when the string is executed the IN commands have the format..

OR (-9 IN (2,3,4,7).....

if you passed the string '2,3,4,7' to the stored proc.

************

If you wanted to go 1 step better than this check out sp_executeSql in books on line as it would imporve performance and allow you to still use your @tid as a variable.

All the best,

-------------
Charlie

BCJ
Starting Member

42 Posts

Posted - 2008-07-10 : 09:58:28

Thanks Charlie, that realy helped me, and thanks to All.

Also would like to know how to convert the date fields to make it work, right now i'm using like

AND (a.CMDate between ' + convert( datetime,@BeginDate ) +' and ' + convert(datetime ,@EndDate ) + ')
but, it shows an error - conversion failed when converting datetime from characterstring.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 11:04:37

Hi Again,

You need to convert the datetimes to a string to concatenate to the @sql variable. Annoyingly because you would have to represent the datetime values inside the string like this ( '2008-01-01 00:00:00' ) you need to escape the ' so that they are included in the string.

Syntax would be like this...


ALTER PROCEDURE currentRpt
	@Sid VARCHAR(1000),
	@BeginDate datetime,
	@EndDate datetime,
	@Tid int
AS

DECLARE @sql VARCHAR(4000)
	
SET @sql = '
SELECT
	description
	, fiscalyear
	, fiscalquarter
	, probableres
	, comment
	, case recordtype
		when ''A'' THEN ''ATT''
		when ''M'' THEN ''MS''
	END as RecordType
from
	t1 a
	inner join t2 c on a.tid = c.tid
	inner join t3 d on a.sbtid = d.sbtid
	inner join t4 b on a.stid = b.stid
	inner join t5 e on b.sttid = e.sttid
where
	(
		a.sid in (' + @Sid + ')
		OR (-8 in (' + @Sid + ') AND e.sttCode = ''A'')
		OR (-7 in (' + @Sid + ') AND e.sttCode = ''P'')
		OR (-6 in (' + @Sid + ') AND e.SttCode = ''I'')
		OR (-5 in (' + @Sid + ') AND e.SttCode = ''C'')
		OR (-9 in (' + @Sid + ') AND e.SttCode IS NOT NULL)
	)
	AND (
		a.tid = ' + CAST(@Tid AS VARCHAR(30)) + '
		OR
		' + CAST(@Tid AS VARCHAR(30)) + ' = -9
	)

	AND (
		a.CMDate BETWEEN ''' + CAST(@BeginDate AS VARCHAR(30)) + ''' AND ''' + CAST(@EndDate AS VARCHAR(30)) + '''
	)'
	
EXEC (@sql)

But in this case if you used EXEC sp_executeSql instead, you could take advantage of variable substitution to simplify the dates.

Check out :: http://www.sommarskog.se/dynamic_sql.html for a very, very good page of information on this topic.

-------------
Charlie

BCJ
Starting Member

42 Posts

Posted - 2008-07-10 : 12:19:25

After adding the changes there is no error but, not getting any data when i pass the date values, i'm passing mm/dd/yyyy format dates through the paramter fields. is there any other option i can try. thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 12:22:53

Can you alter the procedure like this....

And then run again in query analyser and post the results of the new PRINT statement?


ALTER PROCEDURE currentRpt
	@Sid VARCHAR(1000),
	@BeginDate datetime,
	@EndDate datetime,
	@Tid int
AS

DECLARE @sql VARCHAR(4000)
	
SET @sql = '
SELECT
	description
	, fiscalyear
	, fiscalquarter
	, probableres
	, comment
	, case recordtype
		when ''A'' THEN ''ATT''
		when ''M'' THEN ''MS''
	END as RecordType
from
	t1 a
	inner join t2 c on a.tid = c.tid
	inner join t3 d on a.sbtid = d.sbtid
	inner join t4 b on a.stid = b.stid
	inner join t5 e on b.sttid = e.sttid
where
	(
		a.sid in (' + @Sid + ')
		OR (-8 in (' + @Sid + ') AND e.sttCode = ''A'')
		OR (-7 in (' + @Sid + ') AND e.sttCode = ''P'')
		OR (-6 in (' + @Sid + ') AND e.SttCode = ''I'')
		OR (-5 in (' + @Sid + ') AND e.SttCode = ''C'')
		OR (-9 in (' + @Sid + ') AND e.SttCode IS NOT NULL)
	)
	AND (
		a.tid = ' + CAST(@Tid AS VARCHAR(30)) + '
		OR
		' + CAST(@Tid AS VARCHAR(30)) + ' = -9
	)

	AND (
		a.CMDate BETWEEN ''' + CAST(@BeginDate AS VARCHAR(30)) + ''' AND ''' + CAST(@EndDate AS VARCHAR(30)) + '''
	)'
	
PRINT @sql

EXEC (@sql)

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-10 : 12:30:33

Sorry -- it's quitting time for me in the UK. Post the results of the print statement -- they should probably immediately show what's wrong. If no-one else has relied to you by tomorrow morning I'll come back with something.

Bye,
Charlie.

-------------
Charlie

BCJ
Starting Member

42 Posts

Posted - 2008-07-10 : 14:56:48

Here the is print statement details, see the different date ranges i tried.
In the second case i got 3 records(all 3 are from 12th month) when i run the query in sql server, but nothing on the report. why this not shown in the first case.Thanks......

--------------
First case
----------------
select
description
, fiscalyear
, fiscalquarter
, probableres
, comment
, case recordtype
when ''A'' THEN ''ATT''
when ''M'' THEN ''MS''
END as RecordType
from
t1 a
inner join t2 c on a.tid = c.tid
inner join t3 d on a.sbtid = d.sbtid
inner join t4 b on a.stid = b.stid
inner join t5 e on b.sttid = e.sttid
where
(a.sid in (4,89)
OR (-8 in (4,89) AND e.sttCode = ''A'')
OR (-7 in (4,89) AND e.sttCode = ''P'')
OR (-6 in (4,89) AND e.SttCode = ''I'')
OR (-5 in (4,89) AND e.SttCode = ''C'')
OR (-9 in (4,89) AND e.SttCode IS NOT NULL)
)
AND (
a.tid = 1 OR 1 = -9)
)

AND (
a.CMDate BETWEEN '01/01/2007' AND '01/01/2008'
)
(0 row(s) affected)

(1 row(s) affected)

---------------------------------

Second Case
------------------------------
AND (
a.CMDate BETWEEN '01/01/2007' AND '12/31/2007'
)

(3 row(s) affected)

(1 row(s) affected)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-11 : 04:37:32

It doesn't look like your @beginDate and @endDate are DATETIME variables. I was expecting the print to produce something like...


AND (
a.CMDate BETWEEN 'Jan  1 2007 12:00AM' AND 'Jan  1 2008 12:00AM'
)

Which is how a DATETIME gets converted into a VARCHAR on my database if you don't specify a style.

Can you confirm that @beginDate and @endDate are DATETIME's in your procedure and you are not using a STRING instead.

Also -- try passing the values '2007-01-01' and '2008-01-01' rather than the 01/01/2007 etc.

Also -- is A.CMDate a DATETIME? or are you representing dates with strings?

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-11 : 04:40:27

Also, are you sure that...


, case recordtype
when ''A'' THEN ''ATT''
when ''M'' THEN ''MS''
END as RecordType

Got printed out exactly like that?

I was expecting it to look like:


, case recordtype
when 'A' THEN 'ATT'
when 'M' THEN 'MS'
END as RecordType

-------------
Charlie

BCJ
Starting Member

42 Posts

Posted - 2008-07-11 : 09:30:41

Hi,

>>Can you confirm that @beginDate and @endDate are DATETIME's in your procedure and you are not using a STRING instead.

now i havbe changed to datetime and getting the string as expected like AND (
a.CMDate BETWEEN 'Jan 1 2007 12:00AM' AND 'Jan 1 2008 12:00AM'
)

>>Also -- is A.CMDate a DATETIME? or are you representing dates with strings?

A.CMDate is nvarchar in the table.

Thanks.

BCJ
Starting Member

42 Posts

Posted - 2008-07-11 : 10:22:17

So, i changed like ,

AND cast(a.CMDate as DATETIME) BETWEEN ''' + CAST(@BeginDate AS VARCHAR(30)) + ''' AND ''' + CAST(@EndDate AS VARCHAR(30)) + '''
)

and it is working fine..........i realy thankful to you for this help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-07-11 : 10:50:34

You are welcome.

But why are you storing a date as a NVARCHAR in the table? Seriously consider changing this because, as you can see, it needlessly complicates things. You can always get the info out of the database in whatever format you wish with the CONVERT(CHAR(X), <date>, option). For example

SELECT CONVERT(CHAR(11), '2008-01-01', 106) produces :: 01-Jan-2008

-------------
Charlie

BCJ
Starting Member

42 Posts

Posted - 2008-07-11 : 13:19:37

yes, i have to give a suggestion to change the date datatype to datetime, at this time i'm not sure any other usage is there with nvarchar.
once again thanks for the information.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources