T-SQL (SS2K8) :: Convert Cursor To Recursive CTE Or A Normal Query?
Sep 25, 2015
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50)
AS
BEGIN
-- EXEC [USPT] 'xyz corp','Sales Header'
DECLARE @str1 VARCHAR (MAX)
set @str1 = '
DECLARE @No VARCHAR (MAX)
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400) declare @str nvarchar(MAX) TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry] DECLARE GLEntry_cursor CURSOR FOR SELECT REPLACE Name FROM Company where Name <> 'AAAAA' OPEN GlEntry_cursor
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
id name ----- ------------------------------------------ 1 My top parent node 2 My second node 3 my child node
If I do a search for say 'my child node' I need to display where 'my child node' is in relation to the hierarchy. i.e i need to show it's parent and if that has a parent I need to show its parent etc... and continue until there are no more parents left
So using the table details if i search for 'my child node'
I need to display this : My top parent node -> My second node - > my child node
The id for 'My top parent node' doesn't exist in tblparent because it is the top parent
I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point: (id, parentId, label) 0, null, World 1, 0, US 2, 1, NY 3, 0, UK 4, 3, London
now i want to copy object 3 (UK) and it's children, so i would get 0, null, World 1, 0, US 2, 1, NY 3, 0, UK 4, 3, London 5, 0, UK_copy 6, 5, London_copy
I have this sproc:
Code Snippet
alter proc CopyObject
(@ObjectId int,
@NewParentId int)
as
declare @NewId int,
@NewName varchar
select @NewId = max(Id) + 1 from Object
select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId
-- copy object
INSERT INTO [Object]
([Id]
,[Name]
,[ParentId]
select @NewId,
@NewName,
@NewParentId
from [Object]
where Id = @ObjectId
-- copy children and set their parent to the newly created object
declare c cursor fast_forward for
select Id
from [Object]
where ParentId = @ObjectId
declare @ChildId int
open c
fetch next from c into @ChildId
while @@fetch_status = 0
begin
exec CopyObject
@ObjectID = @ChildId,
@NewParentId = @NewId
fetch next from c into @ChildId
end
close c
deallocate c
But htis throws an error that the cursor already exists:
Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66
A cursor with the name 'c' already exists.
Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72
The cursor is already open.
I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.
I've been running a long query which takes almost 39 seconds in Query Analyzer. After creating a Stored Procedure (with the same query) I expected to run it faster bcoz I heared that SP has a cache, and its a faster technique. But I didnt gain any performance improvments.
Can somebody clear my confusion, what I'm doing wrong.
hi friends the below query is actually what type of join whether inner join or normal query..?????
if not exists(select 'x' from cobi_invoice_hdr h(nolock), fin_quick_code_met q(nolock) , ci_adjustment_drdoc_vw z (nolock) where h.tran_ou = @ctxt_ouinstance and h.invoce_cat = @category_tmp and d.so_no between @sonumberfrom and @sonumberto
and isnull(h.tran_amount,0) between @totalinvoiceamountfrom and @totalinvoiceamountto and h.tran_date between convert(varchar(10),@invoicedatefrom,120)and convert(varchar(10),@fininvoicedateto,120) and h.tran_no between @invoicenumberfrom and @invoicenumberto and h.bill_to_cust between @billtocodefrom and @customerto and h.fb_id = isnull(@fb,h.fb_id) and h.tran_currency = isnull(@currency,h.tran_currency) and h.createdby = isnull(@useridentity,h.createdby)
and EXISTS (select '*' from cobi_cust_custinfo_vw c(nolock) where h.bill_to_cust = c.custcode andc.ouid = @ou_tmp )
and z.status = q.parameter_text
and q.parameter_type = 'STATUS' and q.parameter_category = 'STATUS' and q.component_id = 'COBI' and q.parameter_code = @status_tmp and h.tran_no = z.documentno and q.language_id = @ctxt_language and z.language_id = @ctxt_language) begin 'No matching invoices found.' select @m_errorid = 514 -- Porselvi.J - COBIDMS412AT_000255 return end End
I am new to SQL and have created a stored procedure for a .net webapplication. Unfortunately I had to use a cursor in the storedprocedure, so it is taking several minutes to execute because it has toread through about 15,000 records. There must be another way to dowhat I'm trying to do without a cursor. I've tried temp tables andcase statements, but I can't seem to get them to work. I've beentrying to figure this out for over a week and I am just running into awall. Some expert advise would be much appreciated. My code is below.Thank you in advance.--Insert records into first temp tableDECLARE @tempA TABLE(lnkey varchar(10),AuditorIDvarchar(7))INSERT INTO @tempASELECTLNKEY,AuditorIDFROMdbo.tblALPSLoansWHERE AuditDate BETWEEN @BegDate AND @EndDate --parameters from myapplicationAND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE ='ADDED')AND AuditType = @AuditType --parameter from my application--Insert percentage value of Pre-Funding completes for each auditorinto temp table BDECLARE @tempB TABLE(LnkeyCount int,AuditorIDvarchar(7))INSERT INTO @tempBSELECTROUND(COUNT(LNKEY) * @Percent/100, 0) AS 'LnkeyCount',AuditorIDFROM dbo.tblALPSLoansWHERE AuditDate BETWEEN @BegDate AND @EndDateAND AuditorID IN (SELECT LANID FROM dbo.tblEmployees WHERE ACTIONTYPE ='ADDED')GROUP BY AuditorID/*Create cursor to loop through records and add a loan number totblinjectloans if the number of loans in tblinjectloans for eachauditor is less than the percentage value for each auditor from@tempB*/DECLARE @lnkey varchar(10)DECLARE @AuditorID varchar(7)DECLARE @var1intDECLARE @var2intDECLARE @sqlvarchar(4000)DECLARE c1 CURSOR FORSELECT lnkey, auditoridFROM @TempAOPEN c1FETCH NEXT FROM c1INTO @LNKEY, @AuditorIDWHILE @@FETCH_STATUS = 0BEGINSelect @var1 = COUNT(Lnkey) from dbo.tblInjectLoans whereAuditorID=@AuditorIDSelect @var2 = LnkeyCount from @tempB where AuditorID=@AuditorIDIF @var1 < @var2Insert into dbo.tblInjectLoans(lnkey, AuditorID)Values (@LNKEY, @AuditorID)FETCH NEXT FROM c1INTO @LNKEY, @AuditorIDENDCLOSE c1DEALLOCATE c1
DECLARE @id VARCHAR(10) DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT [ServersList] AS 'ID' FROM dbo.Servers
[code]...
How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.
Hi All there - I want to show the o/p of a cursor on a single line. There is a numeric variable that needs to be clubed with the character variable. If I use char() the o/p is not right. How do I do that?
I am using a cursor (i know - but this is actually something that is a procedural loop).
So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.
Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?
DECLARE @SOPCursor AS CURSOR; SET @SOPCursor = CURSOR FOR
[Code] ....
The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.
Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.
Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.
I have another table that houses the primary key and the field of the record that cannot convert.
Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.
I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated.
Summary * The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats. * As far as I know a fetch-next-statement always returns a single row? * Using a static cursor works as aspected. * Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function create partition function fnTestPartition01( smallint ) as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL DROP TABLE #tTable CREATE TABLE #tTable
I am trying to think of a way to read a control table, build the SQL statement for each line, and then execute them all, without using a cursor.
To make it simple... control table would look like this:
CREATE TABLE [dbo].[Control_Table]( [Server_Name] [varchar](50) NOT NULL, [Database_Name] [varchar](255) NOT NULL, CONSTRAINT [PK_Control_Table] PRIMARY KEY CLUSTERED
[Code] ....
So if we then load:
insert into zt_Planning_Models_Plant_Include_Control_Table values ('r2d2','planing1'), ('r2d2','planing7'), ('deathstar','planing3')
Then you would build a SQL script that would end up looking like the following (note all the columns are the same):
insert into master_models Select * from r2d2.planning1.dbo.models insert into master_models select * from r2d2.planning7.dbo.models insert into master_models Select * from deathstar.planning3.dbo.models
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4, The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3, The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3, The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Desired output (no trailing comma):
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4 The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3 The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3 The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Next, how do I call the stored procedure without doing it RBAR? Is that possible?
Article(articleID,CategoryID,ArticleTitle) Categories(categoryID,ParentID,CategoryTitle) I am trying to retrieve the main category ID for a specific article ID. For example lets say I have this data: Article:
In this example I want to know who is the main category of article 3. The query should return the answer: 1 Thats because:
The article ID 3 is inside category 11. Parent for category 11 is 10. Parent for category 10 is 2. Parent for category 2 is 1 and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category. Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables) Do you have any suggestions for such query? Thanks all.
Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project .. .
I’m desperating due to the complex (for me and also I think for some others) sql query that I need to write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification table that holds this mentioned “custom defined fieds� according to the Classifications table, on where the Products can be found trought the productsClassifications table.
CustomFields can be defined and set for the products, trought his Classifications (instead of define a custom field for each product (that consume a lot of data), I decide to use it as I explain)
I will to know the properly SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:
As example on a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the ProductsFields and Values that has the DBA for the:
· requested ID_Classification
o PAL (ID_Classification: 16)
· AND all the Classifications that belongs above (trought ID_ParentClassification) that are :
o Torrents (ID_Classification: 1) that will show the products values for the “Size�
o Games (ID_Class..:4) ß this classification has no CustomFields so none from this one.
o Dreamcast (ID_Class..:14 ) that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)
Hmnn i show a graphic that i design for (feel to click over to see at correct resolution)
I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com
Does anyone know how to do an sql recursion queries?
I believe it involves a view with a union.
I have a User Table and in that table i have a employee_id and a boss_id. What i'd like to do is to find all employees under a certain boss. For example,
Employee_ID Boss_ID 1 2 1 3 4 3 5 2
So if i'd like to know who are under the employee_id = 1 it will return employee_id 2 and 5 since employee 2 also is the boss of employee_id = 5.
I am wondering if there is some type of recursive query to return the values I want from the following database.
Here is the setup:
The client builds reptile cages.
Each cage consists of aluminum framing, connectors to connect the aluminum frame, and panels to enclose the cages. In the example below, we are not leaving panels out to simplify things. We are also not concerned with the dimensions of the cage.
The PRODUCT table contains all parts in inventory. A finished cage is also considered a PRODUCT. The PRODUCT table is recursively joined to itself through the ASSEMBLY table.
PRODUCTS that consist of a number of PRODUCTS are called an ASSEMBLY. The ASSEMBLY table tracks what PRODUCTS are required for the ASSEMBLY.
Sample database can be downloaded from http://www.handlerassociates.com/cage_configurator.mdb
Can you please help me to write a recursive query in sql server 2000?
I have got a table WORKORDER. wonum and parent are the two columns in the table. A wonum can have children.Those children can have children, so on. if i am giving a wonum,it should display all the children ,their children and so on.
Went looking for an answer but not really sure what phrases to lookfor. Just decided to post my question.I have a collection of groups which contain items. I also have acollection of users which can be assigned permissions to both groupsand individual items. If a user has permission to a group then the userhas that permission to each of the items in the group. I need a querywhich will return all the items and permission for a particular user.Here is the code for creating the tables and populating them.CREATE TABLE [Account] ([Name] VARCHAR(10))INSERT INTO [Account] VALUES ('210')INSERT INTO [Account] VALUES ('928')INSERT INTO [Account] VALUES ('ABC')CREATE TABLE [AccountGroup] ([Name] VARCHAR(10))INSERT INTO [AccountGroup] VALUES ('Group1')INSERT INTO [AccountGroup] VALUES ('Group2')CREATE TABLE [AccountGroupMembership] ([GroupName] VARCHAR(10), [AccountName] VARCHAR(10))INSERT INTO [AccountGroupMembership] VALUES ('Group1', '210')INSERT INTO [AccountGroupMembership] VALUES ('Group1', 'ABC')INSERT INTO [AccountGroupMembership] VALUES ('Group2', '928')INSERT INTO [AccountGroupMembership] VALUES ('Group2', 'ABC')CREATE TABLE [Permission] ([User] VARCHAR(10), [Item] VARCHAR(10), [ItemType] VARCHAR(1)-- 'A' for account, 'G' for account group, [ReadPerm] INT, [WritePerm] INT)INSERT INTO [Permission] VALUES ('john', '210', 'A', 1, 0)-- readaccess to 210 accountINSERT INTO [Permission] VALUES ('john', 'Group1', 'G', 1, 1)--read/write access to Group1 groupINSERT INTO [Permission] VALUES ('mary', '928', 'A', 0, 1)-- writeaccess to 928 accountThe simple querySELECT * FROM [Permission] WHERE [User] = 'john'returnsUser Item ItemType ReadPerm WritePerm---------- ---------- -------- ----------- -----------john 210 A 1 0john Group1 G 1 1but what I really want is (notice that Group1 has been replaced withthe two members of Group1)User Item ReadPerm WritePerm---------- ---------- ----------- -----------john 210 1 0john 210 1 1john ABC 1 1(Forget for the moment that 210 is listed twice with differentpermissions. I could take the result and do some sort of union to least(or most) restrictive permissions.)
Hi there,Need a little help with a certain query that's causing a lot of acidin my stomach...Have a table that stores sales measures for a given client. The salesmeasures are stored per year and there could be multiple salesmeasures every year per client. There is another field called lastupdate date. If there are multiple sales measures then need to selectthe one that's been entered last based on this field. Also, if thereis no sales measure data for current year then I need to return thelast year's data for which sales measure has been entered. Forexample: if client #1 has sales measure value of $200 for 1999 andnothing since, then I need to return $200 for any year following 1999.So the query would look something like this:SELECT client_name, sm_dollars FROM <tables>Based on the DDL at the bottom I would expect to get back: c1, 100;c2, 200The way I am doing it now is with correlated subqueries (3 to beexact) that each do an aggregate and join back to the original table.It works, but it is notoriously slow. SQL Server is scanning theindex and does a merge join which in a large query takes %95 of thetime. Here is the part of the query plan for it:| | | | | | |--MergeJoin(Inner Join, MANY-TO-MANYMERGE:([sales_measure].[client_id])=([sales_measure].[client_id]),RESIDUAL:(([sales_measure].[client_id]=[sales_measure].[client_id]AND [sales_measure].[tax_year]=[sales_measure].[tax_year]) AND[Expr1013]=[sales_measure].[last_update_date]))| | | | | | |--StreamAggregate(GROUP BY:([sales_measure].[client_id],[sales_measure].[tax_year])DEFINE:([Expr1013]=MAX([sales_measure].[last_update_date])))| | | | | | | |--MergeJoin(Inner Join, MERGE:([sales_measure].[client_id],[Expr1010])=([sales_measure].[client_id], [sales_measure].[tax_year]),RESIDUAL:([sales_measure].[client_id]=[sales_measure].[client_id] AND[sales_measure].[tax_year]=[Expr1010]))| | | | | | ||--Stream Aggregate(GROUP BY:([sales_measure].[client_id])DEFINE:([Expr1010]=MAX([sales_measure].[tax_year])))| | | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | ||--Index Scan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)| | | | | | |--IndexScan(OBJECT:([stars_perftest].[dbo].[sales_measure].[sales_measure_idx1]),ORDERED FORWARD)There are two indexes on sales measure table:sales_measure_pk - sales_measure_id (primary key) clusteredsales_measure_idx1 - client_id, tax_year, last_update_date, sm_dollarssales_measure table has 800,000 rows in it.Here is the rest of the DDL:IF OBJECT_ID('dbo.client') IS NOT NULLDROP TABLE dbo.clientGOcreate table dbo.client (client_idintidentityprimary key,client_namevarchar(100)NOT NULL)GOIF OBJECT_ID('dbo.sales_measure') IS NOT NULLDROP TABLE dbo.sales_measureGOcreate table dbo.sales_measure(sales_measure_idintidentityprimary key,client_idintNOT NULL,tax_yearsmallintNOT NULL,sm_dollarsmoneyNOT NULL,last_update_datedatetimeNOT NULL)GOCREATE INDEX sales_measure_idx1 ON sales_measure (client_id, tax_year,last_update_date, sm_dollars)GOINSERT dbo.client(client_name)SELECT'c1' UNION SELECT 'c2' UNION SELECT 'c3'GOINSERTdbo.sales_measure(client_id, tax_year, sm_dollars,last_update_date)SELECT1, 2004, 100, '1/4/2004'UNIONSELECT2, 2003, 100, '1/3/2004'UNIONSELECT 2, 2004, 150, '1/4/2004'UNIONSELECT2, 2004, 200, '1/5/2004'The view that I use to calculate sales measures:CREATE VIEW sales_measure_vw ASSELECTsm.*FROM sales_measure smINNER JOIN (SELECT sm2.client_id, sm2.tax_year,MAX(sm2.last_update_date) as last_update_dateFROM sales_measure sm2INNER JOIN (SELECT sm4.client_id, MAX(sm4.tax_year)as tax_yearFROM sales_measure sm4 GROUP BYsm4.client_id) sm3on sm3.client_id = sm2.client_idand sm3.tax_year = sm2.tax_yearGROUP BY sm2.client_id, sm2.tax_year ) sm1ON sm.client_id = sm1.client_id ANDsm.tax_year = sm1.tax_year ANDsm.last_update_date = sm1.last_update_dateAny advice on how to tame this would be appreciated. Also, any adviceon the indexes would help as well.ThanksBob
