stored-procedures

SubSonic - calling sproc's w/o parameters?

I noticed something with SubSonic 2.x that I'm sure others have run into... Calling a sproc with results = SPs.SpGetCountryList().GetDataSet(); I kept getting a Null reference exception. To get around this, I added a dummy parameter in my sproc, and called the same function with an irrelevant number - SPs.SpGetCountryList(1).GetDataSe...

How To use a stored procedure in a crystal reports 8.5?

Hi all.... I have to make a new report using crystal report8.5 . I have created a stored procedure in SQL Server 2005. The stored procedure has one input parameter. Now I wanna to know that how I can add that stored procedure and show its result in my report while designing that report? thank you ...

How to block returning a resultset from stored procedure?

I have a stored procedure that returns multiple resultsets, it looks something like this BEGIN SET NOCOUNT ON; SELECT c1, c2, c3 FROM t1 WHERE id = @id IF (@@ROWCOUNT = 0) BEGIN SELECT c1, c2, c3 FROM t2 WHERE id = @id END END I use this stored procedure in my front-end ASP.NET website. If...

Extending a stored procedure (adding a single part to it) by using a parameter in SQL Server

I have a 200 line long stored procedure, which gets a parameter 'prmtr', What I want to do is add an "sql part" to my stored procedure, according to my parameter. example: SELECT A.* FROM ( SELECT * FROM table1 ) A IF (my parameter) = a LEFT JOIN ( SELECT * FROM table2 ) B ON A.ID= B.ID ...

SQL - Is there a better way of passing a list of keys, for use in a where clause, into a Stored Procedure?

Here's the scenario; I have a list of CustomerIds (1, 2, 3) that have relating OrderIds. I have one Stored Procedure Delete_OrdersByCustomerIds, which deletes all the orders that are related to the CustomerIds specified. Currently, the way I do this is to concatenate the CustomerIds into a string, i.e. "1,2,3". I then pass this string t...

Linq To Sql optional Column

My problem in short: I need a functionality to have optional columns in a linq to sql definition. So that linq to sql normally ignores this column within selects and updates etc. But if a select contains a value for this column it should use this value. Long version: The Scenario I've the following tables: If Field.FieldViews.Coun...

TSQL SELECT then UPDATE in one transaction, then return SELECT

I am really having trouble with a query in my ColdFusion application (backended to MS SQL 2008). I keep getting DB deadlock errors on this transaction: <code> <cftransaction> <cfquery name="selectQuery"> SELECT TOP 20 item_id, field2, field3 FROM Table1 WHERE subject_id = #subject_ID# AND lock_field IS NULL AND ...

Looping Over Result Sets in MySQL (Resolved: Using Cursors)

I am trying to write a stored procedure in MySQL which will perform a somewhat simple select query, and then loop over the results in order to decide whether to perform additional queries, data transformations, or discard the data altogether. Effectively, I want to implement this: $result = mysql_query("SELECT something FROM somewhere ...

What is the VBA equivalent for using Command.Prepare in ADO.NET

Is there a way to use Command.Prepare, and Command.ExecuteNonQuery in VBA? The section entitled "Consider Using Command.Prepare" in the following article on ADO.NET describes objects and methods that are similar to ADODB properties and methods and I am wondering if there is an equivalent for VBA. ADODB has .Command, .Prepared, and .Par...

Can I do it only with SQL Queries or do I need a stored procedure?

Hi guys. I came across with a problem in which I need some advice/help. Background: I'm programming a map in an accounting software which requires special formatting and a tree-like hierarchy between the account codes, i.e.: Account 1 is the father of accounts 11, 12 and 13; On the other hand, account 11 is the father of accounts 11...

Passing an enum value as a stored procedure (data function) parameter in LINQ to SQL

As mentioned in this question, "LINQ to SQL allows table mappings to automatically convert back and forth to Enums by specifying the type for the column - this works for strings or integers." However, I am having trouble with this when using stored procedures (instead of auto-generated SQL) to create and update entities. In my database...

(Probably) Simple SQL Server cursor question

I need to call two stored procedures in sequence via ODBC in PHP: #run stored procedure 1 $query = "Shipped_Not_Shipped_Rep ".$_GET['rep_id']; $result = odbc_exec($dbh, $query); odbc_result_all($result); #run stored procedure 2 $query = "Shipped_Not_Shipped_Account ".$_GET['account_id']; $result = odbc_exec($dbh, $query); odbc_result_a...

question about pl/sql stored program text

hi all. I use TOAD to do my PL/SQL development. In TOAD when i type a procedure name and press f4, I can see this procedure's source code. I think TOAD get the source code from v$sqltext view. To confirm my thought, I wrote a query: select * from v$sqltext but when I execute the upper query, Oracle give me an error: ORA-00942: tab...

Using T-SQL AVG or taking Average after results returned using LINQ

I have a stored procedure that uses a view to pull 6 averages. The SQL database is SQL Server 2000. When I run it in the Query analyzer, it takes roughly 9 seconds. What can I do to get better performance? Should I return the rows using LINQ and determine an average that way? Will it be faster? Here's an example of my current sproc: cr...

Is the usage of stored procedures a bad practice?

Hi, We have an application that is written in C# that is connected to a ms sql server. We use to make a stored procedure for every database call, but then we've noticed that using stored procedures gives us a very big disadvantage, we don't know what stored procedures we need to update if we change our database. Now I was wondering if ...

Return variable from stored procedure MySQL

How can I return a declared string like ( lastInsertId ) from my MySQL stored procedure and out? It's really annoying I can't return error messegts, complate messages and more out to my code in PHP5. I hope somebody can help me here, I have search Google around without luck :( Thanks to everybody. ...

error when creating PROCEDURE on mysql

Am trying to create a mysql stock procedure, but i have the following error Script line: 2 Failed to CREATE PROCEDURE proc_test_bideep The syntax proc is: DELIMITER $$ DROP PROCEDURE IF EXISTS `commun`.`insert_categorie` $$ CREATE PROCEDURE `commun`.`insert_categorie` (id_mere INT, ...

SSRS2005 timeout error

Hi I've been running around circles the last 2 days, trying to figure a problem in our customers live environment. I figured I might as well post it here, since google gave me very limited information on the error message (5 results to be exact). The error boils down to a timeout when requesting a certain report in SSRS2005, when a ce...

Windows Application SqlDepedency Calling Onchange infinitely

Hi, I have console application in which I am doing sqldependency. My problem is when I set commandType as Text, it is working fine. But if I use commandType as StoredProcedure, onchange method is calling infinitely. Please see the code below: static DataSet myDataSet; static SqlConnection connection; static S...

MySQL temporary vs memory table in stored procedures

What's is better to use in a stored procedure: a temporary table or a memory table? The table is used to stored summary data for reports. Are there any trade offs that developers should be aware off? CREATE TEMPORARY TABLE t (avg (double)); or CREATE TABLE t (avg (double)) ENGINE=MEMORY; ...