SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to replace this cursor with set based solution?


how to replace this cursor with set based solution?

Author
Message
sqldriver
sqldriver
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 2519
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.
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1114
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.
sqldriver
sqldriver
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1018 Visits: 2519
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.
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1114
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.
Kurt W. Zimmerman
Kurt W. Zimmerman
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1446 Visits: 1398
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
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1114
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27216 Visits: 17557
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 Modens 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)
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1114
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.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27216 Visits: 17557
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 Modens 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)
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 1114
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;

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search