stored-procedures

Step through mysql stored procedure

Is it possible to step through a mysql stored procedure, watching the values of variables at each step. ...

Oracle global lock across process

I would like to synchronize access to a particular insert. Hence, if multiple applications execute this "one" insert, the inserts should happen one at a time. The reason behind synchronization is that there should only be ONE instance of this entity. If multiple applications try to insert the same entity,only one should succeed and other...

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

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...

sql server 2005 stored procedure unexpected behaviour

i have written a simple stored procedure (run as job) that checks user subscribe keyword alerts. when article posted the stored procedure sends email to those users if the subscribed keyword matched with article title. One section of my stored procedure is: OPEN @getInputBuffer FETCH NEXT FROM @getInput...

Stored procedure with output parameters vs. table-valued function?

Which approach is better to use if I need a member (sp or func) returning 2 parameters: CREATE PROCEDURE Test @in INT, @outID INT OUT, @amount DECIMAL OUT AS BEGIN ... END or CREATE FUNCTION Test ( @in INT ) RETURNS @ret TABLE (outID INT, amount DECIMAL) AS BEGIN ... END What are pros and cons of each approach con...

Force INSERT only via stored procedure

Using SQL Server 2008, is there a way to allow inserts to a table only via a stored procedure, and if so how? EDIT: The best way to go is probably Martin Smith's suggestion to use an INSTEAD OF INSERT trigger. The direct answer to this question is marc_s' one with GRANT and DENY, though it won't limit some user accounts. ...

How to export more than 1MB in XML format using sqlcmd and without an input file?

Hello, In SQL Server 2008, I want to export the result of a stored procedure to a file using sqlcmd utility. Now the end of my stored procedure is a select statement with a "for xml path.." clause at the end. I read on BOL that if I don't want my output truncated when reaching 1MB file size, I have to use this :XML ON command, but it s...

Building Stored Procedure to group data into ranges with roughly equal results in each bucket

I am trying to build one procedure to take a large amount of data and create 5 range buckets to display the data. the buckets ranges will have to be set according to the results. Here is my existing SP GO /****** Object: StoredProcedure [dbo].[sp_GetRangeCounts] Script Date: 03/28/2010 19:50:45 ******/ SET ANSI_NULLS ON GO SET QUO...

How to call stored proc from ASP.Net MVC stack via the ORM & return them in json?

Hi guys, i'm a total newbie with asp.net mvc and here's my jam: i have a 3 level list box which selection on box A shows options on box B and selection on box B will show the options for box C. I'm trying to do the whole thing in asp.net MVC and what i see is that the nerd dinner tutorial uses the ORM method. so i created a dbml to ...

Eclipse Debug Mode disrupting SQL Server 2005 Stored Procedure access

We have a strange problem in our team. When a developer is using Eclipse in Debug mode, SQL Server 2005 blocks other developers from accessing a stored procedure. Debug session typically involves opening Hibernate session to persist an entity which could be accessing a stored procedure used for Primary key generation. Debugging is done i...

Is there an "are you sure" for stored procedure execution? :)

I have a stored procedure which is doing a lot of delete. Hundreds of thousands of records. It is not going to be runnable from the application, but still, i am concerned, that one of my clients accidentally runs it (i had problems earlier due to their "curiosity") :D Yes. there are backups and stuff like that, but I was thinking .... n...

Row as XML in SP

From SP i need to get a row as as XML (Includeing all fileds.) Is there any way we can get like below. Declare @xmlMsg varchar(4000) select * into #tempTable from dbo.order for xml raw select @xmlMsg = 1 from #tempTable print '@xmlMsg' + @xmlMsg Row i would like to get it as XML output. ...

Advantage Database Server: slow stored procedure performance.

I have a question about a performance of stored procedures in the ADS. I created a simple database with the following structure: CREATE TABLE MainTable ( Id INTEGER PRIMARY KEY, Name VARCHAR(50), Value INTEGER ); CREATE UNIQUE INDEX MainTableName_UIX ON MainTable ( Name ); CREATE TABLE SubTable ( Id INTEGER PRIMARY ...

typed-Dataset VS Own access Database classes

Hi i would like to know if it's better to use typed-dataset rather than Own access with Datareader and Store procedure? ...

Linq-to-SQL- A stored procedure returns field which is not there in Mapped Table Info

We are using Linq-to-SQL with stored Procedures we need help on one of our problem. We are having user tables which is having all field required for user table like firstname,lastname and other fields that table is also there in our DBML files.Now the problem is that we are having a stored procedure that is mapped to table->User returnin...

How do you edit a stored procedure in MySQL?

The title says it all. I can't seem to google the syntax for editing an already-created stored procedure in MySQL. I'd be happy with a link. ...

Pick Return Values of Stored Procedure

Hi, I have a stored procedure that returns a result with 250!!! columns. But I only need 3 out of the 250. I want to call the SP and put only the 3 column values in a temp table. I don't want to define a temp table with 250 columns! This is how I would like to do it, but this doesn't work of course: create #myTempTable (id int, valu...

Combine stored procedure and query in T-SQL

What ways are there to combine executing of a stored procedure and using it's result or parameters in a regular SQL query? Or not supported yet but planned in future versions of SQL Server. I'm afraid that I use variables when it's possible do not. I mean next: -- passing result of SELECT to SP SELECT a, b FROM t EXEC my_sp a, b -- pa...

Use SQL to filter the results of a stored procedure

I've looked at other questions on Stack Overflow related to this question, but none of them seemed to answer this question clearly. We have a system Stored Procedure called sp_who2 which returns a result set of information for all running processes on the server. I want to filter the data returned by the stored procedure; conceptually,...

Please take a stab at this VB.Net Oracle-related sample and help me with String.Format.

If the database is not Oracle, it is MS SQl 2008. My task: if Oracle, add two more parameters when calling a stored proc. Oracle and MSFT stored procs are generated; Oracle ones have 3 extra parameters: Vret_val out number, Vparam2 in out number, Vparam3 in out number, ... the rest (The are not actually named Vparam2 and Vparam3, but...