Stored procedure and table value function

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored procedure and table value function

Author

Topic

StateOfMind
Starting Member

3 Posts

Posted - 2010-09-20 : 12:35:54

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.

quote:

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:

quote:

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

Sachin.Nand

2937 Posts

Posted - 2010-09-20 : 13:06:45

Please post the stored proc code.

PBUH

StateOfMind
Starting Member

3 Posts

Posted - 2010-09-20 : 15:35:44

the code is posted and starts with "alter procedure"

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 15:46:36

my first reaction was to shudder....

WHY are you using dynamic SQL

WHY are your dateparts stored as int?

I think s/he meant, post the function code

Do a PRINT on @SQLScript instead of execute to see what it looks like

Also..posting the actual error helps us as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

StateOfMind
Starting Member

3 Posts

Posted - 2010-09-21 : 02:29:43

Hi Brett,
first,I'm new to stored procedures so I've followed an example Ive found online therefor you're more then invited to correct me (does it mean you shuddered because dynamic sql effects to much on performance issues).
second, the DateTime is passed as an int from the outside due to the fact that i found myself stuck with date and time issues when running a query (European vs. American) and this was my way out.
and third, my function is posted bellow, but i don't understand what you mean in "PRINT on @SQLScript instead of execute"


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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources