stored-procedures

SQL Server / 2 select in the same Stored procedure

Here's what I'm trying do to in a single SQL Server procedure: @ID1 int select ID2 from TableA where ID1 = @ID1 (for each selected @ID2) select * from TableB where ID2 = @ID2 Any ideas? ...

Loading Entity from different table in EF4

Lets say I have two entities in my EF model Customer and Address which map to their respective Customer and Address tables in the db. What I'd like to do is load up a Customer and their Address from the db into these entities but from different tables with slightly different schemas. They will then be saved to the Customer and Address ...

CLR Sproc Permissions

When I'm trying to execute a clr sproc (Still in C# IDE for this) I have permission errors. The error is " Request for the permission of type 'System.Data.SqlClient.SqlClientPermission,... failed", I've looked around online and it seems to throw back to IIS settings (I'm not using IIS nor am I using a website so these solutions don't app...

What am I doing wrong in this MySQL stored procedure?

I'm trying to use the following stored procedure. DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `DeleteField`( IN _TABLENAME Text, IN _FIELDNAME text) BEGIN if exists (select * from information_schema.Columns where table_name = _TABLENAME and column_name = _FIELDNAME) then alter table _TABLENAME drop column _...

Need help for general sql stored procedure for pagination

Hi, I've created following stored procedure in my SQL server 2005 database for general pagination: USE [training] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_Pagination1] -- Add the parameters for the stored procedure here @SqlColumns VARCHAR(MAX), @SqlFriendlyColumns VARCHAR(MAX), ...

listing records using stored procedures

I'm having a little practice regarding mysql stored procedures. Here is the php file which calls it: <?php include('conn.php'); $cor=$_POST['course']; $sql="CALL list('$cor')"; $result=mysql_query($sql); ?> <table> <tr> <th>Name</th> <th>Course</th> </tr> <?php while($row=mysql_fetch_assoc($result)){ ?> <tr> <td><?php echo $row['N...

Stored procedure call is very slow from Entity Framework 4 due to inexplicable reason

Stored procedure (query ) when run in Management Studio takes 4-8 seconds. However, when launched inside WCF service via Entity Framework it may take more than minute. WCF runs in VS 2010 in debugging mode with ASP.Net Development Web Server. The actual call requests = transactionEntities.spRequestsForRescreening(cutoffDate).ToArray(...

mysql stored procedure that calles itself recursively

I have the following table: id | parent_id | quantity ------------------------- 1 | null | 5 2 | null | 3 3 | 2 | 10 4 | 2 | 15 5 | 3 | 2 6 | 5 | 4 7 | 1 | 9 Now I need a stored procedure in mysql that calles itself recursivly and returns the computed quantity. For example the i...

In a PL/SQL procedure, how do I pass a table name as a parameter?

CREATE PROCEDURE A(tab IN <table - what should I write here?>) AS BEGIN INSERT INTO tab VALUES(123); END A; How can I specify that the parameter tab is a table name? ...

How do I use dynamic SQL to declare a column name derived from a table name?

Building on Tony's answer on this question: If I want to do something like this, CREATE PROCEDURE A(tab IN VARCHAR2) IS tab.col_name <column> --static declaration (column name always remains the same) BEGIN EXECUTE IMMEDIATE 'INSERT INTO ' || tab(col_name) || 'VALUES(123)'; END A; How can I use Dynamic SQL in the above case? ...

Optimized query to get min/max

I have data as Employee: id Name -------- 1 xyz 2 abc 3 qaz Employee_A: (Eid - employee table, title - title table) eid active type title ------------------------------ 1 1 1 1 1 1 2 2 1 1 4 3 2 0 3 4 2 1 2 2 2 0 ...

Adding the iTextSharp dll for use with CLR Stored Procedures

Has anyone had any luck creating an assembly for iTextSharp for use with CLR Stored procedures? I've been trying all afternoon and have not been able to get any results. SQL management studio is saying I need to register system.drawing first (can't be the new version, has to be the old version no less) but it gives me issues when I try...

Advanced SQL books - tutorials

Hi My situation is i know the basics of SQL and am a little rusty on stuff to do with joins etc. Id like a book which covers relational databases(info about them etc), understanding normalisation, set theory and then more advanced concepts such as stored procedures, cursors and so on. Tutorials, books, anything is good! Obviously if t...

Search Problem in SQL Server

I need to search in a table for items which have all of my desired values in a column i.e. I have table : ID : 1 2 3 3 2 2 2 1 1 3 VALUE : 5 6 5 3 6 7 2 1 9 0 I want to give a StoredProc a list of values for example ("6,7,2") and it returns me all IDs that have all the given values in this case it would only returns 2 If I wanted ...

Using For loop to retrieve multiple rows in Oracle procedure

Hi all, Im working on stored procedure where I need to retrieve a set of results and process each element individually and then return the entire result.(using 3 different tables) Im not too familiar with databases, but heres what I was able to come up with.. create or replace procedure GET_EMP_RSLT IS CURSOR ecursor IS select emp...

passing list of name/value pairs to stored procedure

I have a name/value pair in a List<T> and needing to find the best way to pass these to a stored procedure. Id Name 1 abc 2 bbc 3 cnn .... ... What is the best way to accomplish this? ...

Using table variables in stored procedures versus merely selecting from tables or a view?

I'm looking at sprocs right now that seem to follow the behavior demonstrated below DECLARE @tablevar TABLE ( FIELD1 int, FIELD2 int, FIELD3 varchar(50), -- etc ) INSERT INTO @tablevar ( FIELD1, FIELD2, FIELD3, -- etc ) SELECT FIELD1, FIELD2, FIELD3, -- etc FROM TableA Inner Join TableB on TableA.F...

Need help creating complex T-SQL SELECT statement.

I am trying to make SELECT statement for following situation and need help to make this SELECT statement. It's SQL Server 2005. When the select statement is run, it should return rows which have SentDate as NULL assuming that there are no duplicate PersonID in table. It will return result set with Status as 'Initial Record' (as the same...

SQL Server 2000 Output Parameters

I have created a stored procedure in SQL Server 2000 and within the stored procedure I have created a number of variables that I want to return to the asp classic script. I have tried declaring the variable like this: DECLARE @output int OUTPUT But SQL Server then says that I cannot declare an output variable that way. So instead, I...

Migrating a stored procedure using REF CURSOR from Oracle to DB2

I'm currently migrating a package of procedures from Oracle to DB2, with the assistance of the IBM Migration Toolkit. MTK unfortunately chokes on custom types, such as: TYPE G_reference_cursor IS REF_CURSOR ... FUNCTION do_some_stuff RETURN g_reference_cursor ... What would be the idiomatic way of migrating these statements to DB2?...