Conversion failed when converting the varchar valu

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 2000 Forums

Transact-SQL (2000)

Conversion failed when converting the varchar valu

Author

Topic

gotafly
Yak Posting Veteran

54 Posts

Posted - 2006-12-03 : 15:10:53

Here is my stored proc:

ALTER PROCEDURE dbo.GetArticlesAll

(
@ArticleSiteID int,
@ArticleTypeID int,
@ArticleIssueID int
)

AS
DECLARE @sql nvarchar(4000)
SELECT @sql =
'SELECT
Articles.ArticleID,
Articles.ArticleTitle,
ArticleType.ArticleTypeDesc,
issue.descr,
Articles.ArticleDateStart
FROM
Articles
INNER JOIN
ArticleType ON Articles.ArticleTypeID = ArticleType.ArticleTypeID
INNER JOIN
issue ON Articles.ArticleIssueID = issue.ukey
WHERE
(Articles.ArticleSiteID = ' + @ArticleSiteID + ') '

EXEC sp_executesql @sql

Here is the error?

Conversion failed when converting the varchar value 'SELECT
Articles.ArticleID,
Articles.ArticleTitle,
ArticleType.ArticleTypeDesc,
issue.descr,
Articles.ArticleDateStart
FROM
Articles
INNER JOIN
ArticleType ON Articles.ArticleTypeID = ArticleType.ArticleTypeID
INNER JOIN
issue ON Articles.ArticleIssueID = issue.ukey
WHERE
(Articles.ArticleSiteID = ' to data type int.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-03 : 15:43:45

why are you using dynamic SQL for this?
you could simply do:


SELECT
Articles.ArticleID, 
Articles.ArticleTitle, 
ArticleType.ArticleTypeDesc, 
issue.descr, 
Articles.ArticleDateStart
FROM 
Articles 
INNER JOIN
ArticleType ON Articles.ArticleTypeID = ArticleType.ArticleTypeID 
INNER JOIN
issue ON Articles.ArticleIssueID = issue.ukey
WHERE Articles.ArticleSiteID = @ArticleSiteID

but to answer you question you need to convert the int to varchar using convert function.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

gotafly
Yak Posting Veteran

54 Posts

Posted - 2006-12-03 : 16:04:15

This is the whole thing...

ALTER PROCEDURE dbo.GetArticlesAll

(
@ArticleSiteID varchar(30) = '',
@ArticleTypeID int = 0,
@ArticleIssueID int = 0
)

AS
DECLARE @sql nvarchar(4000)
Set @sql =
'SELECT
Articles.ArticleID,
Articles.ArticleTitle,
ArticleType.ArticleTypeDesc,
issue.descr,
Articles.ArticleDateStart
FROM
Articles
INNER JOIN
ArticleType ON Articles.ArticleTypeID = ArticleType.ArticleTypeID
INNER JOIN
issue ON Articles.ArticleIssueID = issue.ukey
WHERE
(Articles.ArticleSiteID = ' + @ArticleSiteID + ' )'
if @ArticleTypeID <> 0
set @sql = @sql + ' and Articles.ArticleTypeID = ' + @ArticleTypeID

if @ArticleIssueID <> 0
set @sql = @sql + ' and Articles.ArticleIssueID = ' + @ArticleIssueID
EXEC @sql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 04:15:30

[code]ALTER PROCEDURE dbo.GetArticlesAll
(
@ArticleSiteID varchar(30) = '',
@ArticleTypeID int = 0,
@ArticleIssueID int = 0
)
AS

SELECT Articles.ArticleID,
Articles.ArticleTitle,
ArticleType.ArticleTypeDesc,
issue.descr,
Articles.ArticleDateStart
FROM Articles
INNER JOIN ArticleType ON Articles.ArticleTypeID = ArticleType.ArticleTypeID
INNER JOIN issue ON Articles.ArticleIssueID = issue.ukey
WHERE Articles.ArticleSiteID = @ArticleSiteID
AND CASE WHEN @ArticleTypeID <> 0 AND Articles.ArticleTypeID = @ArticleTypeID THEN 1 ELSE 0 END = 1
AND CASE WHEN @ArticleIssueID <> 0 AND Articles.ArticleIssueID = @ArticleIssueID THEN 1 ELSE 0 END = 1[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources