stored-procedures

How do I remotely debug a stored procedure within the same domain?

What are the steps needed to successfully be able to remotely debug a store proc (SQL Server 2005) from within VS 2005? Again both client and server machines are on the same domain. I have never done this so step by step instructions would greatly be appreciated. ...

What is the best way to version control my SQL server stored procedures?

What is the best way to version control my database objects? I'm using Visual studio 2005/2008 and SQL server 2005. I would prefer a solution which can be used with SVN. ...

Find out the calling stored procedure in SQL Server

Is it possible to find out who called a store procedure. So if you get an error in proc3, I know if it was called by proc1 or proc2. I'm using SQL Server 2005. ...

Dynamic Sorting within SQL Stored Procedures

This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that really addresses what I see to be an incredibly common need in any Web or Windows application with a database back-end. I speak of dynamic sor...

Stored Procedure Ownership Chaining

I have several stored procedures in my database that are used to load data from a datamart that is housed in a separate database. These procedures are, generally, in the form: CREATE PROCEDURE load_stuff WITH EXECUTE AS OWNER AS INSERT INTO my_db.dbo.report_table ( column_a ) SELECT column_b FROM data_mart.dbo.source_table WHERE ...

Ideas to replace Stored Procedure in Cash Flow report

We have a Cash flow report which is basically in this structure: Date |Credit|Debit|balance| 09/29| 20 | 10 | 10 | 09/30| 0 | 10 | 0 | The main problem is the balance, and as we are using a DataSet for the Data, it's kinda hard to calculate the balance on the DataSet, because we always need the balance from the previous ...

Stored procedure bit parameter activating additional where clause to check for null

I have a stored procedure that looks like: CREATE PROCEDURE dbo.usp_TestFilter @AdditionalFilter BIT = 1 AS SELECT * FROM dbo.SomeTable T WHERE T.Column1 IS NOT NULL AND CASE WHEN @AdditionalFilter = 1 THEN T.Column2 IS NOT NULL Needless to say, this doesn't work. How can I activate the additional where clause th...

Transpose a set of rows as columns in SQL Server 2000

Is there any facility of transposing rows to columns in SQL Server (it is possible in MS-Access)? I was befuddled because this facility is available in MS-Access but not in SQL Server. Is it by design that this feature has not been included in SQL Server? ...

Is there a SQL Server profiler similar to Java/.Net profilers?

I love the way I can profile a Java/.Net app to find performance bottlenecks or memory problems. For example, it's very easy to find a performance bottleneck looking at the call tree with execution times and invocation counts per method. In SQL Server, I have stored procedures that call other stored procedures that depend on views, which...

Stored Procedure Default Value

I'm a newbie when it comes to SQL. When creating a stored procedure with parameters as such: @executed bit, @failure bit, @success bit, @testID int, @time float = 0, @name varchar(200) = '', @description varchar(200) = '', @executionDateTime nvarchar(max) = '', @message nvarchar(max) = '' This is the correct f...

Why do I need OleDbCommand.Prepare()?

I'm working with a datagrid and adapter that correspond with an MSAccess table through a stored query (named "UpdatePaid", 3 paramaters as shown below) like so: OleDbCommand odc = new OleDbCommand("UpdatePaid", connection); OleDbParameter param; odc.CommandType = CommandType.StoredProcedure; param = odc.Parameters.Add...

Is it possible to determine when a stored procedure was last modified in SQL Server 2000?

I know that you can do this in SQL Server 2005, but I'm at a loss for 2000. ...

Calling stored procedures

I have a c# application that interfaces with the database only through stored procedures. I have tried various techniques for calling stored procedures. At the root is the SqlCommand class, however I would like to achieve several things: make the interface between c# and sql smoother, so that procedure calls look more like c# function ...

Simple way to programmatically get all stored procedures

Is there a way to get stored procedures from a SQL 2005 Express database using C#? I would like to export all of this data in the same manner that you can script it our using SQL Management Studio, without having to install the GUI. I've seen some references to do thing via the PowerShell but in the end a C# console app is what I reall...

Benefits of using a Case statement over an If statement in a stored procedure?

Hi are there any pros / cons relating to the speed that a stored procedure executes when using an IF statement or choosing to use a CASE statement instead? ...

Executing a stored procedure within a stored procedure

Hi, I would like to execute a stored procedure within a stored procedure eg EXEC SP1 BEGIN EXEC SP2 END But I only want SP1 to finish after SP2 has finished running so I need to find a way for SP1 to wait for SP2 to finish before SP1 ends. Thanks ...

Stored procedure not being executed within another stored procedure

Hi, I have found that SP2 doesn't execute from within SP1 when SP1 is executed. Below is the structure of SP1: ALTER PROCEDURE SP1 AS BEGIN Declare c1 cursor.... open c1 fetch next from c1 ... while @@fetch_status = 0 Begin ... Fetch Next from c1 end close c1 deallocate c1 exec sp2 end ...

Reset @@FETCH_STATUS stored procedure

Hi, How can I reset the @@FETCH_STATUS variable or set it to 0 in a stored procedure? ...

Using stored procedures for calculations.

I am currently working on a project that will store specific financial information about our clients in a MS SQL database. Later, our users need to be able to query the database to return data from the clients based on certain criteria (eg. clients bigger then a certain size, clients in a certain geographical location) and total it to us...

Is there any alternative to stored procedures?

Is there any alternative to stored procedures, secure and fast as well as stored procs. i know only Hibernate. Is there any other technologies like that? ...