Search Results

Search found 3935 results on 158 pages for 'extended procedures'.

Page 28/158 | < Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >

• query in sql server for retrieving rows

  - by Arash khangaldi

  I have a table that contains the following 4 columns: id name lastname phone I want to write a stored procedure that gets an id as parameter, and then gets the name of that id and then use that name to get all the rows that their name is equal to the names that i found in last step! here it is my query, i know it's wrong but i'm new to sql commands: ALTER PROCEDURE dbo.GetAllNames @id int AS select name as Name from Users where id = @id -- i don't how to retrieve the names that are equal to Name select * from Users where name = Name can you correct my query and help me? Thanks.

  Read the article

• How do I get output into Java from a SELECT stored procedure in Oracle?

  - by Ventrue

  I'm using Java to connect to an Oracle 10 Database. I want to create a stored procedure (don't ask why) that takes no arguments and returns a lot of rows. Specifically, in Java I want to be able to get this data with something like: ResultSet rs = stmt.executeQuery("call getChildless"); where getChildless is the query: SELECT objectid FROM Object WHERE objectid NOT IN (SELECT parent FROM subparts); However, I just cannot for the life of me figure out how to get my output from the stored procedure. I've googled it and I get all this sample code that Oracle won't compile, presumably it's for a previous version. Refcursors seem to come up a lot, but I'm not sure if that's what I actually want, to use it with a ResultSet.

  Read the article

• SQL Server 2008 - Keyword search using table Join

  - by Aaron Wagner

  Ok, I created a Stored Procedure that, among other things, is searching 5 columns for a particular keyword. To accomplish this, I have the keywords parameter being split out by a function and returned as a table. Then I do a Left Join on that table, using a LIKE constraint. So, I had this working beautifully, and then all of the sudden it stops working. Now it is returning every row, instead of just the rows it needs. The other caveat, is that if the keyword parameter is empty, it should ignore it. Given what's below, is there A) a glaring mistake, or B) a more efficient way to approach this? Here is what I have currently: ALTER PROCEDURE [dbo].[usp_getOppsPaged] @startRowIndex int, @maximumRows int, @city varchar(100) = NULL, @state char(2) = NULL, @zip varchar(10) = NULL, @classification varchar(15) = NULL, @startDateMin date = NULL, @startDateMax date = NULL, @endDateMin date = NULL, @endDateMax date = NULL, @keywords varchar(400) = NULL AS BEGIN SET NOCOUNT ON; ;WITH Results_CTE AS ( SELECT opportunities.*, organizations.*, departments.dept_name, departments.dept_address, departments.dept_building_name, departments.dept_suite_num, departments.dept_city, departments.dept_state, departments.dept_zip, departments.dept_international_address, departments.dept_phone, departments.dept_website, departments.dept_gen_list, ROW_NUMBER() OVER (ORDER BY opp_id) AS RowNum FROM opportunities JOIN departments ON opportunities.dept_id = departments.dept_id JOIN organizations ON departments.org_id=organizations.org_id LEFT JOIN Split(',',@keywords) AS kw ON (title LIKE '%'+kw.s+'%' OR [description] LIKE '%'+kw.s+'%' OR tasks LIKE '%'+kw.s+'%' OR requirements LIKE '%'+kw.s+'%' OR comments LIKE '%'+kw.s+'%') WHERE ( (@city IS NOT NULL AND (city LIKE '%'+@city+'%' OR dept_city LIKE '%'+@city+'%' OR org_city LIKE '%'+@city+'%')) OR (@state IS NOT NULL AND ([state] = @state OR dept_state = @state OR org_state = @state)) OR (@zip IS NOT NULL AND (zip = @zip OR dept_zip = @zip OR org_zip = @zip)) OR (@classification IS NOT NULL AND (classification LIKE '%'+@classification+'%')) OR ((@startDateMin IS NOT NULL AND @startDateMax IS NOT NULL) AND ([start_date] BETWEEN @startDateMin AND @startDateMax)) OR ((@endDateMin IS NOT NULL AND @endDateMax IS NOT NULL) AND ([end_date] BETWEEN @endDateMin AND @endDateMax)) OR ( (@city IS NULL AND @state IS NULL AND @zip IS NULL AND @classification IS NULL AND @startDateMin IS NULL AND @startDateMax IS NULL AND @endDateMin IS NULL AND @endDateMin IS NULL) ) ) ) SELECT * FROM Results_CTE WHERE RowNum >= @startRowIndex AND RowNum < @startRowIndex + @maximumRows; END

  Read the article

