stored-procedures

How do I select from a stored procedure in Sybase?

My DBA has constructed me a stored procedure in a Sybase database, for which I don't have the definition. If I run it, it returns a resultset with a set of columns and values. I would like to SELECT further to reduce the rows in the result set. Is this possible? From this question it seems like I could insert the results into a temporar...

How to return temporary table from stored procedure

CREATE PROCEDURE [test].[proc] @ConfiguredContentId int, @NumberOfGames int AS BEGIN SET NOCOUNT ON RETURN @WunNumbers TABLE (WinNumb int) INSERT INTO @WunNumbers (WinNumb) SELECT TOP (@NumberOfGames) WinningNumber FROM [Game].[Game] g JOIN [Game].[RouletteResult] AS rr ON g.[Id] = rr.[gameId] WHERE g.[ConfiguredContentId] =...

Performance issue with SQL Server stored procedure

I used the ANTS profiler to identify the remaining bottleneck in my C# application: the SQL Server stored procedure. I am using SQL Server 2008. Can anybody here help me increase performance, or give me pointers as to what I can do to make it better or more performant? First, here's the procedure: PROCEDURE [dbo].[readerSimilarity] --...

Simple ADO.NET C# Stored Procedure Generator

I am using Visual Studio 2005, Sql Server 2005, C#, ADO.NET. We have a very large database and routinely adding new stored procedures. I am tired of writing the C# wrapper code for these stored procedures, seems like there should be some simple utility or Add In that would allow me to simply point to a stored procedure and generate s...

Do triggers decreases the performance? Inserted and deleted tables?

Suppose i am having stored procedures which performs Insert/update/delete operations on table. Depending upon some criteria i want to perform some operations. Should i create trigger or do the operation in stored procedure itself. Does using the triggers decreases the performance? Does these two tables viz Inserted and deleted exist...

C# LINQ-to-SQL Multiple selects

Hello! I got this, its calling a SP in my MS SQL 2008 database: [Function(Name = "dbo.Content_GetContent")] [ResultType(typeof(Content_GetContentResult))] [ResultType(typeof(Content_GetContentImagesResult))] [ResultType(typeof(Content_GetContentBoxesResult))] [ResultType(typeof(Content_GetContentSearchWordsResult))] ...

SPROC to update record: how to handle unchanged values

I'm calling a update SPROC from my DAL, passing in all(!) fields of the table as parameters. For the biggest table this is a total of 78. I pass all these parameters, even if maybe just one value changed. This seems rather inefficent to me and I wondered, how to do it better. I could define all parameters as optional, and only pass t...

linq2sql: using ExceuteQuery<dto> when rows returned are not in my dto? Can i use a generic data type?

hi there, been using ExecuteQuery with some success, i.e. where AccessRights is my dto and queryString contains "Exec sp_name param1,param2 etc" var accessRights = this.db.ExecuteQuery<AccessRights>(queryString, sqlParams.Values.ToArray()).AsQueryable(); Everything works perfect if what returns from the stored procedure can be...

Storedprocedure returns xml but linq replaces ' " ' with '\'

Hi, I have a stored procedure in sql server that returns xml. The problem I'm facing is that the returned result which is of type ISingleResult contains the string for the xml returned by the stored procedure and in this string all the ' " ' are replaced by '\'. So I can't parse the xml. Why is this happening? ...

Crystal Reports 8: Change the stored procedure used by a subreport ?

Hi I'm using Crystal reports 8 and I need to change the stored procedure associated with a sub report, is it even possible? or I'm going have to redo it? ...

How to select an empty result set?

I'm using a stored procedure in MySQL, with a CASE statement. In the ELSE clause of the CASE ( equivalent to default: ) I want to select and return an empty result set, thus avoiding to throw an SQL error by not handling the ELSE case, and instead return an empty result set as if a regular query would have returned no rows. So far I've...

Retrieving ADO errors using Delphi

I'm using Delphi 2007 with ADO to access a MS SQL 2008 database. A stored procedure on the database prevalidates the input and if the validation fails it returns an error result set (containing custom error info). Uisng the SQL Server Management Studio, when I run the stored proc I get the custom error result set in one tab and the nat...

Should stored procedures be many or focused?

How generic should you make your stored procedures? Should they be considered "one hit" or mirror more general use? Consider the following cases: 1. FindUser(username, ...) Stored procedure returns the user id to the caller FindItem(itemname, ...) Store procedure reurns the item id to the caller AddOrder(userid, itemid, ...) The ...

Where cab I find a host with stored procedures

Hi I have created a project that uses mysql stored procedures and views. I have a lot of difficulties to find a hosting service that would support them. Do you know where I can go? thank you. ...

how to create static cursor in db2?

I am facing a problem while creating a static cursor in DB2. This is the statement i used in my SP. DECLARE CURNAME SENSITIVE STATIC SCROLL CURSOR FOR SELECT COL1, COL2 FROM SCH.TABLENA ORDER BY COL1; on compilation it says : DB2 Database Error: ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "SEN...

Retrieving Two Values from a Stored Procedure

Can someone tell me how I can combine the two SQL statements that count the number of messages and number of unread messages? Its ineffecient two have two statements but I don't know what to search for to get the answer im looking for. Thanks in advance. CREATE PROCEDURE dbo.GetMessages ( @username nchar(12), @isCount bit, @messag...

How to copy & insert records in table by strored procedure?

I have a table with one field: lngStatusID with value 2 for all the records. Now I need to insert all the records again in the same table but with lngStatusID=1 So for that I think stored procedure will help me somehow. AS per my logic it should be something like: 1) I need to read each record with loop 2) copy all fields in tempora...

ASP.NET – Error Handling in Database Stored Procedures

. Hi, Good morning. Would you please let me know whether we need to write exception handling mechanism (Try, Catch blocks) in database stored procedures? Is it the best Practice? (As the corresponding error will be thrown to the calling ASP.NET application itself whenever an error occurs in the database stored procedure.) Thanks and...

Problem with Sql Server query using BETWEEN in where with DATETIME

I have a Stored Procedure that loops through the months in the fiscal year and does a count for the items in each month. I know for a fact there are 176 items, but when I run this it returns a total count of 182. I tried removing one second from @EndDate, but then my total count was 165. So I'm either counting items twice, or not coun...

How do you measure the performance of a stored procedure?

I'm using a version of SQL Server 2005 that does not support the profiler, trying to figure out how best to compare the performance of two stored procedures. I've run the execution plan for each, but it's not clear to me which of the provided metrics I should be focusing on. Do I go through and add up the various costs? What's the bes...