I have an Oracle function returning record defined in the package, so one can do: select a,b,c FROM my_function(...); Calling this oracle function from .NET is as simple as executing normal sql query. Unfortunately the function has to do updates now and when it is called like this Oracle complains that updates are not allowed within ...
I have a stored procedure that basically calls 7 or 8 other stored procedures. I'd like know if recompiling the outer stored procedure will cause the 7 or 8 to also be recompiled? ...
At the moment I write stored procedures this way: create proc doStuff @amount int as begin try begin tran ... if something begin select 'not_good' rollback return end if someelse begin select 'some_other_thing' rollback return end --do the stuff ... commit end try begin catch if @@trancount > 0 rollback select 'error' end catch th...
I'm building an app around Vici Coolstorage (asp.net version). I have my classes created and mapped to my database tables and can pull a list of all records fine. I've written a stored procedure where the query jumps across databases that aren't mapped with Coolstorage, however, the fields in the query result map directly to one of my c...
Hi, I need to execute some Sql server stored procs in a thread safe manner. At the moment I'm using software locks (C# locks) to achieve this but wonder what kind of features provided by the Sql server itself to achieve thread safety. It seems to be there are some table and row locking features built in to Sql server. Also from a perf...
i created an edmx in visual studio c# web application. after creating the edmx and mapped the stored procedures, i found out that there was a missing relation between 2 tables. so i went back to the sqlserver database and corrected the FK relation between the 2 tables, and i updated the edmx. i also updated the stored procedure mapping i...
I have a huge disgusting stored procedure that wasn't slow a couple months ago, but now is. I barely know what this thing does and I am in no way interested in rewriting it. I do know that if I take the body of the stored procedure and then declare/set the values of the parameters and run it in query analyzer that it runs more than 20x...
I have select, insert, update and delete query. If I have to write all queries in the same stored procedure that is good for performance or should I write all queries in separate stored procedures? ...
I have a stored procedure which basically does something like select top 1 expiryDate, flag, (bunch of other columns) from someTable (bunch of joins) order by expiryDate desc So this will grab the record that expires last. This works for most cases, except some records have a flag that are just a char(1). Most of the time it's just Y ...
Hi everyone I thought I would use a stored routine to clean up some of my more complex SQL statements. From what I've read, it seems impossible to use a stored procedure within an sql statement, and a stored function only returns a single value when what I need is a result set. I am using mySQL v5.0 SELECT p.`id`, gi.`id` FROM `sport...
I used SQL Server 2005 for my small web application. I Want pass parameters to SP . But there is one condition. number of parameter that can be change time to time. Think ,this time i pass name and Address , next time i pass name,surname,address , this parameter range may be 1-30 , ...
Today I run this select 'exec sp_refreshview N''['+table_schema+'].['+table_name+']''' from information_schema.tables where table_type = 'view' This generates a lot of: exec sp_refreshview N'[SCHEMA].[TABLE]'. I then copy the result to the query editor window and run all those execs. How do I do this all at once? I would like to have...
I'm a SQL noob, and I need a little bit of help understanding the big picture of if it is possible, and if so how to go about filtering a result set based on the return value of a function which is passed one of the fields of the record. Let's say I have a table called "Numbers" with just one field: "Value". How could I correctly speci...
Hi, I'm executing a SQL stored procedure from a c# code but it is not responding at all. There is no exception or error generated. The processing as if gets hung. The stored procedure consists of multiple update statements and a select statement. The stored procedure is running fine independently and takes about 3-5 minutes to execute w...
I'm deploying an example SQL CLR stored procedure which has a SQL CLR type as parameter using Visual Studio 2008 and menu Project -> Deploy. public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void TakeTariff(TariffInfo tariffInfo) { } } public class TariffInfo { public SqlDecimal...
On my SQL Server 2008 I have a stored procedure with a large number of parameters. The first part of them is used in every call and parameters from the second part are used rarely. And I can't move the logic to two different stored procedures. Is there a way to encapsulate all this parameters to a class or struct and pass it as a store...
Hi everyone, I have a stored proc I'd like to debug in the SQL Server 2008 management studio. I see a number of tutorials about doing this, but the ones I've seen don't have any input parameters going into the SP. My SP has several, and an output parameter as well. Could someone show me how to do debug a stored procedure with paramet...
Created a simple stored procedure in MySQL: CREATE PROCEDURE `proc01`() BEGIN SELECT * FROM users; END Start Rails console: $ script/console Loading development environment (Rails 2.3.5) >> User.connection.execute("CALL proc01") => #<Mysql::Result:0x10343efa0> Looks good. BUT, any more call to the same stored procedure via the exi...
I have a simple table where I have to extract some records. The problem is that the evaluation function is a very time-consuming stored procedure so I shouldn't to call it twice like in this sentence: SELECT *, slow_sp(row) FROM table WHERE slow_sp(row)>0 ORDER BY dist DESC LIMIT 10 First I thought in optimize like this: SELECT *, sl...
Hi, I have a stored procedure which executes another procedure inside it. The second one sometimes takes a while to run but the first seems to finish before waiting for the second. This has resulted in missing data, which should have been updated by the second procedure. Is there a timeout limit within the first procedure and can this ...