.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Fauzul Azmi
Asad Ali
Post New Web Links

looking for CURSOR replacement

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :Sql Server
 
I got below solution from christa with cursor...could some one help me out without cursor solution.. here we are devloping WHERE clause... CREATE TABLE X (UID INT IDENTITY(1,1), COL_N VARCHAR(50), COL_V VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES ('S_NAME', 'AYAZ', 'VARCHAR(50)') INSERT INTO X VALUES ('S_ROLL', '10', 'DECIMAL(15, 0)') INSERT INTO X VALUES ('S_MARKS', '99', 'INT') INSERT INTO X VALUES ('S_SCHOOL', 'ZINQ', 'VARCHAR(100)') --SELECT * FROM X /*CURSOR*/ declare @n varchar(50), @v varchar(50), @d varchar(50) declare @sql varchar(max) set @sql = ' where ' declare mycur cursor for select col_N, col_V, col_D from X order by uid open mycur fetch next from mycur into @n,@v,@d while @@fetch_status = 0 begin set @sql = @sql + @n + ' = ' if @d like '%char%' set @sql = @sql + '''' + @v + '''' else set @sql = @sql + @v set @sql = @sql + ' and ' fetch next from mycur into @n,@v,@d end close mycur deallocate mycur set @sql = LEFT(@sql, len(@sql) - 4) print @sql --WHERE S_NAME = 'AYAZ' AND S_ROLL = 10 AND S_MARKS = 99 AND S_SCHOOL = 'ZINQ'


View Complete Post


More Related Resource Links

Cursor Functions

  
A cursor allows looping through a record set and performing a certain operation on each record within the set. SQL Server supports three functions that can help you while working with cursors: @@FETCH_STATUS, @@CURSOR_ROWS and CURSOR_STATUS. Cursor functions are non-deterministic.

DECLARE CURSOR in Transact-SQL

  
Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Replacement options for Membership

  

Hi everyone,
     I tried to use Microsoft's Membership option for signing up new users and authenticating them on subsequent pages, but it has not worked very well. The biggest issue has been with the REMEMBER ME function. With this checked, I am randomly logged out in the middle of some functions and some pages always show me as not authenticated, while others properly show me as authenticated. I built a site based on VB.
I was wondering what other people are using ro register users and monitor authentication and also if anyone has had better experiences with other solutions. 

Hi everyone,

     I tried to use Microsoft's Membership option for signing up new users and authenticating them on subsequent pages, but it has not worked very well. The biggest issue has been with the REMEMBER ME function. With this checked, I am randomly logged out in the middle of some functions and some pages always show me as not authenti

Web deployment project - web.config section replacement does not add remove tag

  

I have a web application that is actually installed as a component of a third party site.  In some configurations, I need to remove certain connection strings and re-add them.  I'm replacing this web.config section with a xml file that includes the following:

<connectionStrings>
      <remove name="MyOverridenConnection"/>
      <add connectionString="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDb;Data Source=MyServer" name="MyOverridenConnection"
           providerName="System.Data.SqlClient"/>
    </connectionStrings>

For some reason the remove tag is left out during the substitution and I end up with the following in the installed config:

<connectionStrings>
      <add connectionString="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDb;Data Source=MyServer" name="MyOverridenConnection"
           providerName="System.Data.SqlClient"/>
    </connectionStrings>

Is there any way to issue removes in replaced sec

Win32 Resources: Using C++ to Programmatically Retrieve a Global Cursor's Shape and ID

  

Getting global cursor information is useful when developing software that drives or gathers information about other UI-based applications, including information about a remote machine. This article describes a way to programmatically identify the current cursor's ID and bitmap at any point in time. The first technique described is based on polling for information and shows how to get the handle of the current global cursor. This handle will then allow you to get information about the cursor. You can also monitor WinEvents for changes to the global cursor.

Dmitri Klementiev

MSDN Magazine October 2001


Find and replacement SSIS Custom component issues

  
Hi All, I developed one custom component in SSIS  Find and Replacement I register the dll and copy and paste in to Pipleline component. but it's not visible in the Toolbox while choose Items Please suggest why it's not comming.. using   System; using   System.Collections.Generic; using   System.Linq; using   System.Text; using   Microsoft.SqlServer.Dts.Pipeline; using   Microsoft.SqlServer.Dts.Design; using   Microsoft.SqlServer.Dts.Runtime.Wrapper; using   Microsoft.SqlServer.Dts.Pipeline.Wrapper; using   System.Data.OleDb; using   System.Data.SqlClient; using   System.Windows.Forms; using   System.Runtime.InteropServices; using   System.Data; using   System.Collections; using   Microsoft.SqlServer.Dts.Runtime; using   Microsoft.SqlServer.Server; namespace   FindAndReplace { [ ComVisible(true)] [ DtsPipelineComponent ( DisplayName = "Find and Replace", Description = "Finding Find and replace", IconResource = "Microsoft.Samples.SqlServer.Dts.Find and Replace.ico", ComponentType = ComponentType.Transform )]   class FindAndReplace :PipelineComponent {   #region   ProvideComponentProperties   /// <summary>   /// </summary>   public ove

is there alternate solution for CURSOR?

  
/* CREATE TABLE X (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES (3, 'S_NAME', 'VARCHAR(50)') INSERT INTO X VALUES (5, 'S_ROLL', 'DECIMAL(15, 0)') INSERT INTO X VALUES (8, 'S_MARKS', 'INT') INSERT INTO X VALUES (9, 'S_SCHOOL', 'VARCHAR(100)') SELECT * FROM X CREATE TABLE Y (UID INT IDENTITY(1,1), S_NAME VARCHAR(50), S_ROLL DECIMAL(15, 0), S_MARKS INT, S_SCHOOL VARCHAR(100)) INSERT INTO Y VALUES ('AYAZ', 10, 99, 'ZINQ') SELECT * FROM Y CREATE TABLE Z (UID INT, COL_N VARCHAR(50), COL_D VARCHAR(50), COL_V VARCHAR(50)) */ DECLARE @vSQL NVARCHAR(MAX) DECLARE @UID INT DECLARE @COL_N VARCHAR(50) DECLARE @COL_D VARCHAR(50) DECLARE cur CURSOR FOR SELECT UID, COL_N, COL_D FROM X ORDER BY UID OPEN cur FETCH cur INTO @UID, @COL_N, @COL_D WHILE(@@FETCH_STATUS=0) BEGIN SET @vSQL = 'INSERT INTO Z (UID, COL_N, COL_D, COL_V) SELECT ' + CAST(@UID AS VARCHAR) + ',' + '''' + @COL_N + '''' + ',' + '''' + @COL_D + '''' + ',' + @COL_N + ' FROM Y ' EXEC SP_EXECUTESQL @vSQL PRINT @vSQL FETCH cur INTO @UID, @COL_N, @COL_D END CLOSE cur DEALLOCATE cur SELECT * FROM Z

Hide HoverMenuExtender when cursor is in an associated TextBox

  
Hi.I use HoverMenuExtenders on TextBoxes to display some info in certain cases (typically; "you did something wrong here" - messages). Thing is, I want the HoverMenuExtender to disappear when the cursor is in its associated TextBox (and the mouse is outside its borders). Right now the HoverMenuExtender is still visible when the user have placed the cursor in a TextBox to write even though the mouse pointer is outside the TextBox.Is this possible? Thanks for any input! 

Could not complete cursor operation because the table schema changed

  
Microsoft SQL Server  2000 - 8.00.2039 Got this error: Could not complete cursor operation because the table schema changed after the cursor was declared. SQLCode: 16943 SQLState: HY000 Is this a known issue?  I suspect the application logic may cause this error. Please advise. Thanks a lot!  

fetch data from cursor(what wrong?)

  
hi there i have a SP that send mailbut in the cursor does not appears no one record, i written PRINT'-------' , and it seems no recordwhere is wrong here?the query returns 200 records here, but not in cursoralter PROCEDURE [dbo].MAIL (@date date) AS BEGIN declare @MonthlyTimeInterval int declare @WorkingDaysBeforeOpen int declare @WorkingDaysBeforeClose int ----valorizzazione parametri MAIL--------- select @MonthlyTimeInterval=MonthlyTimeInterval, @WorkingDaysBeforeOpen=WorkingDaysBeforeOpen, @WorkingDaysBeforeClose=WorkingDaysBeforeClose FROM ParameterData --------market in apertura------------- DECLARE @getMktOpen CURSOR declare @PeriodEND date declare @PeriodStart date declare @Market Varchar(300) declare @Status bit declare @MarketID int DECLARE @MarketPeriodID INT SET @getMktOpen = CURSOR FOR SELECT MAX(Period.[Desc]) AS PeriodEND, MIN(Period.[Desc]) AS PeriodStart, Market.[Desc] AS Market, MarketPeriod.Status, Market.MarketID, MarketPeriod.MarketPeriodID FROM MarketPeriod INNER JOIN Period ON MarketPeriod.PeriodID = Period.PeriodID INNER JOIN Market ON MarketPeriod.MarketID = Market.MarketID GROUP BY Market.[Desc], MarketPeriod.Status, Market.MarketID, MarketPeriod.MarketPeriodID -- HAVING (MarketPeriod.Status = 0) -- AND (MIN(Period.[Desc]) = CONVERT(CHAR(11),@date,111) ) OPE

XAML Serialization replacement

  
I've got an app where the user lays out a windows with various information displays.  To save the layout the window is serialized using XAML Serialization vial XAMLWriter.Save. the problem is all of the controls are user controls and as XAML Writer is"self contained" it serializes all the contents of the usercontrol as well.  You end up with the control, its resources, contextmenus and component controls being serialized. Does any one know of a solution to this problem so only the XAML for the user control only? I'd role my own version of this, but don't really know the best way to do so.   any help would be appreciated.  

Changing Mouse Cursor into Crossed Circle in ListBox-ScrollBar/ScrollViewer(on Drag and Drop).

  
Hi All,   I have a ListBox with 10 items, the automatic Scrollbar/ScrollViewer is appearing. If i'm dragging an item from the listbox and moving the mouse cursor on top scrollbar/scrollviewer the cusor have to change into CROSSED CIRCLE(means its not a location to drop). Can any one help to do the same, or please let me know if more info needed.   Thanks, Karthikeyan Manickam.

Advanced T-SQL - want to get rid of a large Cursor

  
Hi all, I am importing & processing large amounts of data (100MB+ xml files), and the biggest bottleneck is a step involving one huge cursor.  In this step, I have say 300,000 rows, that I need to split space-delimited values into their own individual rows (10 Million rows total after the 300k are fn_split). Presently I am loading them all into a Cursor, fetch next into a group of local vars, then combine fn_split with insert.  It takes 10-15 minutes to do. Meanwhile, my server has 24 processors (cores + hyperthreading), and only ONE is in use due to this cursor.  Talk about a waste of resources! I have tried many alternatives - @id=1 WHILE @id<max BEGIN select @values=cols fn_split Insert @id=@id+1 END, and they all seem to take 2-3 times as long to execute as the Cursor. (the While loop also uses less processing %age than the cursor!) There has to be a way to optimize this thing... I would love to find a way to engage all 24 processors at 40-60% to crank through this thing, it'd be done in 2-3 minutes.  Anybody have ideas? Thanks- Jason

Oracle stored proc optionally returning a ref cursor.

  
Hi, I would like to use a technique for exception handling on our database stored procs whereby, each proc exposes a p_error_code output parameter of type int. 0 indicates no error, and other values indicate application specific exceptions. Now, where a stored proc will return a ref cursor if no exceeptions occur, we would like to check the p_error_code and if not 0, then use the ref cursor. If an exception does occur, then the stored proc may or may not have populated the ref cursor, but the dotnet code will almost definately not read the contents. Now, we have done this with the following code:            OracleCommand command = CommandFactory.CreateCommand(transaction, "RTS.p_get_audit_trail");             command.Parameters.Add(ParameterFactory.CreateParameter("p_doc_id", RTSId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_rev_id", RevisionId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_c_item_id", ContentItemId));             command.Parameters.Add(ParameterFactory.CreateParameter("p_error_code", OracleType.Number, ParameterDirection.Output));          &nbs

RunTime ControlTemplate Replacement

  
Hello, I would simply like to replace the ControlTemplate of a Label as shown below, and have the visual effect of this ControlTemplate replacement render in real time (during run time).  The code works, but the host panel, that which is hosting all of the labels with this ControlTemplate, needs to be re-displayed to the user.  This means the user basically has to close the tab and open it again in my application to see the effect of the ControlTemplate replacement. Here is the code: // setup and configure the resources // MemoryStream srRequiredColumnLabel = null; MemoryStream srRequiredColumnLabelFixed = null; ParserContext pc = null; string xamlRequiredColumnLabel = string.Empty; string xamlRequiredColumnLabelFixed = string.Empty; xamlRequiredColumnLabel = @"<ControlTemplate TargetType='{x:Type Label}'> <Grid> <Grid.ColumnDefinitions> <ColumnDefinition Width='*'/> <ColumnDefinition Width='8'/> <ColumnDefinition Width='2'/> </Grid.ColumnDefinitions> <ContentPresenter Grid.Column='0' VerticalAlignment='Center'/> <DockPanel Grid.Column='1' VerticalAlignment='Center' HorizontalAlignment='Left'>

Oracle ref cursor or temp table?

  
I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a ref cursor would be faster and more efficient. Does anyone know?

Cursor problem with Stored Procedure and PHP

  
I'm struggling with accessing the result set of a stored procedure using the SQL Server Driver for PHP 1.1. I've already got a few of them running, but this one is special in that it has an XML input parameter which is parsed into a table variable. This table variable is then used within a select statement. When I execute the SP within Management Studio, it runs fine and returns exactly one result set with multiple rows. But when I execute it from PHP, I get the error message "Executing SQL directly; no cursor. ". I've tried every cursor type out there, the only one that doesn't fail is SQLSRV_CURSOR_FORWARD, but with that I'm not getting any results at all. The original SP is enormous, so I have stripped it down as far as I could so it still throws the error. If I remove the bold part, my PHP code executes fine.   USE [testsite] GO SET  ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getCrossSellingItems]     @InputIds XML AS     SET NOCOUNT ON ;     DECLARE @tblInputIds TABLE     (         Id INT     );     INSERT INTO @tblInputIds (Id)     (         SELECT         T.Item.value('@Id', 'INT') FROM @InputIds.nodes('/Root/Item') AS T(
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend