PostgreSQL functions select*
How can I use select * from some_table in a function in postgres? ...
stored-procedures
How to use SqlDataReader if I'm having more than one select statements in a stored procedure
I have coded three select statements in stored procedure in Microsoft SQL Server 2005. Both select statements return multiple number of records and table list for select statements is different. One select records from a master table and the other from a child table. In C# code I want to get all these records and put all the data in one ...
How do I use LINQ on a store procedure with a Select Statment?
Hi there, How do I use LINQ on a store procedure with a Select Statment? I know how can I use store procedure to execute some procedure for me, however, if I want to return some data from a store procedure? How do I do that? Regards ...
How do you call a Stored Procedure in SSIS?
I am trying to create an SSIS package that queries data from a table, and calls a stored procedure in another database with each row. In my old DTS package, I was doing this: EXEC myStoredProcedure ?, ?, ? ...and then I mapped the parameters. However, in SSIS, I can't figure out how to make this work. I have a Data Flow task, which...
How to run multiple .sql files in Eclipse DTP
I've a list of .sql script files to create Stored Procedures which I'm using the Eclipse DTP to develop. Currently to create/update all these Stored Procedures, I've to open & run one by one from the Data Perspective. Is there a way to create a batch file that run the scripts along the lines of run createSP1.sql run createSP...
Preventing Sql column overflows
If I have a parametrized stored procedure which takes a varchar(10) value and converts it to an int, I currently have to ensure that the value is not greater than the varchar equivalent of the maximum int value. IF @Criteria <= '2147483647' SET @Id = CONVERT(int, @Criteria) My question: Is there a better way to prevent overflowing an...
ADO.NET: How to get Return Value of a Stored Procedure
Probably an easy-to-answer question. I have this procedure: CREATE PROCEDURE [dbo].[AccountExists] @UserName nvarchar(16) AS IF EXISTS (SELECT Id FROM Account WHERE UserName=@UserName) SELECT 1 ELSE SELECT 0 When I have ADO.NET code that calls this procedure and does this: return Convert.ToBoolean(sproc.ExecuteScalar()); Either...
Compare Images in SQL
Hi, What is the best way to compare to Images in the database? I tried to compare them (@Image is the type Image): Select * from Photos where [Photo] = @Image But receives the error "The data types image and image are incompatible in the equal to operator". ...
Performance Difference between LINQ and Stored Procedures
Related LINQ-to-SQL vs stored procedures? I have heard a lot of talk back and forth about the advantages of stored procedures being pre compiled. But what are the actual performance difference between LINQ and Stored procedures on Selects, Inserts, Updates, Deletes? Has anyone run any tests at all to see if there is any major diffe...
SQL Server 2008: How crash-safe is a CLR Stored Procedure that loads unmanaged libraries
We've got a regular (i.e. not extended) stored procedure in SQL Server 2000 that calls an external exe. That exe, in turn, loads a .dll that came from an SDK and calls some procedures from it (i.e. Init, DoStuff, Shutdown). The only reason we have this external exe thing is because we didn't want to create an extended stored procedure t...
mysql stored procedure syntax error
Ok, this is only the second stored procedure I've written. I think you'll get the idea, I'm trying to close a credit line, and all invoices, charges, notes, etc with it. But I get a syntax error. The goal is to CALL close_account_proc(398985994) DELIMITER $$ CREATE PROCEDURE `cc`.`close_account_proc`(cid INT) @uid_usr := uid_...
L2Entities, stored procedure and mapping
Finally checked out L2E framework and ran into problems almost instantly. Yeah, i know... i should read some books before. Situation: entity with props -> id and name. entity is mapped to table, which has id and name columns. sproc, which returns ONLY id column. Problem: ObjectResult<MyProp> result = _container.MyStoredProced...
PL/SQL: Procedure not working correctly in a package
I'm working on a package with some procedures in them and I'm running into a bit of trouble. When I try to test the procedure to convert gallons to liters or the other procedures, it just prints out what was declared in the unnamed block instead of converting the numbers. Any ideas? CREATE OR REPLACE PACKAGE eng_metric IS PROCEDURE ...
running sp_execute query epects parameter @statement
Hi, I'm not sure how to resolve this error: *Procedure or Function 'sp_executesql' expects parameter '@statement', which was not supplied.* for this query: DECLARE @a INT DECLARE @b VARCHAR SET @a = 1 WHILE @a < 30 BEGIN set @b = @a exec sp_executesql update source_temp set pmt_90_day = pmt_90_day + convert(money,'trans_total_'+...
SQL Stored Procedure Comparing values
Hi all, Suppose i have the following table book ----- id name genre check_out_date location_id Can anyone post an example of an efficient stored procedure that only updates the book table where the passed in values are different from the current values in the table? i.e. if i passed in values ( 1, "Slaughterhouse V", "Fiction", 10/1...
SQL Server error handling: exceptions and the database-client contract
We’re a team of SQL Servers database developers. Our clients are a mixed bag of C#/ASP.NET, C# and Java web services, Java/Unix services and some Excel. Our client developers only use stored procedures that we provide and we expect that (where sensible, of course) they treat them like web service methods. Some our client developers don...
Use current auto_increment value as basis for another field in the same INSERT query - is this possible in MySQL?
In my table (MySQL) I have these fields: id - primary, auto_increment hash - base 36 representation of id I'd like to populate both fields at once, using a stored procedure to compute the base 36 equivalent of the value id receives on INSERT. Something like this: INSERT into `urls` (`id`,`hash`) VALUES (NULL,base36(`id`)); Obviou...
Multiple update statements in one StoredProcedure
Hi I am wondering if it is possible to have multiple Update statements in a store procedure Something like this: Update Table1 set field1 = @new_value where id = @table1_id Update Table2 set field2 = @new_value where id = @table2_id Update Table3 set field3 = @new_value where id = @table3_id Right now I am executing them seperatel...
Execute a SProc from within a Sproc (using params passed in)
Hi all, Does anyone know of a way to execute a stored procedure from within a stored procudure using some of the params passed in from the original procedure. i.e. if i pass in params @id, @event date, @ log into sproc 1 then sproc 1 will do it's own stuff and pass in @log and @event_date to sproc 2. Thanks! ...
What is the difference between function and procedure in PL/SQL?
Duplicate: what is the difference between a function and a procedure What is the difference between function and procedure in PL/SQL ? ...