adding a if statement within my cursor.
Use master
GO
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
[code]....
The cursor should only grant access to a particular database. If the user exists within the database it should not execute the script.
I have a SQL statement stored in a SQL varriable (after a lot of conditions)
Code:
declare @sql char(100)
set @sql = 'select ma_kh, ten from _khang'
Now, I want to create a cursor to recalculate some values
I've tried:
Code:
declare cur_T cursor for exec(@sql) open cur_T
but it doesn't work.
Can I have another way to do that???
I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.
Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.
Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.
For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequency
1 2014-02-15 48 8 12
the future cash flow table the prcessing date column will be shown in the following way
Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15
I do not to want to use cursor....
I need to use WHILE to avoid Cursor under certains conditions.
My SELECT statement is:
SELECT ref, ano, numberofyears ,nreint, naoreint,degress,
tabela, tax, taxamaxima,[evactual],
[evaldepact],[ereintact],nrregbt,[taxAmtAno]
FROM deprec
ORDER BY [ref] ASC
numberofyears= 100 /tax for exemple for a good where lifecycle is 4 years ,ex:
Tax = 25% Then 100/25 = 4 years
I see this WHILE script, but i need to run :
1. for each REF + Until years < 4 in this exemple, because i have goods years depend on Percent.
the WHILE script i see is:
DECLARE @table1 TABLE (Id int not null primary key identity(1,1), col1 int )
INSERT into @table1 (col1) SELECT col1 FROM table2
SET @num_rows=@@ROWCOUNT
SET @cnt=0
WHILE @cnt<@num_rows
[Code] .....
My doubt is how to make the LOOP for each REF until Year < 4 (like my example)
I am trying to calculate cumulative GPA which is currently it is being done via a cursor and i was looking to change it to set based if possible.
The cumulative GPA is basically the average marks of all courses in a term and all previous terms. Where I am having an issue is if the course was taken in a previous term and the mark was lower, then the lower mark in not factored in to the GPA. If a course was taken in a previous term and the mark was higher then the marks for the current term are not factored into the GPA.
In the example below, math is taken in all three terms and the current term's mark is included in the GPA but not the previous terms because current terms mark is higher. Two classes (bio,chem) are taken in third term but not included due to marks being higher in second term.
CREATE TABLE [gpa]([term] int,
[course] varchar(10),
[mark] float);
INSERT INTO [gpa]
VALUES (1,'math',3),
(1,'eng',4),
[Code] .....
I like to backup the stored procedures' code used in my databases. So I created this Script:
/*
IF OBJECT_ID('[Monitor].[dbo].[Procedurecode]') IS NOT NULL DROP TABLE [Monitor].[dbo].[Procedurecode];
*/
DECLARE
@db nvarchar(50),
@strSQL nvarchar (100)
IF CURSOR_STATUS('global','cur1')>=-1 BEGIN DEALLOCATE cur1 END
[code]....
Problem is (and I seem not to be the only one with tis) described here:
"If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running." There is nothing magical in SQL server that knows you intend a series of dynamic sql commands to be part of a single job running in a single context. You have to build the entire string for the job you want to execute."
[URL]
So I only get the SPs of the current database.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0
PRINT @Found
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END
What is the best way to script this
I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
I have a temptable with a list of user IDs that I want to drop so I created a script to do a cursor and run through my drop functions. The drops work by themselves and the ver check works with them but when I wrap them in the cursor all i get is an output for each user in the results window in ssms. why it's not setting the variable and instead outputting to results?
DECLARE @ver nvarchar(128);
DECLARE @UserName nvarchar(50);
DECLARE @UserD nvarchar(80);
DECLARE @LoginD nvarchar(80);
-- Initialize the variable.
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
[code]...
I need removing cursor in my table valued function with alternate code.
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE
[Code] ....
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
[code]....
I have stored procedure .In SP i am using cursur to load data from Parent to several child table.
I have attached the script with this message.
And my problem is how to use direct select and insert or load to speedup the process instead of cursor.
USE [IconicMarketing]
GO
/****** Object: StoredProcedure [dbo].[SP_DMS_INVENTORY] Script Date: 3/6/2015 3:34:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] ....
Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR
[Code] ....
so async cursor population is supposed to create the cursor and return the cursor id quickly, while the server works on async populating the results. For a keyset-driven cursor, SQL Server stores the key sets in tempdb, which it then uses to fetch data for cursor results. Anyway, this works fine for smaller tables, but I'm finding for large result sets, the async cursor population is very slow and indeed seems to approximate synchronous time. The wait stat I get while it is running (supposedly asynchronously) is TRANSACTION_MUTEX.
Example:
--enable async cursor
exec dbo.sp_configure 'cursor threshold', 0; reconfigure;
declare @cursor int, @stmt nvarchar(max), @scrollopt int, @ccopt int, @rowcount int;
--example of giant result set
set @stmt = 'select * from sys.all_objects o1, sys.all_objects o1';
[code]...
Note that using the SQL "select * from sys.all_objects o1" is much faster than "select * from sys.all_objects o1, sys.all_objects o2". However, if cursor population is async, I'd expect the time to return a cursor id to be similar between the two.
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx
View 4 Replies View RelatedHi...
I have a stored procedure that rertrieves data from an sql database
and sends out a mail to each receipient who meets the criteria
I am using SQL mail.
I dynamically generate the where clause for my sql query based on criteria taken
from other stored procedures and store it in a varchar variable
called @sqlquery
When i have the following code to run my cursor
DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City,
Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3
I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary.
What should i do. i have tested the variable @sqlquery and it is
definately not blank. There is no bracket error or anything.
Please help!!!
Thanks much indeed
Ramesh
Hi All, Can anyone please help?
TableA has data as below:
ssn sex dob rel_code
111111111 m 19500403 m
111111111 f 19570908 w
111111111 f 19770804 d
111111111 f 19801203 d
111111111 f 19869712 d
111111111 m 19870907 s
111111111 m 19901211 s
I have to convert the rel_code into a specific manner so the data will look as below in TableB:
ssn sex dob rel_code
111111111 m 19500403 01
111111111 f 19570908 02
111111111 f 19770804 20
111111111 f 19801203 21 111111111 f 19869712 22
111111111 m 19870907 30
111111111 m 19901211 31
Member's rel_code = 01
spouse's rel_code = 02
daughter's rel_code starts from 20 with the oldest and increments by 1.
Son's rel_code starts from 30 and increments by 1 from oldest to the youngest.
I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor.
Thanks in advance.
Jannat.
Hi All,
What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).
=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"
if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"
DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?
Thanks !
I would like to get the results of a cursor into update statement but it fills only the last record of the cursor
this is the cursor:
Code:
DECLARE @avg varchar(50)
DECLARE @cur_avg CURSOR
SET @cur_avg = CURSOR FOR
select cast(avg(cast(reply as decimal(12,2))) as decimal(12,2)) from tbl_app_monitoring
group by test_name, application_id
this is the update statement:
Code:
OPEN @cur_avg
FETCH @cur_avg INTO @avg
WHILE (@@FETCH_STATUS = 0) BEGIN
UPDATE tbl_app_monitoring_archive
SET average = @avg
FETCH @cur_avg INTO @avg
END
is it also possible to do this without the cursor ?
I have a cursor that goes through a table with the names of all the database in my server. So for each fetch, the cursor gets the name of a database and assign it to a variable, @databaseName, and try to do some queries from that database by using the command "USE @databaseName". But "USE" doesn't take the variable @databaseName; it is expecting a database name (i.e. USE master).
Here is my code:
Declare @databaseName varchar(50)
Declare c_getDatabaseName CURSOR for SELECT name from tblDatabases
OPEN c_getDatabaseName
FETCH NEXT from c_getDatabaseName into @databaseName
While @@FETCH_STATUS = 0
[Code] ....
How to get USE to take the variable value ?
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
View 4 Replies View RelatedHi everyone,
The following snippet of code returns something like that: string;string1;string2
Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task)
Thanks a lot,
declare @anex as varchar(500)
declare @anex2 as varchar(700)
set @anex2 = ''
DECLARE anexos CURSOR FOR
SELECT [Ruta] + [Fichero] as ANEXO
FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO
WHERE ENVIADO = 0
OPEN anexos;
FETCH NEXT FROM anexos INTO @anex
WHILE @@FETCH_STATUS = 0
BEGIN
IF @anex2 = ''
begin
set @anex2 = @anex
end
else
begin
set @anex2 = @anex2 + ';' + @anex
end
FETCH NEXT FROM anexos INTO @anex
END
CLOSE anexos
DEALLOCATE anexos
Hello,
I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function.
I have a temp table populated with the below headers and their associated data.
The headers are as follows:
ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost.
The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function.
One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand.
The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with.
The current script I have written is below. If you can offer any help I would greatly appreciate it.
Code SnippetDeclare @ItemID VarChar(30),
@QtyOnHand Decimal (16,8),
@WhseID VarChar (6),
@SumRcvd Int,
@TranID VarChar(30),
@TranDate DateTime,
@QtyRcvd Decimal (16,8),
@UnitCost Decimal (16,8),
@ItemKey Int,
@WhseKey Int,
@ShortDesc VarChar (40),
@StdCost Decimal (16,8)
DECLARE Temp_cursor CURSOR FOR
SELECT TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost
FROM #Temp1 tem
OPEN Temp_cursor
FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost
WHILE @@FETCH_STATUS = 0
BEGIN -- 0
Insert Into #Temp3
(TranID, TranDate, QtyRcvd,
UnitCost, ItemKey, WHseKey,
ItemID, ShortDesc, WhseID,
QtyOnHand, StdCost)
Values
(@TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost)
FETCH NEXT FROM Temp_cursor
INTO @TranID, @TranDate, @QtyRcvd,
@UnitCost, @ItemKey, @WHseKey,
@ItemID, @ShortDesc, @WhseID,
@QtyOnHand, @StdCost
I am looking to create a simple IF Else statement in a SPROC but my syantax seems to be wrong.
IF (vchDept='IT')
BEGIN
THEN 1
END
ELSE
IF vchEmail like '%@abc.com' AND vchDept<>1
BEGIN
THEN 2
END
[code]....
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'
Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'
but I can't seem to come up with the right statement.
Any help greatly appreciated.
Thanks,
Judith
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View 6 Replies View RelatedWhen I run this update statement, it updates the proper badgenumbers but it only updates them to 1 when I did a count? As the data displays some of the results should be more than 1. Why did this occur?
Declare
@count int,
@Assignment varchar(100),
@fullname varchar(100),
@timeworkedtoday decimal(18,2),
@badgeNum varchar(50),
@ticket varchar(50)
[Code] ....
Hi everyone,
I have been trying to perform the following task:
Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:
Database name
Table name
Column name from that table with a clustered index
I have already created the following script which displays all the databases for a given server:
declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name
open testCursorForDB
fetch next from testCursorForDB
into @DBname
while @@fetch_status = 0
begin
print @DBname
fetch next from testCursorForDB
into @DBname
end
close testCursorForDB
deallocate testCursorForDB
I also have created the following query which will display all the table and column names which have a clustered index for a given database:
select object_name(i.id) as TableName,
i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'
However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.
Any help is greatly appreciated
Thanks!
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' +
Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727))
+ ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID)
From Parts As P
Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187
Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
My table structure like below.
id date value1 value2
1 5-june-2104 Yes No
1 6-june-2014 No Yes
2 5-june-2104 Yes Yes
Want to calculate yes count on any day for same id
I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.
SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',
[code]...