September 27, 2016 at 3:40 pm
Hello,
I have a function, where I am planning to check the performance statistics like start and end time.
I want to test with 10,000 rows. Pass some random values and call them 10 times and use it in join table and call again?
Is there a way to create cursor to do this?
FUNCTION dbo.Blabla
(
@DE1 Varchar(100),
@DE2 Varchar(100)
)
RETURNS TABLE
AS
RETURN
(
SELECT
substring(CAST(COL1 AS VARCHAR(20)),1,CAST(CHARINDEX('-',COL1) AS VARCHAR(20)) -1) AS ,
substring(COL1,CHARINDEX('-',COL1)+1,(Len(COL1))) AS [lCode]
FROM [Emp] A
Where DE1=@DE1 and DE2=@DE2
AND DT = (Select MAX(DT) From dbo.Emp B Where A.DE1=B.DE1 and A.DE2=B.DE2)
)
September 28, 2016 at 12:54 am
Sorry, but it is unclear to me what you're trying to do.
Please post table scripts, sample data and expected output.
See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.
-- Gianluca Sartori
September 28, 2016 at 12:42 pm
You could populate a table with the test values you want to us, then process from it in a loop or by using GO <#_of_test_runs>, which I used below just for the heck of it :).
CREATE TABLE #test_run_values (
run# int PRIMARY KEY,
DE1 varchar(100) NOT NULL,
DE2 varchar(100) NOT NULL,
has_been_processed bit NOT NULL
)
--populate this table however you need to to get the
--"random" values you want
TRUNCATE TABLE #test_run_values
INSERT INTO #test_run_values
VALUES(1, 'A', 'B', 0),
(2, 'C', 'D', 0)
SELECT * FROM #test_run_values
--the GO below **MUST** be present.
GO
DECLARE @run# int
DECLARE @DE1 varchar(100)
DECLARE @DE2 varchar(100)
SELECT TOP (1) @run# = run#, @DE1 = DE1, @DE2 = DE2
FROM #test_run_values
WHERE has_been_processed = 0
ORDER BY run#
SELECT @DE1 AS DE1, @DE2 AS DE2
UPDATE #test_run_values
SET has_been_processed = 1
WHERE run# = @run#
SELECT *
FROM dbo.BlaBla(@DE1, @DE2)
GO 2
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply