September 20, 2010 at 10:36 am
I have a function called F_TBL_VALS_FROM_STRING this function returns a table and uses '|' as a delimiter to distinguish between values.
this function is used in a procedure (bellow), while I've tested this function and it returns the requested values, using it in this stored procedure does not work.
ALTER PROCEDURE dbo.mytestproc
(
@doorList varchar(4000),
--From Date
@FromDateYear int,
@FromDateMonth int,
@FromDateDay int,
--To Date
@ToDateYear int,
@ToDateMonth int,
@ToDateDay int,
--From Time
@FromTimeHour int,
@FromTimeMinute int,
@FromTimeSecond int,
--To Time
@ToTimeHour int,
@ToTimeMinute int,
@ToTimeSecond int
)
AS
begin
SET NOCOUNT ON
Declare @SQLScript varchar(max)
set @SQLSCRIPT=
'select t1.EventTime, t1.Source, t1.UserID, t1.EventType, t1.EventTypeName, t1.DoorName, t1.Fname, t1.Lname, t1.Date, t1.Time
from (
select convert(varchar,Events.EventTime,111)as Date, convert(varchar,Events.EventTime,108)As Time ,
EventTypes.EventTypeName,Doors.DoorName, Users.Fname, Users.Lname, Events.EventTime,
Events.Source, Events.Source as VAL, Events.UserID, Events.EventType
from Events inner join EventTypes on Events.EventType=EventTypes.EventTypeID inner join
Doors on Doors.DoorID=Events.Source inner join Users on Users.UserID = Events.UserID inner join
dbo.F_TBL_VALS_FROM_STRING(' + @usersList + ') us on us.VAL = Events.UserID inner join
dbo.F_TBL_VALS_FROM_STRING(' + @eventTypeList + ') etl on etl.VAL = Events.EventType inner join
dbo.F_TBL_VALS_FROM_STRING(' + @doorList + ') dl on dl.VAL = Events.Source
where (EventTime >= dbo.Date(' + convert(nvarchar(5),@FromDateYear) + ',' + convert(nvarchar(3),@FromDateMonth) + ',' + convert(nvarchar(3),@FromDateDay) + ')) AND
(EventTime <= dbo.Date(' + convert(nvarchar(5),@ToDateYear) + ',' + convert(nvarchar(3),@ToDateMonth) + ',' + convert(nvarchar(3),@ToDateDay) + ')) AND
dbo.Time(DATEPART(HOUR,Events.EventTime),DATEPART(minute,Events.EventTime),DATEPART(second,Events.EventTime))
>= dbo.Time(' + convert(nvarchar(3),@FromTimeHour) + ',' + convert(nvarchar(3),@FromTimeMinute) + ',' + convert(nvarchar(3),@FromTimeSecond) + ') AND
dbo.Time(DATEPART(HOUR,Events.EventTime),DATEPART(minute,Events.EventTime),DATEPART(second,Events.EventTime))
<= dbo.Time(' + convert(nvarchar(3),@ToTimeHour) + ',' + convert(nvarchar(3),@ToTimeMinute) + ',' + convert(nvarchar(3),@ToTimeSecond) + ')
) as t1
where Source IN (select * from dbo.F_TBL_VALS_FROM_STRING(' + @doorList + '))
group by t1.EventTime, t1.Source, t1.UserID, t1.EventType, t1.EventTypeName, t1.DoorName, t1.Fname, t1.Lname, t1.Date, t1.Time
order by t1.EventTime desc, t1.Fname asc, t1.Lname asc'
EXEC(@SQLScript)
end
Go
the stored procedure works fine and returns values if i don't call the function
I've used this to test the procedure:
declare @eventTypeList varchar(4000), @doorList varchar(4000), @usersList varchar(4000)
set @doorList = '1|2|3|8|4|5|6|9|7|10|11'
set @eventTypeList = '1|2|3|8|4|5|6|9|7|10|11|12|13|14|15|16'
set @usersList = '1|2|3|8|4|5|6|9|7|10|11'
exec dbo.mytestproc @doorList ,@eventTypeList ,@usersList , 2010 ,6 ,1 ,2010, 9, 2 ,1 ,2 ,3 ,23 ,47 ,53
September 20, 2010 at 2:34 pm
Are you getting any errors?
Can you post the code for the function?
Have you tried doing a print or select of @SQLStr and then run that? I'm guessing you need to wrap your delimited strings in quotes:
'Select * From dbo.F_TBL_VALS_FROM_STRING(''' + @usersList + ''')'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 1:25 am
I get no errors and i don't know how to do a print of @SQLStr (I'll be happy to learn).
my function code is posted bellow
ALTER function F_TBL_VALS_FROM_STRING
(@p_str varchar(4000))
returns @tbl table (VAL int)
as
begin
set @p_str = @p_str + '|'
declare @p_counter int
declare @p_len int
declare @p_curr_char varchar(1)
declare @p_char varchar(6)
declare @p_num int
set @p_len = len(@p_str)
set @p_counter = 1
set @p_curr_char = ''
set @p_char = ''
-- loop
while @p_counter <= @p_len
begin
set @p_curr_char = substring(@p_str, @p_counter, 1)
if (@p_curr_char <> '|')
begin
set @p_char = @p_char + @p_curr_char
end
else
begin
set @p_num = convert(int, @p_char)
insert into @tbl values(@p_num)
set @p_char = ''
end
set @p_counter = @p_counter + 1
end
return
end
GO
September 21, 2010 at 2:29 am
just got the solution in another place, it should have been:
dbo.F_TBL_VALS_FROM_STRING(''' + @doorList + ''')
instead of
dbo.F_TBL_VALS_FROM_STRING(' + @doorList + ')
September 21, 2010 at 4:59 am
yonatam (9/21/2010)
just got the solution in another place, it should have been:dbo.F_TBL_VALS_FROM_STRING(''' + @doorList + ''')
instead of
dbo.F_TBL_VALS_FROM_STRING(' + @doorList + ')
That's exactly the solution I gave.
Whenever you are working with dynamic SQL you should do:
PRINT @SQLStr
This will output the string to the Messages tab in SSMS/Query Analyzer
or
SELECT @SQLStr
This will output the string to the Results tab
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 8:23 pm
Funny part - you don't need dynamic SQL at all.
This should work with no problems (if you add parameters @usersList and @eventTypeList to the procedure 🙂 )
ALTER PROCEDURE dbo.mytestproc
(
@doorList varchar(4000),
--From Date
@FromDateYear int,
@FromDateMonth int,
@FromDateDay int,
--To Date
@ToDateYear int,
@ToDateMonth int,
@ToDateDay int,
--From Time
@FromTimeHour int,
@FromTimeMinute int,
@FromTimeSecond int,
--To Time
@ToTimeHour int,
@ToTimeMinute int,
@ToTimeSecond int
)
AS
SET NOCOUNT ON
Declare @FromDate datetime, @ToDate datetime
SET @FromDate = convert(nvarchar(5),@FromDateYear) + ',' + convert(nvarchar(3),@FromDateMonth) + ',' + convert(nvarchar(3),@FromDateDay)
SET @ToDate = convert(nvarchar(5),@ToDateYear) + ',' + convert(nvarchar(3),@ToDateMonth) + ',' + convert(nvarchar(3),@ToDateDay) )
SET @FromTime = convert(nvarchar(5),@FromTimeHour) + ',' + convert(nvarchar(3),@FromTimeMinute) + ',' + convert(nvarchar(3),@FromTimeSecond)
SET @ToTime = convert(nvarchar(5),@ToTimeHour) + ',' + convert(nvarchar(3),@ToTimeMinute) + ',' + convert(nvarchar(3),@ToTimeSecond) )
select t1.EventTime, t1.Source, t1.UserID, t1.EventType, t1.EventTypeName, t1.DoorName, t1.Fname, t1.Lname, t1.Date, t1.Time
from (
select convert(varchar,Events.EventTime,111)as Date, convert(varchar,Events.EventTime,108)As Time ,
EventTypes.EventTypeName,Doors.DoorName, Users.Fname, Users.Lname, Events.EventTime,
Events.Source, Events.Source as VAL, Events.UserID, Events.EventType
from Events inner join EventTypes on Events.EventType=EventTypes.EventTypeID inner join
Doors on Doors.DoorID=Events.Source inner join Users on Users.UserID = Events.UserID inner join
dbo.F_TBL_VALS_FROM_STRING(@usersList) us on us.VAL = Events.UserID inner join
dbo.F_TBL_VALS_FROM_STRING(@eventTypeList) etl on etl.VAL = Events.EventType inner join
dbo.F_TBL_VALS_FROM_STRING(@doorList) dl on dl.VAL = Events.Source
where (EventTime >= dbo.Date(@FromDate) AND
(EventTime <= dbo.Date(@ToDate) AND
dbo.Time(DATEPART(HOUR,Events.EventTime),DATEPART(minute,Events.EventTime),DATEPART(second,Events.EventTime))
>= dbo.Time(@FromTime) AND
dbo.Time(DATEPART(HOUR,Events.EventTime),DATEPART(minute,Events.EventTime),DATEPART(second,Events.EventTime))
<= dbo.Time(@ToTime)
) as t1
where Source IN (select * from dbo.F_TBL_VALS_FROM_STRING(@doorList))
group by t1.EventTime, t1.Source, t1.UserID, t1.EventType, t1.EventTypeName, t1.DoorName, t1.Fname, t1.Lname, t1.Date, t1.Time
order by t1.EventTime desc, t1.Fname asc, t1.Lname asc
Go
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply