Helpabout stored procedure

Question

Post reply

Helpabout stored procedure

aysegul

Right there with Babe

Points: 778
More actions
September 16, 2008 at 8:57 am

#75316

Hi,
I have a problem on my stored procedure.my stored procedure has one parameter type of int.
I write dyn and dynamic sql and exec it. It completed successfully when I parse it.
When I try to exec my stored procedure, I get this message
Conversion failed when converting the varchar value ' SELECT 'Görevli', dbo.Common_Accounts.FullName AS soforFROM dbo.Transportation_AssignerMissionDrivers INNER JOIN dbo.Transportation_Missions ON dbo.Transportation_AssignerMissionDrivers.MissionDriverDetailId = dbo.Transportation_Missions.OId INNER JOIN dbo.Transportation_Drivers ON dbo.Transportation_AssignerMissionDrivers.DriverId = dbo.Transportation_Drivers.OId INNER JOIN dbo.Common_Accounts ON dbo.Transportation_Drivers.CreateTime = dbo.Common_Accounts.OIdWHERE CONVERT(nvarchar, dbo.Transportation_Missions.DueStartDate, 102) > (SELECT top 1 DueStartDate FROM dbo.Transportation_ServiceSchedules WHERE dbo.Transportation_ServiceSchedules.OId = ' to data type int. (Microsoft SQL Server, Error: 245)
My dynamic sql :
set @sqlgorevli = ' SELECT ''Görevli'', dbo.Common_Accounts.FullName AS sofor'+ 'FROM dbo.Transportation_AssignerMissionDrivers INNER JOIN ' +
' dbo.Transportation_Missions ON dbo.Transportation_AssignerMissionDrivers.MissionDriverDetailId = dbo.Transportation_Missions.OId INNER JOIN' +
' dbo.Transportation_Drivers ON dbo.Transportation_AssignerMissionDrivers.DriverId = dbo.Transportation_Drivers.OId INNER JOIN ' +
' dbo.Common_Accounts ON dbo.Transportation_Drivers.CreateTime = dbo.Common_Accounts.OId'+
'WHERE CONVERT(nvarchar, dbo.Transportation_Missions.DueStartDate, 102) > '+
' (SELECT top 1 DueStartDate FROM dbo.Transportation_ServiceSchedules WHERE dbo.Transportation_ServiceSchedules.OId = '+@id+')'
Kind Regards
Ayşegül

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Cast your @id as a varchar.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

I think your problem is in the last line, where you are trying to add the int data type to the rest of the select statement. I think you need to put the whole select in one set of single quotes, then add the @id variable in another set. Try this

' SELECT

''Görevli'',

dbo.Common_Accounts.FullName AS sofor

FROM dbo.Transportation_AssignerMissionDrivers INNER JOIN

dbo.Transportation_Missions ON dbo.Transportation_AssignerMissionDrivers.MissionDriverDetailId = dbo.Transportation_Missions.OId INNER JOIN

dbo.Transportation_Drivers ON dbo.Transportation_AssignerMissionDrivers.DriverId = dbo.Transportation_Drivers.OId INNER JOIN

dbo.Common_Accounts ON dbo.Transportation_Drivers.CreateTime = dbo.Common_Accounts.OId

WHERE CONVERT(nvarchar, dbo.Transportation_Missions.DueStartDate, 102) >

(SELECT top 1 DueStartDate FROM dbo.Transportation_ServiceSchedules

WHERE dbo.Transportation_ServiceSchedules.OId = ' + @id + ' + ')'

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

WHERE dbo.Transportation_ServiceSchedules.OId = ' + CONVERT(VARCHAR(10), @id) + ' + ')'

_____________
Code for TallyGenerator

aysegul Right there with Babe Points: 778 More actions · Answer 4

aysegul

Right there with Babe

Points: 778

September 17, 2008 at 1:52 am

#872835

Thank you very much.

I works.

Kind regards

Aysegül