Convert issue in dynamic sql - SQL Server Forums

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

SQL Server Development (2000)

Convert issue in dynamic sql

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-06-25 : 10:48:17

DECLARE @retval int,
@sql varchar(2000),
@sqlf varchar(50)

--determine filter
SET @sqlf=
CASE WHEN @filter = 1 THEN ' AND eventtype = 1 '
WHEN @filter = 2 THEN ' AND eventtype = 2 '
WHEN @filter = 3 THEN ' AND eventtype = 3 '
WHEN @filter = 4 THEN ' AND eventtype = 4 '
WHEN @filter = 5 THEN ' AND eventtype = 5 '
WHEN @filter = 10 THEN ' AND eventtype = 10 '
END

SET @sql= 'SELECT
a.EVENTID,
a.seen as custseen,
b.typeid,
b.eventtype,
b.eventtypedesc,
b.fkey,
b.catid,
b.ordn,
convert(varchar(25),b.createddate,101),
b.metoffid,
b.custuid,
left(b.custname,12),
b.custoffid,
b.totviewed,
b.lastviewedname,
convert(varchar(25),b.lastvieweddate,101),
b.newmet,
b.completed,
left(b.eventdesc,53),
b.haschild

FROM tbleventuidview a
INNER JOIN tbleventcomlog b ON a.EVENTID=B.EVENTID
WHERE a.UID=' + @UID + @sqlf + '
ORDER by a.seen , b.createddate '

PRINT (@sql)

I am getting a convert issue with the left function am I not
enclosing this right.

Any help. Thanks for your time.

slow down to move faster...

MakeYourDaddyProud

184 Posts

Posted - 2002-06-25 : 11:18:41

Where is the declaration for your UID? I am assuming wherever this value comes from it is a varchar field, in that case where are your starting and ending quotes to complete the SQL?...

quote:

...
WHERE a.UID=' + @UID + @sqlf + '
...

In that case you could have...

WHERE a.UID = Danny2Sweet + AND EventType = 2

Which won't work syntactically

Try changing your where line to

WHERE a.UID=' + CHAR(39) + @UID + CHAR(39) + @sqlf + '

That should help a bit

Dan

<<monet makes money>>

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-06-25 : 11:38:47

quote:

...
WHERE a.UID=' + @UID + @sqlf + '
...

You need quotes around the unique identifier. The conversion is implicit, but if you don't add quotes SQL tries to parse the dashes in the uid as subtraction operators.

WHERE a.UID=''' + @UID + ''''+@sqlf + '...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources