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
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2234 Visits: 2536
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3150 Visits: 1257
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.

--Quote me
sqldriver
sqldriver
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2234 Visits: 2536
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3150 Visits: 1257
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.

--Quote me
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2652 Visits: 1400
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3150 Visits: 1257
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?

--Quote me
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63612 Visits: 17966
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3150 Visits: 1257
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.

--Quote me
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63612 Visits: 17966
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

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


--Quote me
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