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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-09 : 18:10:33
|
Sathiya Narayanan N writes "Hi,
I have a problem in creating a dynamic query. The sproc is given below:
-----------------
CREATE PROC sproc_GetAvailableVehicles
@nSrcPlaceId as int,
@nDestPlaceId as int,
@dtDate as datetime="January 1, 1900",
@dtTime as datetime="00:00"
AS
DECLARE @dDay as int,
@dMonth as int,
@nColName as nvarchar(5),
@loopVar as int,
@qSQL as nVarChar(500)
SET @qSQL = ''
SET @dDay = Day(@dtDate)
SET @dMonth = Month(@dtDate)
SET @loopVar = 1
SET @nColName = N'nD' + CAST(@dDay AS nvarchar(2))
WHILE (@loopVar < 32)
BEGIN
IF (@loopVar = @dDay)
BEGIN
/sn/ SET @qSQL = 'SELECT nVehicleId from tblAvailability where nd' + CAST(@dDay AS nvarchar(2)) + ' = 1'
PRINT @qSQL
BREAK;
END
SET @loopVar = @loopVar + 1
END
SELECT DISTINCT vr.nVehicleId FROM tblVehicleRoute vr, tblStopPoints sp, tblAvailability a WHERE vr.nSrcPlaceId = @nSrcPlaceId and sp.nStopPlaceId = 2 and vr.nDestPlaceId = sp.nStopPlaceId and vr.nVehicleId in (/sn1/EXEC sp_executesql @qSQL)
UNION
SELECT DISTINCT vr.nVehicleId FROM tblVehicleRoute vr, tblStopPoints sp where vr.nSrcPlaceId = @nSrcPlaceId and vr.bIncludeStopPoints = 1 and sp.nStopPlaceId = 2 and vr.nVehicleId in (/sn1/EXEC sp_executesql @qSQL)
-------------------------------------
I want to generate the query as in /sn/. I have column names like nD1,nD2,nD3,...,nD31.
By the input value I would like to compare with the appropriate column as in /sn/. When I run the generated sql separately it works just fine.
But when I attach it in the places marked /sn1/ it displays error mesgs. and says 'Incorrect Syntax near Exec'.
How to solve this?
I am using Win 2000 Prof., MS SQL Server 7.0.
Thanks in advance,
Sathya." |
|
|
|
|
|
|
|
Generating a dynamic query