• INSERT stored procedure does not work?

  - by vikitor

  Hello, I'm trying to make an insertion from one database called suspension to the table called Notification in the ANimals database. My stored procedure is this: ALTER PROCEDURE [dbo].[spCreateNotification] -- Add the parameters for the stored procedure here @notRecID int, @notName nvarchar(50), @notRecStatus nvarchar(1), @notAdded smalldatetime, @notByWho int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Animals.dbo.Notification values (@notRecID, @notName, @notRecStatus, null, @notAdded, @notByWho); END The null inserting is to replenish one column that otherwise will not be filled, I've tried different ways, like using also the names for the columns after the name of the table and then only indicate in values the fields I've got. I know it is not a problem of the stored procedure because I executed it from the sql server management studio and it works introducing the parameters. Then I guess the problem must be in the repository when I call the stored procedure: public void createNotification(Notification not) { try { DB.spCreateNotification(not.NotRecID, not.NotName, not.NotRecStatus, (DateTime)not.NotAdded, (int)not.NotByWho); } catch { return; } } It does not record the value in the database. I've been debugging and getting mad about this, because it works when I execute it manually, but not when I automatize the proccess in my application. Does anyone see anything wrong with my code? Thank you

  Read the article

• Export view data programmatically in Access/SQL Server

  - by andy

  We have an Access application front-end connected to a SQL Server 2000 database. We would like to be able to programmatically export the results of some views to whatever format we can (ideally Excel, but CSV / tab delimited is fine). Up until now we've just hit F11, opened up the view, and hit File-Save As, but we're starting to get results with more than 16,000 results, which can't be exported. I'd like some sort of server side stored procedure we can trigger that will do this. I'm aware of the sp_makewebtask procedure that does this, however it requires administrative rights on the server, and for obvious reasons we can't give that to everyone. Any ideas?

  Read the article

• how to use a parameterized function for the Default Binding of a Sql Server column

  - by Walt Gaber

  I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged. I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate). I would like to provide a Default Binding for this column that would check for other occurrences of this file (i.e. name, length, timestamp) at the time the new file is being recorded. I have created a function that performs this check (see “GetPrimaryDuplicate” below). But I don’t know how to bind this function which requires three parameters to the table’s “primary_duplicate” column as its Default Binding. I would like to avoid using a trigger. I currently have a stored procedure used to insert new records that performs this check. But I would like to ensure that the flag is set correctly if an insert is performed outside of this stored procedure. How can I call this function with values from the row that is being inserted? USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FileCatalog]( [id] [uniqueidentifier] NOT NULL, [catalog_timestamp] [datetime] NOT NULL, [primary_duplicate] nchar NOT NULL, [name] nvarchar NULL, [length] [bigint] NULL, [timestamp] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_id] DEFAULT (newid()) FOR [id] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_catalog_timestamp] DEFAULT (getdate()) FOR [catalog_timestamp] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_primary_duplicate] DEFAULT (N'GetPrimaryDuplicate(name, length, timestamp)') FOR [primary_duplicate] GO USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[GetPrimaryDuplicate] ( @name nvarchar(255), @length bigint, @timestamp datetime ) RETURNS nchar(1) AS BEGIN DECLARE @c int SELECT @c = COUNT(*) FROM FileCatalog WHERE name=@name and length=@length and timestamp=@timestamp and primary_duplicate = 'P' IF @c > 0 RETURN 'D' -- Duplicate RETURN 'P' -- Primary END GO

  Read the article

• Problem with passing parameters to SQL procedure using VBA

  - by Stefan Åstrand

  Hi, I am trying to pass @intDocumentNo and @intCustomerNo to a stored procedure using VBA but only @intCustomerNo is updated in dbo.tblOrderHead. I don't think the problem is with the procedure because if I enter the values manually it runs properly. What am I doing wrong? VBA Code: Private Sub intCustomerNo_Click() Dim cmdCommand As New ADODB.Command With cmdCommand .ActiveConnection = CurrentProject.Connection .CommandType = adCmdStoredProc .CommandText = "uspSelectCustomer" '@intDocumentNo .Parameters("@intDocumentNo").Value = Forms![frmSalesOrder].[IntOrderNo] '@intCustomerNo .Parameters("@intCustomerNo").Value = Me![intCustomerNo] .Execute End With DoCmd.Close Forms![frmSalesOrder].Requery End Sub Procedure: UPDATE dbo.tblOrderHead SET dbo.tblOrderHead.intCustomerNo = @intCustomerNo , dbo.tblOrderHead.intPaymentCode = dbo.tblCustomer.intPaymentCode, dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode, dbo.tblOrderHead.txtRegionCode = dbo.tblCustomer.txtRegionCode, dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode, dbo.tblOrderHead.txtLanguageCode = dbo.tblCustomer.txtLanguageCode FROM dbo.tblOrderHead INNER JOIN dbo.tblCustomer ON dbo.tblOrderHead.intCustomerNo = dbo.tblCustomer.intCustomerNo AND dbo.tblOrderHead.intOrderNo = @intDocumentNo

  Read the article

• Possible to get the line number of the currently executing sproc in SQL Server?

  - by Ian Kemp

  A few years back I worked in a Sybase/Delphi environment, using the BDE to connect to the DB server. We had a little Delphi app that, given the name of a currently executing stored procedure, could tell you what line of that sproc was currently being executed. This was exceptionally useful for debugging sprocs that seemed to be hanging. I'd like to use this functionality in SQL Server, but I can't remember whether it was a Sybase or a BDE feature. Is this functionality available in SQL Server and if so, what command(s) do I need to use to view this information?

  Read the article

• error invoking store procedure with input parameter from ADO.Net

  - by George2

  Hello everyone, I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net. Here is my code and related error message. The error message says, @Param1 is not supplied, but actually it is supplied in my code. Any ideas what is wrong? System.Data.SqlClient.SqlException: Procedure or function 'Pr_Foo' expects parameter '@Param1', which was not supplied. class Program { private static SqlCommand _command; private static SqlConnection connection; private static readonly string _storedProcedureName = "Pr_Foo"; private static readonly string connectionString = "server=.;integrated Security=sspi;initial catalog=FooDB"; public static void Prepare() { connection = new SqlConnection(connectionString); connection.Open(); _command = connection.CreateCommand(); _command.CommandText = _storedProcedureName; _command.CommandType = CommandType.StoredProcedure; } public static void Dispose() { connection.Close(); } public static void Run() { try { SqlParameter Param1 = _command.Parameters.Add("@Param1", SqlDbType.Int, 300101); Param1.Direction = ParameterDirection.Input; SqlParameter Param2 = _command.Parameters.Add("@Param2", SqlDbType.Int, 100); portal_SiteInfoID.Direction = ParameterDirection.Input; SqlParameter Param3 = _command.Parameters.Add("@Param3", SqlDbType.Int, 200); portal_RoleInfoID.Direction = ParameterDirection.Input; _command.ExecuteScalar(); } catch (Exception e) { Console.WriteLine(e); } } static void Main(string[] args) { try { Prepare(); Thread t1 = new Thread(Program.Run); t1.Start(); t1.Join(); Dispose(); } catch (Exception ex) { Console.WriteLine(ex.Message + "\t" + ex.StackTrace); } } } thanks in avdance, George

  Read the article

• SQL Query to get Count within a Stored Proc

  - by sia

  So i need to figure out how i can get a record count value and use that count as a new value to insert into a table. Ex: In My Stored Procedure @Count int What im looking for @Count to equal "select top (1) _fieldName from _someTable order by _fieldName Desc" Finally insert into _newTable (_fieldName) values (@Count) End I dont have to use a variable, just trying to demonstrate what im really trying to do. My SQL knowledge is pretty limited so no laughing, or smirking! ;)

  Read the article

• MySQL db Audit Trail Trigger

  - by Natkeeran

  I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here. I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp. The mysql stored procedure is the following (this executes fine, and creates the procedure): The call to the procedure such as: CALL addLogTrigger('ProductTypes', 'ProductTypeID'); executes, but does not create any triggers (see the image). SHOW TRIGGERS returns empty set. Please let me know what could be the issue, or an alternate way to implement this. DROP PROCEDURE IF EXISTS addLogTrigger; DELIMITER $ CREATE PROCEDURE addLogTrigger(IN tableName VARCHAR(255), IN pkField VARCHAR(255)) BEGIN SELECT CONCAT( 'DELIMITER $\n', 'CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, ' FOR EACH ROW BEGIN ', GROUP_CONCAT( CONCAT( 'IF NOT( OLD.', column_name, ' <=> NEW.', column_name, ') THEN INSERT INTO AuditLog (', 'TableName, ', 'RowPK, ', 'FieldName, ', 'OldValue, ', 'NewValue' ') VALUES ( ''', table_name, ''', NEW.', pkField, ', ''', column_name, ''', OLD.', column_name, ', NEW.', column_name, '); END IF;' ) SEPARATOR ' ' ), ' END;$' ) FROM information_schema.columns WHERE table_schema = database() AND table_name = tableName; END$ DELIMITER ;

  Read the article

• SQL Server store procedure line number issue

  - by George2

  Hello everyone, I am using SQL Server 2008 Enterprise. I met with issue which says line 9 of store procedure foo is meeting with dead lock issue. My question is how to find exactly the 9th line of the store procedure? My confusion is because of coding format issue, how to locate 9th line correctly. thanks in advance, George

  Read the article

• SQL Server stored procedure return code oddity

  - by gbn

  Hello The client that calls this code is restricted and can only deal with return codes from stored procs. So, we modified our usual contract to RETURN -1 on error and default to RETURN 0 if no error If the code hits the inner catch block, then the RETURN code default to -4. Where does this come from, does anyone know...? IF OBJECT_ID('dbo.foo') IS NOT NULL DROP TABLE dbo.foo GO CREATE TABLE dbo.foo ( KeyCol char(12) NOT NULL, ValueCol xml NOT NULL, Comment varchar(1000) NULL, CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (KeyCol) ) GO IF OBJECT_ID('dbo.bar') IS NOT NULL DROP PROCEDURE dbo.bar GO CREATE PROCEDURE dbo.bar @Key char(12), @Value xml, @Comment varchar(1000) AS SET NOCOUNT ON DECLARE @StartTranCount tinyint; BEGIN TRY SELECT @StartTranCount = @@TRANCOUNT; IF @StartTranCount = 0 BEGIN TRAN; BEGIN TRY --SELECT @StartTranCount = 'fish' INSERT dbo.foo (KeyCol, ValueCol, Comment) VALUES (@Key, @Value, @Comment); END TRY BEGIN CATCH IF ERROR_NUMBER() = 2627 --PK violation UPDATE dbo.foo SET ValueCol = @Value, Comment = @Comment WHERE KeyCol = @Key; ELSE RAISERROR ('Tits up', 16, 1); END CATCH IF @StartTranCount = 0 COMMIT TRAN; END TRY BEGIN CATCH IF @StartTranCount = 0 AND XACT_STATE() <> 0 ROLLBACK TRAN; RETURN -1 END CATCH --Without this, we'll send -4 if we hit the UPDATE CATCH block above --RETURN 0 GO --Run with RETURN 0 and fish line commented out DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' SELECT @rtn; SELECT * FROM dbo.foo DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar2 />', 'testing2' --updated OK but we get @rtn = -4 SELECT @rtn; SELECT * FROM dbo.foo --uncomment fish line DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' --Hit outer CATCH, @rtn = -1 as expected SELECT @rtn; SELECT * FROM dbo.foo

  Read the article

• Insert Stored Procedure, Using Asp.Net WebForm to Insert new [Customer]

  - by user2953815

  Can someone please help me create a stored procedure to insert a new customer from a web form. I am having difficulty making the state a drop down list for customer to pick a state from the list and having that inserted into the database. INSERT INTO Customer ( Cust_First, Cust_Middle, Cust_Last, Cust_Phone, Cust_Alt_Phone, Cust_Email, Add_Line1, Add_Line2, Add_Bill_Line1, Add_Bill_Line2, City, State_Prov_Name, Postal_Zip_Code, Country_ID ) VALUES ( @Cust_First, @Cust_Middle, @Cust_Last, @Cust_Phone, @Cust_Alt_Phone, @Cust_Email, @Add_Line1, @Add_Line2, @Add_Bill_Line1, @Add_Bill_Line2, @City, @State_Prov_Name, @Postal_Zip_Code, @Country_ID )"> <InsertParameters> <asp:Parameter Name="Cust_First" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Middle" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Last" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Phone" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Alt_Phone" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Email" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Line1" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Line2" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Bill_Line1" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Bill_Line2" Type="String"></asp:Parameter> <asp:Parameter Name="City" Type="String"></asp:Parameter> <asp:Parameter Name="Postal_Zip_Code" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_ID" Type="Int32"></asp:Parameter> <asp:Parameter Name="State_Prov_Name" Type="String"></asp:Parameter> <asp:Parameter Name="Country_Name" Type="String"></asp:Parameter> </InsertParameters>

  Read the article

• Stored procedure does not return result when using Temp table

  - by Muhammad Akhtar

  I have used temp table in my stored procedure and unable to get the result when executing. It return result fine when running the same as query. my query structure is something like... ALTER PROCEDURE [dbo].[test] as begin SET NOCOUNT ON; SELECT * INTO #Dates FROM Dates -- Used temp table SET @Query = ' SELECT [Name], [HotelName], '+@Dates+' FROM ( SELECT , HD.[Name], HD.[HotelName], HD.Price FROM #Dates D LEFT JOIN #HotelData HD ON D.DateVal = HD.OccDate) T PIVOT ( SUM(Price) FOR [Date] IN ('+@Dates+') ) AS PT' execute(@Query) end Exec test -- Exectuting, I am getting message command completed successfully

  Read the article

• How to use Stored Procedure in Inner Joins?

  - by Shubham Jain

  How to use stored procedure in inner joins?

  Read the article

• Pass parameters to a stored procedure in a Crystal Report sub report?

  - by mattruma

  I have a Crystal Report, that is populated with a stored procedure, it also contains a sub report, that I am trying to populate with another stored procedure ... same parameters. I cannot figure out how to pass the parameter value to the sub report. Any help would be appreciated!

  Read the article

• MS SQL Server 2008 Stored Procedure Result as Column Default Value

  - by user337501

  First of all, thank you guys. You always know how to direct me when I cant even find the words to explain what the heck im trying to do. The default values of the columns on a couple of my tables need to equal the result of some complicated calculations on other columns in other tables. My first thought is to simply have the column default value equal the result of a stored procedure. I would also have one or more of the parameters pulled from the columns in the calling table. I don't know the syntax of how to do it though, and any time the word "stored" and "procedure" land next to each other in google I'm flooded with info on Parameter default values and nothing relating to what I actually want. Half of that was more of a vent than a question...any ideas though? And plz plz dont say "Well, you could use an On-Insert Trigger to..."

  Read the article

• SQl Server: serializable level not working

  - by Zé Carlos

  I have the following SP: CREATE PROCEDURE [dbo].[sp_LockReader] AS BEGIN SET NOCOUNT ON; begin try set transaction isolation level serializable begin tran select * from teste commit tran end try begin catch rollback tran set transaction isolation level READ COMMITTED end catch set transaction isolation level READ COMMITTED END The table "test" has many values, so "select * from teste" takes several seconds. I run the sp_LockReader at same time in two diferent query windows and the second one starts showing test table contents without the first one terminates. Shouldn't serializeble level forces the second query to wait? How do i get the described behaviour? Thanks

  Read the article

• MySQL: How do I combine a Stored procedure with another Function?

  - by Laxmidi

  Hi I need some help in combining a stored procedure with another function. I've got a stored procedure that pulls latitudes and longitudes from a database. I've got another function that checks whether a point is inside a polygon. My goal is to combine the two functions, so that I can check whether the latitude and longitude points pulled from the db are inside a specific area. This stored procedure pulls latitude and longitudes from the database based on offense: DROP PROCEDURE IF EXISTS latlongGrabber; DELIMITER $$ CREATE PROCEDURE latlongGrabber(IN offense_in VARCHAR(255)) BEGIN DECLARE latitude_val VARCHAR(255); DECLARE longitude_val VARCHAR(255); DECLARE no_more_rows BOOLEAN; DECLARE latlongGrabber_cur CURSOR FOR SELECT latitude, longitude FROM myTable WHERE offense = offense_in; DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE; OPEN latlongGrabber_cur; the_loop: LOOP FETCH latlongGrabber_cur INTO latitude_val, longitude_val; IF no_more_rows THEN CLOSE latlongGrabber_cur; LEAVE the_loop; END IF; SELECT latitude_val, longitude_val; END LOOP the_loop; END $$ DELIMITER ; This function checks whether a point is inside a polygon. I'd like the function to test the points produced by the procedure. I can hard-code the polygon for now. (Once, I know how to combine these two functions, I'll use the same pattern to pull the polygons from the database). DROP FUNCTION IF EXISTS myWithin; DELIMITER $$ CREATE FUNCTION myWithin(p POINT, poly POLYGON) RETURNS INT(1) DETERMINISTIC BEGIN DECLARE n INT DEFAULT 0; DECLARE pX DECIMAL(9,6); DECLARE pY DECIMAL(9,6); DECLARE ls LINESTRING; DECLARE poly1 POINT; DECLARE poly1X DECIMAL(9,6); DECLARE poly1Y DECIMAL(9,6); DECLARE poly2 POINT; DECLARE poly2X DECIMAL(9,6); DECLARE poly2Y DECIMAL(9,6); DECLARE i INT DEFAULT 0; DECLARE result INT(1) DEFAULT 0; SET pX = X(p); SET pY = Y(p); SET ls = ExteriorRing(poly); SET poly2 = EndPoint(ls); SET poly2X = X(poly2); SET poly2Y = Y(poly2); SET n = NumPoints(ls); WHILE i<n DO SET poly1 = PointN(ls, (i+1)); SET poly1X = X(poly1); SET poly1Y = Y(poly1); IF ( ( ( ( poly1X <= pX ) && ( pX < poly2X ) ) || ( ( poly2X <= pX ) && ( pX < poly1X ) ) ) && ( pY > ( poly2Y - poly1Y ) * ( pX - poly1X ) / ( poly2X - poly1X ) + poly1Y ) ) THEN SET result = !result; END IF; SET poly2X = poly1X; SET poly2Y = poly1Y; SET i = i + 1; END WHILE; RETURN result; End $$ DELIMITER ; This function is called as follows: SET @point = PointFromText('POINT(5 5)') ; SET @polygon = PolyFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'); SELECT myWithin(@point, @polygon) AS result I've tested the stored procedure and the function and they work well. I just have to figure out how to combine them. I'd like to call the procedure with the offense parameter and have it test all of the latitudes and longitudes pulled from the database to see whether they are inside or outside of the polygon. Any advice or suggestions? Thank you. -Laxmidi

  Read the article

• stored procedure with cursor in MySQL

  - by parag

  Can I use Cursor with stored procedure in MySQL? And can I receive the output values from it?

  Read the article

• How can I make a multi search SPROC/UDF by passing a tabled-value to it?

  - by Shimmy

  I actually want to achieve the following description This is the table argument I want to pass to the server <items> <item category="cats">1</item> <item category="dogs">2</item> </items> SELECT * FROM Item WHERE Item.Category = <one of the items in the XML list> AND Item.ReferenceId = <the corresponding value of that item xml element> --Or in other words: SELECT FROM Items WHERE Item IN XML according to the splecified columns. Am I clear enought? I don't mind to do it in a different way other than xml. What I need is selecting values that mach an array of two of its columns' values.

  Read the article

• How to add second JOIN clause in Linq To Sql?

  - by Refracted Paladin

  I am having a lot of trouble coming up with the Linq equivalent of this legacy stored procedure. The biggest hurdle is it doesn't seem to want to let me add a second 'clause' on the join with tblAddress. I am getting a Cannot resolve method... error. Can anyone point out what I am doing wrong? Below is, first, the SPROC I need to convert and, second, my LINQ attempt so far; which is FULL OF FAIL! Thanks SELECT dbo.tblPersonInsuranceCoverage.PersonInsuranceCoverageID, dbo.tblPersonInsuranceCoverage.EffectiveDate, dbo.tblPersonInsuranceCoverage.ExpirationDate, dbo.tblPersonInsuranceCoverage.Priority, dbo.tblAdminInsuranceCompanyType.TypeName AS CoverageCategory, dbo.tblBusiness.BusinessName, dbo.tblAdminInsuranceType.TypeName AS TypeName, CASE WHEN dbo.tblAddress.AddressLine1 IS NULL THEN '' ELSE dbo.tblAddress.AddressLine1 END + ' ' + CASE WHEN dbo.tblAddress.CityName IS NULL THEN '' ELSE '<BR>' + dbo.tblAddress.CityName END + ' ' + CASE WHEN dbo.tblAddress.StateID IS NULL THEN '' WHEN dbo.tblAddress.StateID = 'ns' THEN '' ELSE dbo.tblAddress.StateID END AS Address FROM dbo.tblPersonInsuranceCoverage LEFT OUTER JOIN dbo.tblInsuranceCompany ON dbo.tblPersonInsuranceCoverage.InsuranceCompanyID = dbo.tblInsuranceCompany.InsuranceCompanyID LEFT OUTER JOIN dbo.tblBusiness ON dbo.tblBusiness.BusinessID = dbo.tblInsuranceCompany.BusinessID LEFT OUTER JOIN dbo.tblAddress ON dbo.tblAddress.BusinessID = dbo.tblBusiness.BusinessID and tblAddress.AddressTypeID = 'b' LEFT OUTER JOIN dbo.tblAdminInsuranceCompanyType ON dbo.tblPersonInsuranceCoverage.InsuranceCompanyTypeID = dbo.tblAdminInsuranceCompanyType.InsuranceCompanyTypeID LEFT OUTER JOIN dbo.tblAdminInsuranceType ON dbo.tblPersonInsuranceCoverage.InsuranceTypeID = dbo.tblAdminInsuranceType.InsuranceTypeID WHERE tblPersonInsuranceCoverage.PersonID = @PersonID var coverage = from insuranceCoverage in context.tblPersonInsuranceCoverages where insuranceCoverage.PersonID == personID join insuranceCompany in context.tblInsuranceCompanies on insuranceCoverage.InsuranceCompanyID equals insuranceCompany.InsuranceCompanyID join address in context.tblAddresses on insuranceCompany.tblBusiness.BusinessID equals address.BusinessID where address.AddressTypeID = 'b' select new { insuranceCoverage.PersonInsuranceCoverageID, insuranceCoverage.EffectiveDate, insuranceCoverage.ExpirationDate, insuranceCoverage.Priority, CoverageCategory = insuranceCompany.tblAdminInsuranceCompanyType.TypeName, insuranceCompany.tblBusiness.BusinessName, TypeName = insuranceCoverage.InsuranceTypeID, Address = };

  Read the article

• Inline Conditional Statement in Stored Procedure

  - by Jason

  Here is the pseudo-code for my inline query in my code: select columnOne from myTable where columnOne = '#variableOne#' if len(variableTwo) gt 0 and columnTwo = '#variableTwo#' end I would like to move this into a stored procedure but am having trouble building the query correctly. I assume it would be something like select columnOne from myTable where columnOne = @variableOne CASE WHEN len(@variableTwo) <> 0 THEN and columnTwo = @variableTwo END This is giving me a syntax error. Could someone tell me what I've got wrong. Also, I would like to keep it to only one query and not just have one if statement. Also, I do not want to build the sql in the stored procedure and run Exec() on it.

  Read the article

• Export products and variants from SQL Server

  - by mickyjtwin

  I have a SQL Server DB that has a table of products, and another table which contains a list of the sku variants of each product if it has one. I want to export all the products and their SKU's into excel. At the moment, I have a helper SQL function which performs the subquery against a product_id and concatenates all the SKU's into a comma-delimited string, e.g: Product Code, Name, SKUs 111 P1 77, 22, 11 Is there an easier way to do this, so that each SKU is a row which the associated product code as well, i.e: Product Code, Name, SKUs 111 P1 77 111 P1 22 111 P1 11

  Read the article

< Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >