Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

how to replace this cursor with set based solution? Expand / Collapse
Author
Message
Posted Friday, September 27, 2013 8:33 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 443, Visits: 1,792
I vaguely recall running into that error when creating a view. In my case adding dbo. to the table name fixed it. So in your case dbo.Runs. Hope this works for you.
Post #1499427
Posted Friday, September 27, 2013 10:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
well, then I had to comment out WITH SCHEMABINDING, because try as I might, to do what the message wanted, it was still same error.
If you can show me how to use proper naming convention, as in point out where exactly problem is, I would appreciate.
See, I have added the schema dbo...
CREATE FUNCTION GetBench (@bench varchar(20))
RETURNS TABLE
--WITH SCHEMABINDING
AS RETURN
SELECT r.createdate
FROM Sandbox.dbo.Runs r
INNER JOIN Sandbox.dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN Sandbox.dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@bench + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());



ps. I recall now that schemabinding ensures that you can't modify underlyling tables without re-creating the dependent functions and views.
Post #1499511
Posted Friday, September 27, 2013 10:41 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 443, Visits: 1,792
polkadot (9/27/2013)
well, then I had to comment out WITH SCHEMABINDING, because try as I might, to do what the message wanted, it was still same error.
If you can show me how to use proper naming convention, as in point out where exactly problem is, I would appreciate.
See, I have added the schema dbo...
CREATE FUNCTION GetBench (@bench varchar(20))
RETURNS TABLE
--WITH SCHEMABINDING
AS RETURN
SELECT r.createdate
FROM Sandbox.dbo.Runs r
INNER JOIN Sandbox.dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN Sandbox.dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@bench + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());



ps. I recall now that schemabinding ensures that you can't modify underlyling tables without re-creating the dependent functions and views.


It requires two part notation. So one or three won't work. It should just be dbo.Runs, I believe. I'd have to dig through some emails to find where I had the same problem and exactly what fixed it. But give that a shot.

Post #1499514
Posted Friday, September 27, 2013 11:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
to be honest, I did, but I'm not going to battle that anymore. As long as commenting out the With Schemabinding allows me to continue with the APPLY 'lesson' I'm fine with that.
Post #1499523
Posted Friday, September 27, 2013 12:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
Luis Cazares (9/26/2013)
Be careful on using table variables, they might reduce performance on your query depending on the number of rows. You should test what's better for your performance (temp table, table variable, other) and consider pros and cons.
Here's an article on the subject: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/


I agree with this. When developing a solution and the choice of table variables vs temp tables comes into question then it is best to know how much data you are dealing with to choose the right path.

Just all part of the development process.


Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1499540
Posted Friday, September 27, 2013 1:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
is it possible to put the right side of the OUTER APPLY into a TVF, given that the input values are coming from an array type variable?
Post #1499569
Posted Friday, September 27, 2013 1:34 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
polkadot (9/27/2013)
is it possible to put the right side of the OUTER APPLY into a TVF, given that the input values are coming from an array type variable?


Sure. I assume you mean you need to parse this parameter? Take a look at the link in my signature about splitting strings.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1499574
Posted Friday, September 27, 2013 1:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
before I go there, I want to clarify. The parameter @benches is called out in the left query as well as the right.
I tried this:
created tvf
CREATE FUNCTION GetBench (@benches varchar(20))
RETURNS TABLE
AS RETURN
SELECT r.createdate
FROM Sandbox.dbo.Runs r
INNER JOIN Sandbox.dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN Sandbox.dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@benches + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());



incorporated in APPLY
DECLARE @benches TABLE (Bench varchar(20));

INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');

INSERT INTO RunsByBench
SELECT Bench AS BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches
OUTER APPLY
GetBench (@benches)
group by Bench;

select * from RunsByBench;



got error:
Msg 206, Level 16, State 2, Line 10
Operand type clash: table is incompatible with varchar


So, you say Jeff Moden's splitter article contains the answer, yes? I'm not ready for that article unless it addresses this problem. I currently can make no sense of it and it's long. If you can provide more pointed guidance to whether I should be able to encapsulate the right query into TVF, given that it relies on the same variable as the left side of query, I would appreciate.
Post #1499577
Posted Friday, September 27, 2013 2:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
polkadot (9/27/2013)
before I go there, I want to clarify. The parameter @benches is called out in the left query as well as the right.
I tried this:
created tvf
CREATE FUNCTION GetBench (@benches varchar(20))
RETURNS TABLE
AS RETURN
SELECT r.createdate
FROM Sandbox.dbo.Runs r
INNER JOIN Sandbox.dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN Sandbox.dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@benches + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());



incorporated in APPLY
DECLARE @benches TABLE (Bench varchar(20));

INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');

INSERT INTO RunsByBench
SELECT Bench AS BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches
OUTER APPLY
GetBench (@benches)
group by Bench;

select * from RunsByBench;



got error:
Msg 206, Level 16, State 2, Line 10
Operand type clash: table is incompatible with varchar


So, you say Jeff Moden's splitter article contains the answer, yes? I'm not ready for that article unless it addresses this problem. I currently can make no sense of it and it's long. If you can provide more pointed guidance to whether I should be able to encapsulate the right query into TVF, given that it relies on the same variable as the left side of query, I would appreciate.


The problem here is that you are mixing a table and a scalar value.

First you create your iTVF GetBench. It receives a varchar(20) as the only parameter. However, in your query you are mixing the references.

I would recommend making this a bit simpler until you get the hard parts ironed out. Specifically, don't do an insert until you get the select working. Drop all the grouping and just pull select *.

Let's start here:

DECLARE @benches TABLE (Bench varchar(20));

INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');

SELECT *
FROM @benches

So far so good. You have a table variable, it is populated with some rows and you can select them.

So now you want to get some additional results by joining to your iTVF. However, in the code you posted you are passing it a table variable, this is NOT a varchar(20) as defined by your function.

I think that you want to pass a single row's value here but I am not entirely sure.

That would be something like this.

SELECT *
FROM @benches b
OUTER APPLY dbo.GetBench(b.Bench)

Does that help?

From your earlier post you could change your function to use schema binding by simply using 2 part naming instead of 3...

CREATE FUNCTION GetBench 
(
@benches varchar(20)
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN

SELECT r.createdate
FROM dbo.Runs r
INNER JOIN dbo.runs_machines rm ON rm.runid = r.runid
INNER JOIN dbo.Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@benches + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate());



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1499588
Posted Friday, September 27, 2013 5:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
Very nice of you Sean.
Aliased the table variable, and referenced the values in the table variable with column name.

worked:

DECLARE @benches TABLE (Bench varchar(20));

INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');

INSERT INTO RunsByBench
SELECT
Bench as BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches b
OUTER APPLY dbo.GetBench(b.bench)
group by Bench;
Post #1499621
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse