where kolumn in () i lagrad procedur

2 alternativ

a) Dynamisk SQL

exec('SELECT bla...
FROM bla...
WHERE Usergroupid In (' + @Usergroupid +')')

b) Dela upp strängen och lagra den i en tabellvariabel som du gör join med.

hmm får bara detta

Syntax error converting the varchar value '+ @Usergroupid +' to a column of data type int.

fortfarande problem, vad kan det vara som är fel tror ni?
kan man lösa det med en raplace tro?

where Usergroupid In (replace(@Usergroupid,'.',',')

exec
sp_timeline_activity 3.2

ovanstående får jag heller inte att funka, kanske inte går heller.

Hur ser koden ut? Inte den med replace.

Här kommer hela koden!
Det vore otroligt fiffigt om det går att lösa. :)

CREATE PROCEDURE sp_timeline_activity
@Objectid int, 
@Areaid int, 
@First_date datetime, 
@Last_date datetime, 
@Usergroupid int
AS
SELECT Ob.Objectid, Ob.Objectname, Ar.Areaid, Ar.Areaname, Pl.Placeid, 
Pl.Placename, Ta.Taskid, Ta.Description, Ta.Method, Ta.Equipment, Ta.Chemicals, 
Ta.Tasktype, Ta.Intervaltype, Ta.Startdate, Ta.Stopdate, Ta.Notes AS Ta_notes, 
Ta.Currentnr, Ta.Author, Ta.Shift, Ta.Active, Ta.Regdate, U.Userid, 
U.Username, U.Fullname, S.Signid, S.Notes, S.Author, 
S.Complete, S.Regdate, M.Measureid, M.Notes, M.Author, M.Regdate

FROM Objecttbl Ob INNER JOIN Areatbl Ar ON Ar.Objectid = Ob.Objectid 
INNER JOIN Placetbl Pl ON Pl.Areaid = Ar.Areaid 
INNER JOIN Tasktbl Ta ON Ta.Areaid = Ar.Areaid
INNER JOIN Taskrelationtbl Tr ON Ta.Taskid = Tr.Taskid
INNER JOIN Usertbl U ON U.Userid = Ta.Userid
Left JOIN Signtbl S ON Ta.Taskid = S.Taskid
Left Join Measuretbl M ON S.Signid = M.Signid

WHERE Ob.Objectid = @Objectid And coalesce(@Areaid, Ar.Areaid) = Ar.Areaid
And Ta.Tasktype = 0 And Ta.Active = 1 And Year(Ta.Startdate) <= Year(@First_date) 
And Ta.Startdate <= @Last_date  And Stopdate >= @First_date
And Tr.Usergroupid In ('+Usergroupid+')
GO

exec
sp_timeline_activity 1, null, '2002-10-01', '2002-10-30', '2,3,5'

CREATE PROCEDURE sp_timeline_activity
@Objectid int, 
@Areaid int, 
@First_date datetime, 
@Last_date datetime, 
@Usergroupid [b] varchar(40) [/b]
AS
[b]exec('[/b]
SELECT Ob.Objectid, Ob.Objectname, Ar.Areaid, Ar.Areaname, Pl.Placeid, 
Pl.Placename, Ta.Taskid, Ta.Description, Ta.Method, Ta.Equipment, Ta.Chemicals, 
Ta.Tasktype, Ta.Intervaltype, Ta.Startdate, Ta.Stopdate, Ta.Notes AS Ta_notes, 
Ta.Currentnr, Ta.Author, Ta.Shift, Ta.Active, Ta.Regdate, U.Userid, 
U.Username, U.Fullname, S.Signid, S.Notes, S.Author, 
S.Complete, S.Regdate, M.Measureid, M.Notes, M.Author, M.Regdate
FROM Objecttbl Ob INNER JOIN Areatbl Ar ON Ar.Objectid = Ob.Objectid 
INNER JOIN Placetbl Pl ON Pl.Areaid = Ar.Areaid 
INNER JOIN Tasktbl Ta ON Ta.Areaid = Ar.Areaid
INNER JOIN Taskrelationtbl Tr ON Ta.Taskid = Tr.Taskid
INNER JOIN Usertbl U ON U.Userid = Ta.Userid
Left JOIN Signtbl S ON Ta.Taskid = S.Taskid
Left Join Measuretbl M ON S.Signid = M.Signid
WHERE Ob.Objectid = @Objectid And coalesce(@Areaid, Ar.Areaid) = Ar.Areaid
And Ta.Tasktype = 0 And Ta.Active = 1 And Year(Ta.Startdate) <= Year(@First_date) 
And Ta.Startdate <= @Last_date  And Stopdate >= @First_date
And Tr.Usergroupid In [b](' + @Usergroupid + ')' ) [/b]
GO

Hmm, skriver man nått speciellt när man hämtar den sedan?
Får detta: Must declare the variable '@Objectid'.

Jo, jag såg inte dom

CREATE PROCEDURE sp_timeline_activity
@Objectid varchar(10), 
@Areaid varchar(10), 
@First_date varchar(20), 
@Last_date varchar(20), 
@Usergroupid  varchar(40) 
AS
exec('
SELECT Ob.Objectid, Ob.Objectname, Ar.Areaid, Ar.Areaname, Pl.Placeid, 
Pl.Placename, Ta.Taskid, Ta.Description, Ta.Method, Ta.Equipment, Ta.Chemicals, 
Ta.Tasktype, Ta.Intervaltype, Ta.Startdate, Ta.Stopdate, Ta.Notes AS Ta_notes, 
Ta.Currentnr, Ta.Author, Ta.Shift, Ta.Active, Ta.Regdate, U.Userid, 
U.Username, U.Fullname, S.Signid, S.Notes, S.Author, 
S.Complete, S.Regdate, M.Measureid, M.Notes, M.Author, M.Regdate
FROM Objecttbl Ob INNER JOIN Areatbl Ar ON Ar.Objectid = Ob.Objectid 
INNER JOIN Placetbl Pl ON Pl.Areaid = Ar.Areaid 
INNER JOIN Tasktbl Ta ON Ta.Areaid = Ar.Areaid
INNER JOIN Taskrelationtbl Tr ON Ta.Taskid = Tr.Taskid
INNER JOIN Usertbl U ON U.Userid = Ta.Userid
Left JOIN Signtbl S ON Ta.Taskid = S.Taskid
Left Join Measuretbl M ON S.Signid = M.Signid
WHERE Ob.Objectid = ' + @Objectid + ' And coalesce(' + @Areaid + ', Ar.Areaid) = Ar.Areaid
And Ta.Tasktype = 0 And Ta.Active = 1 And Year(Ta.Startdate) <= Year(''' + @First_date + ''') 
And Ta.Startdate <= ''' + @Last_date + ''' And Stopdate >= ''' + @First_date + '''
And Tr.Usergroupid In (' + @Usergroupid + ')' ) 
GO

Tipp topp! :bire
Finns det nån möjlighet att om man skickar in ett tomt/null värde
till @Usergroupid så visas samtliga Tr.Usergroupid precis som
coalesce fungerar på Ar.Areaid?

CREATE PROCEDURE sp_timeline_activity
@Objectid varchar(10), 
@Areaid varchar(10), 
@First_date varchar(20), 
@Last_date varchar(20), 
@Usergroupid  varchar(40) 
AS
exec('
SELECT Ob.Objectid, Ob.Objectname, Ar.Areaid, Ar.Areaname, Pl.Placeid, 
Pl.Placename, Ta.Taskid, Ta.Description, Ta.Method, Ta.Equipment, Ta.Chemicals, 
Ta.Tasktype, Ta.Intervaltype, Ta.Startdate, Ta.Stopdate, Ta.Notes AS Ta_notes, 
Ta.Currentnr, Ta.Author, Ta.Shift, Ta.Active, Ta.Regdate, U.Userid, 
U.Username, U.Fullname, S.Signid, S.Notes, S.Author, 
S.Complete, S.Regdate, M.Measureid, M.Notes, M.Author, M.Regdate
FROM Objecttbl Ob INNER JOIN Areatbl Ar ON Ar.Objectid = Ob.Objectid 
INNER JOIN Placetbl Pl ON Pl.Areaid = Ar.Areaid 
INNER JOIN Tasktbl Ta ON Ta.Areaid = Ar.Areaid
INNER JOIN Taskrelationtbl Tr ON Ta.Taskid = Tr.Taskid
INNER JOIN Usertbl U ON U.Userid = Ta.Userid
Left JOIN Signtbl S ON Ta.Taskid = S.Taskid
Left Join Measuretbl M ON S.Signid = M.Signid
WHERE Ob.Objectid = ' + @Objectid + ' And coalesce(' + @Areaid + ', Ar.Areaid) = Ar.Areaid
And Ta.Tasktype = 0 And Ta.Active = 1 And Year(Ta.Startdate) <= Year(''' + @First_date + ''') 
And Ta.Startdate <= ''' + @Last_date + ''' And Stopdate >= ''' + @First_date + '''
And Tr.Usergroupid In (' + coalsece(@Usergroupid,'Tr.Usergroupid')  
+ ')' ) 
GO

incorrect syntax near 'coalesce'
(coalesce blir färgad blå, det ska den väl inte bli i detta läge, eller?)

And Tr.Usergroupid In (' + coalesce(@Usergroupid,'Tr.Usergroupid')  + ')')

Jaså, den illfänas

And Tr.Usergroupid In (' + cast(coalesce(@Usergroupid,'Tr.Usergroupid') as varchar(30))  + ')')

kanske?

grr, den lilla lymmen är på bråkhumör!
incorrect syntax near 'cast'

Om du gör så här då

CREATE PROCEDURE sp_timeline_activity
@Objectid varchar(10), 
@Areaid varchar(10), 
@First_date varchar(20), 
@Last_date varchar(20), 
@Usergroupid  varchar(40) 
AS
declare @m varchar(40)

set @m = coalesce(@Usergroupid,'Tr.Usergroupid')

och sedan

And Tr.Usergroupid In (' + @m + ')')

Ha! Världsklass LarsG! :bire

(Suck! tänk när jag blir så där duktig...) :stud

Gott, men att förra varianten inte fungerade, skulle jag nog kalla för ett fel i SQL server.