stored-procedures

how can i initialize an stored procedures IN parameter to a default value?

when this stored procedure executes without this parameter, stored procedure should use the default value and if this parameter is assigned to a value, stored procedure should execute by assigned value. ...

Many to Many DataSet Insert to Database using Stored Procedures

I'm trying to do a many-to-many bulk insert to the database using a list of three different types of models. The only tools I can use are stored procedures and datasets. (But I can't use DataSet fills or DataAdapters, just plain ol' Stored Procedures). Is there an easy way to pass multiple values, let alone, pass three different lists an...

Changing Database Names and Cross Database Queries In Stored Procedures

Hi, I have a number of related databases that are version-ed. Instance of different versions may run side by side, identified by their different versions, i.e. [NorhwindV1.1] and [NorhwindV1.2] may be on the same server, along with [SouthwindV1.1] and [SouthwindV1.2]. There are a number of stored procedures that make cross database q...

Stored Procedure: Reducing Table Data

Hi Guys, A simple question about Stored Procedures. I have one stored procedure collecting a whole bunch of data in a table. I then call this procedure from within another stored procedure. I can copy the data into a new table created in the calling procedure but as far as I can see the tables have to be identical. Is this right? Or i...

What is your best-practice advice on implementing SQL stored procedures (in a C# winforms application)?

I have read these very good questions on SO about SQL stored procedures: When should you use stored procedures? and Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? I am a beginner on integrating .NET/SQL though I have used basic SQL functionality for more than a decade in other environment...

nHibernate Storeprocedure Call

I want to call the sp from the application with out specifying anything in the mapping file my sp return is a list. public IList<INewItem> GetItemsByfilter(IList<object> Filters) { // call sp with out using the mapping file sp returns a dataset } ...

Some questions about the .NET Entity Framework and Stored Procedures

Hey everyone, I had a couple of questions relating to the .NET Entity Framework and using stored procedures. Here goes: I know that we can right click a stored procedure and choose Function Import to be able to use with code. Is there a way to do this for many stored procedures at once? When doing a Function Import, I can create a ne...

T-SQL Dynamic SQL and Temp Tables

It looks like #temptables created using dynamic SQL via the EXECUTE string method have a different scope and can't be referenced by "fixed" SQLs in the same stored procedure. However, I can reference a temp table created by a dynamic SQL statement in a subsequence dynamic SQL but it seems that a stored procedure does not return a query r...

locked stored procedures in sql

Hi, I am not too familiar with sql server 2005. I have a schema in sql which has stored procedures with small lock on them. As I understand they were created using C#, all these locked procedures have a source file in C# with the code of the procedures. The thing is I can't access them. I need to modify one of these procedures but it ...

How to use local variables in stored procedures?

if I want to select any id from a table and want to insert it's value in another table as foreign key then how i will do it through stored procedure? ...

How to automatically generate documentation of stored procedures of SQL Server 2008?

How to automatically generate documentatation of stored procedures of SQL Server 2008 like doxygen does to codes? I read about hyperSQL but it doesn't work nice with Windows. Thanks! Gil. ...

What would cause linq to sql to think that a parameter is nullable?

Hi I have a stored procedure that declares a input parameter @BatchNumber uniqueidentifier This field in the table is set to not allow nullables. When I drag in the stored procedure and it hooks everything up it says the parameter is nullable. I don't understand why. Is this normal for it to think that the parameters are nullable?...

Stored Procedure for one-to-many relation

Hi I'm using GlassFish ESB to for example to store data recieved from a Microsoft WCF webservice in SQL Server 2005. The problem I'm faced with is firstly that i have to insert into three tables, the usual approach would be to create a DB Binding in a "BPEL" but that has the problem of me having to create one binding for each table so I...

stored procedure with cursor in MySQL

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

I need to run a stored procedure on multiple records

I need to run a stored procedure on a bunch of records. The code I have now iterates through the record stored in a temp table. The stored procedure returns a table of records. I was wondering what I can do to avoid the iteration if anything. set @counter = 1 set @empnum = null set @lname = null set @fname = null --...

MySQL: How to consume/discard the result of a query?

I have a stored procedure which executes an optimize table statement for every table in a DB. Those optimize table statements are prepared statements of course (they have to be built at runtime) and I need to call that procedure from PHP using ext/mysql API. Unfortunately, ext/mysql does't support doing such thing because optimize table...

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

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

Ad hoc queries vs stored procedures vs Dynamic SQL

Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons? Best regards, Kristaps ...

mysql changing delimiter

I'm trying to add this function using php myadmin, first off I get on error line 5, which is apparently because you need to change the delimiter from ; to something else so i tried this DELIMITER | CREATE FUNCTION LEVENSHTEIN (s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS INT DETERMINISTIC BEGIN etc.... END DELIMI...

Variable collation with MySQL stored function?

I want to do something like this in a stored procedure: IF case_sensitive = FALSE THEN SET search_collation = "utf8_unicode_ci"; ELSE SET search_collation = "utf8_bin"; END IF; INSERT INTO TABLE1 (field1, field2) SELECT * FROM TABLE 2 WHERE some_field LIKE '%rarf%' collate search_collation; However, when I do this, I get ERROR 1...