SP failed converting varchar 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

General SQL Server Forums

New to SQL Server Programming

SP failed converting varchar to int

Author

Topic

mburge
Starting Member

3 Posts

Posted - 2010-08-24 : 11:45:43

Hi All,

Sorry if this is a basic question but I'm new to SQL so would appreciate the help

This is my stored procedure:

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

Declare @SQL varchar(1000)

SET @SQL = 'Select memberpolicies.policyid,
memberpolicies.StartDate,
memberpolicies.Enddate,
members.firstname,
members.lastname,
members.postalcode,
members.telephone,
policytypes.description
from memberpolicies
inner join members
on
memberpolicies.UserId = members.UserId
inner join policytypes
on
memberpolicies.policytypeid = policytypes.policytypeid
Where 1 = 1'

IF @ID <> ''
SET @SQL = @SQL + ' AND memberpolicies.policyid = ' + @ID
EXEC(@SQL)

----------

The procedure works fine if i pass a NULL value. However It seems to be detecting @ID as a string. I have tried using Cast() with no luck.

The Error message i get is:

Conversion failed when converting the varchar value to data type int

any help or direction to suitable reading material would be great.

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-24 : 11:55:33

it's because you are missing the quotes around the ID Value. You need to be careful when running dynamic SQL like this since it it can be subject to SQL injection. There's a couple of ways to handle this. You can use dynamic sql or you can perform the logic in the where clause (i.e. memberpolicies.policyid = IsNull(@id, memberpolicies.policyid) and not use dynamic sql. But here is the safer dynamic approach.


DECLARE @sql nvarchar(2000), 
        @Parms nvarchar(int)

set @parms = N'@Id varchar(10)'

SET @SQL = 'Select memberpolicies.policyid, 
memberpolicies.StartDate, 
memberpolicies.Enddate, 
members.firstname, 
members.lastname, 
members.postalcode, 
members.telephone, 
policytypes.description 
from memberpolicies
inner join members
on
memberpolicies.UserId = members.UserId
inner join policytypes
on
memberpolicies.policytypeid = policytypes.policytypeid
Where 1 = 1'

IF @ID <> ''
    SET @SQL = @SQL + ' AND memberpolicies.policyid = @ID'

EXECUTE sp_executesql @SQL, @Parms,
                      @ID = @ID

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:25:47

why do you need dynamic sql for this? wont the following suffice?


Select memberpolicies.policyid, 
memberpolicies.StartDate, 
memberpolicies.Enddate, 
members.firstname, 
members.lastname, 
members.postalcode, 
members.telephone, 
policytypes.description 
from memberpolicies
inner join members
on
memberpolicies.UserId = members.UserId
inner join policytypes
on
memberpolicies.policytypeid = policytypes.policytypeid
Where memberpolicies.policyid = @ID
OR ISNULL(@ID,'') = ''

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources