September 16, 2008 at 12:41 am
Can someone please explain to me where my problem lies in the following statements. I am struggeling to get to the stage where i can execute the thing...:w00t:
use adventureWorks
go
--When trying to create this procedure it give the following error
/*
Msg 102, Level 15, State 1, Procedure sp_Test, Line 5
Incorrect syntax near '+'.
*/
Create procedure sp_Test @Wherestring VARCHAR(50)
as
SELECT *
FROM Person.Address where AddressLine2 is null + @WhereString + ''
go
sp_Test ' and city = ''Bothell'''
Why do people point to their wrists when asking for the time, but don't point to their crotch when they ask where the bathroom is?
September 16, 2008 at 12:55 am
u hv to use dynamic queries for this.
Declare @sql varchar(1000)
SET @sql = 'SELECT *
FROM Person.Address where AddressLine2 ' + @WhereString
exec (@sql).
Its not clear why you are using is null. To check whether a column contains null value use "select * from table1 where col1 is null". For replacing/comparison null with some value use "ISNULL(col1,'aaa') = 'aaa' ".This will replace null values with the value 'aaa'.
"Keep Trying"
September 16, 2008 at 1:43 am
Thank you very much. This indeed worked.
Why does it now not work when i use it in a cursor that is in a SP?
The cursor that follows has 3 parameters
when i use the @dateFrom and @dateto Parameters only then the query work
but as soon as i use ... + @Wherestring + and ... then it gives me an error
saying "Incorrect syntax near '+'." and as soon as i declare @SQL and use that as a dynamic
it gives me another error
...
Incorrect syntax near the keyword 'Set'.
Here is the code...
Create Procedure SP_GasconP2 @DateFrom varchar(50), @dateTo varchar(50), @Wherestring varchar(500)
as
--Declare Variables
declare @IDNo varchar(50)
declare @ZoneIN varchar(50)
declare @SQL as varchar (2000)
--declare @DateFrom varchar(50)
--declare @DateTo varchar(50)
Declare GasconCur Cursor Static
for
--Select StateMent goes here
/* Alternative way using the @SQL
--This give the error
Set @SQL = 'Select P.ID_no ,zv.ZoneIn from ZonesVisited zv
Join Positions P on zv.PositionID = p.ID
Join Individuals I on I.ID_no = P.ID_no
Where
zv.zoneout is null and
(Convert(DateTime,[zonein],103) > convert(dateTime,@DateFrom,103)) and
(Convert(DateTime,[zonein],103) < convert(dateTime,@DateTo,103))'
--Group by P.ID_no
exec @SQL
*/
Select P.ID_no ,zv.ZoneIn from ZonesVisited zv
Join Positions P on zv.PositionID = p.ID
Join Individuals I on I.ID_no = P.ID_no
Where
zv.zoneout is null + @WhereString + and
(Convert(DateTime,[zonein],103) > convert(dateTime,@DateFrom,103)) and
(Convert(DateTime,[zonein],103) < convert(dateTime,@DateTo,103))
--Group by P.ID_no
Open GasconCur
Fetch from GasconCur
Into @IDno, @ZoneIN
while @@Fetch_Status = 0
Begin
insert into [11SpecialTbl]
Select top 1 I.[First_Name],I.Surname, I.Employeeno,P.Shift,@ZoneIN as ZoneIn,P.ID,P.[Clock in],[Clock out],max(zonein),
act.[Activity type],act.[Activity Start], act.[activity stop],act.Job,c.Description
from ZonesVisited zv
join Positions P on zv.PositionID = P.ID
Join Individuals I on I.ID_no = P.ID_no
Join Activity act on act.[Position ID] = P.ID
Join Cards c on c.[Card No] = Act.Job
where Convert(dateTime,[Clock in],103) >=
convert(dateTime,@ZoneIN,103) and
act.[Activity Type] <> 'BREAK' and
zv.zoneOUt is not null
and P.ID_no = @IDNo
and convert(datetime,P.[Clock in],103) >= Convert(dateTime,@zonein,103) and Convert(dateTIme,P.[Clock out],103) <= convert(dateTime,@dateTo,103)
group by [Clock in],[clock out],P.id,I.[First_Name],I.Surname, P.Shift,I.Employeeno,act.[Activity type],act.[Activity Start],
act.[activity stop],act.job,c.description
order by COnvert(datetIME,[cLOCK IN],103)
Fetch from GasconCur
into @idNo,@zoneIN
end
close GasconCur
deallocate GasconCur
Why do people point to their wrists when asking for the time, but don't point to their crotch when they ask where the bathroom is?
September 16, 2008 at 11:27 pm
what are u passing to @WhereString ?
"Why do people point to their wrists when asking for the time, but don't point to their crotch when they ask where the bathroom is? "
Very funny but not in very good taste i believe..;)
"Keep Trying"
September 18, 2008 at 4:25 am
Try this:
Set @SQL = 'Select P.ID_no ,zv.ZoneIn from ZonesVisited zv
Join Positions P on zv.PositionID = p.ID
Join Individuals I on I.ID_no = P.ID_no
Where
zv.zoneout is null and
(Convert(DateTime,[zonein],103) > convert(dateTime,' + @DateFrom + ',103)) and
(Convert(DateTime,[zonein],103) < convert(dateTime,' + @DateTo +',103))'
You need to have the @dateto/from values outside the quotes, or the text will be considered part of the dynamic query you are building
September 19, 2008 at 1:45 pm
Jip i know the @date must be outside the '.
@wherestring = ' and company = ''Ntiro'' '
I hope that you can help me after this post 😉
Why do people point to their wrists when asking for the time, but don't point to their crotch when they ask where the bathroom is?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply