I have a view like this: CREATE VIEW MyView AS SELECT Column FROM Table WHERE Value = 2; I'd like to make it more generic, it means to change 2 into a variable. I tried this: CREATE VIEW MyView AS SELECT Column FROM Table WHERE Value = @MyVariable; But mysql doesn't allow this. I found an ugly workaround: CREATE FUNCTION Ge...
I have a parent/child relationship defined which, unfortunately, cannot be maintained via a foreign key. Parents and children are stored in the same table. The parent/child relationship is identified by a column "ITEM_ID". A child ITEM_ID consists of its parent ITEM_ID and then what we can effectively think of as the child's unique id...
I'm tracking down an odd and massive performance problem in my SQL server installation. On my system, a particular stored procedure takes 2 minutes to execute; on a colleague's system it takes less than 1 second. We have similar databases/data and configurations, but there's obviously something very different. I ran the SP in question t...
On an MS SQL Server 2000 installation I have numerous stored procedures that pull data from databases other than the one it's stored in. All selects occur on the same database server. For example: select * from [OtherDatabase]..table How can I find which procedures do that sort of thing without eyeballing each one? ...
Do you guys see any problems with the way I handle errors in the following stored procedure? I am still not confident enough with SQL to realize if I am doing anything wrong. Thank you :) CREATE PROCEDURE [dbo].[UserAccounts_Create] @username varchar(255), @email varchar(255), @password nvarchar(127), @id bigint OUTPUT A...
hi every body iam new to Entity framework i have learned about using SP and how to map (insert \ delete \ update) in every table and how to import function to call SP that returns values from one table my question is hot can i to call SP that returns multiple columns from multiple tables in LINQ to SQL generating class that represent...
I have an SQL Function with the following SQL within: SELECT StockID FROM (SELECT DISTINCT StockID, ROW_NUMBER() OVER(ORDER BY DateAdded DESC) AS RowNum FROM Stock WHERE CategoryCode LIKE @CategoryID) AS Info WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1 I have a Parameter @CategoryID - however I need to...
I'm creating a sproc that will insert rows into a 'staging' table with an insert into + subquery like so: INSERT INTO myStagingTable SELECT col1, col2, col3 FROM myRealTable I need to put a conditional in there somehow to determine if the value from col1 for example already exists on myStagingTable, then don't insert it, just skip tha...
Here's my cursor: CURSOR C1 IS SELECT * FROM MY_TABLE WHERE SALARY < 50000 FOR UPDATE; I immediately open the cursor in order to lock these records for the duration of my procedure. I want to raise an application error in the event that there are < 2 records in my cursor. Using the C1%ROWCOUNT property fails because it only counts t...
I am looking to insert and update records in a database using functions and logic that are not available in SQL Server or any other RDBMS for that matter. After Googling around a bit this morning, I have come across the concept of Extended Stored Procedures. As far as I can tell, I should be able to compile my desired functionality int...
hey i want to make a search feature with mysql stored procedures and php. how can i do this? i know how to call proc but how to send "q" input to procedure? thank you ...
I have an ODBC connection set up on my Windows 2008 Server, and I'm trying to replace some .BAT files that do some processing with Powershell files. Is there a way to do the same thing as this in PowerShell? CALL osql /instanceName /Uuser /Ppassword /Q"EXECUTE storedProcName @Parm1= %ePROFILE%, @param2 = N'%eValList%' ...
As you can probably very soon see, I am a complete newbie at VB.NET, and I am having some trouble getting output from a stored procedure in SQL Server 2005. This is the code I use Dim con As New SqlConnection Dim cmd As New SqlCommand("esp_getDates", con) Dim par As New SqlParameter("@PlaceID", SqlDbType.Int, 3906) con....
Hello, I am trying to populate a couple text box fields in my MVC application. I have a text box that a user can enter an ID and then click search, and based on the ID input from the user, information should be brought back to populate First Name, Last Name text boxes on the same page. The problem I am having is bringing back this d...
We have a high volume web application based on ASP.NET 3.5 and SQL 2008 where we hope to maintain high availability levels 24x7 without the need for a maintenance window. Over time, we have become reliant upon some stored procs which perform housekeeping operations to purge data which is no longer needed, compile some metrics, etc. O...
Hi, I have a PostgreSQL stored procedure which loops over a very large list, and makes changes to some of its members using UPDATE. Is there a way to commit these changes per iteration, not at the end of the function execution? It would allow me to run the function for shorts periods of time, making small changes at each run. Thanks, ...
I'm a complete newbie to VB.NET, how can I test if a dataset has rows? I'm trying to see if my sql procedure call managed to fill up my dataset. Also, is there an easy way to print the rows to a label? ...
I am using this vb.net code file to call a procedure to get dates (among other things). When I execute this procedure in SQL 2005 Server Management Studio, I get about 10 columns. However when I execute this code, the dataset seems to only have one index value, maybe I am misunderstanding something. When I change this ds.Tables(0).Rows...
If I have a procedure definition that has been stored in source control, is it necessary or helpful to keep the 'tombstone' up to date as well? Here's what I mean by a 'tombstone': CREATE proc [dbo].[getCreditTransactions] AS /* 2001-02-12 jdoe : created proc 2003-04-15 kdoe : added handling for credit business rules */ etc...
What is the difference between ; and GO in stored procedure in SQL Server ? Actually, if I have a stored procedure in SQL server and wanna to put t separate queries inside it which the first one just calculates number of records (count) and the second one selects some records based on some conditions, then what sould I use between that ...