I have a stored procedure that returns values from a temp table. In my DBML, it displays (None) for the return type. What is the trick to get it to recognize the columns from my temp table? CREATE PROCEDURE [dbo].[GetCategoryPriceRanges] @CategoryId int AS BEGIN DECLARE @MinPrice money, @MaxPrice money SELECT @MinPrice = MIN(ourP...
Hi, Is it possible to create a stored procedure as CREATE PROCEDURE Dummy @ID INT NOT NULL AS BEGIN END Why is it not possible to do something like this? ...
I am running a sproc on an SQL Server 2005 server which is resulting in the following error: Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) Once the error occurs I loose m...
I'm trying to write a query for an advanced search page on my document archiving system. I'm attempting to search by multiple optional parameters. I have about 5 parameters that could be empty strings or search strings. I know I shouldn't have to check for each as a string or empty and create a separate stored procedure for each combi...
I have an SQL Server 2005 server, and I'd like to run a .Net CLR stored procedure on it. However, I'd like to use .NET Framework 3.5. If I try this right now, I get this error: Error: Assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' was not found in the SQL catalog. I'm told this is possible ...
Hello, I want to plan a data access layer and I thought to use Linq. I read that linq has its problems with performance and that you could use stored procedures with linq. Should I use stored procedures with linq when planning my data access layer? Is it crucial for the performance? When should I use them? I know that stored procedures...
Is there a simple process in SQL 2005 for spitting all of my stored procedures out to individual .sql files. I'd like to move them into VSS, but am not too excited by the prospect of clicking on each one to get the source, dumping it into a text file and so on.. ...
What is the preferred way to use stored procedures between the following two methods and why: One general SP such as 'GetOrders' which returns all the columns for the table Order. Several different parts of the application will use the same SP. OR Several more specific SPs such as 'GetOrdersForUse1' and 'GetOrdersForUse2' which retur...
How do you organize your stored procedures so you can easily find them and keep track of their dependencies? ...
I'm getting increasingly frustrated with the limitations and verbosity required to actually commit some business logic to stored procedures, using languages such as Transact-SQL or PL/SQL. I would love to convert some current databases to Oracle and take advantage of its support for Java stored procedures, but that option is not availab...
Is it a best practice to use SP (store Proc) for every single SQL call in .NET applications? Is it encouraged for performance reasons and to reduce surface area for SQL injection attacks (in web applications)? ...
So I was thinking about creating a dynamic sql question, meaning that i want the amount of parameters to be dynamic. Having looked at this: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause#337725 i was thinking that using like '%x%' is SLOW and not good. What i actually would like to do is using the IN keyword a...
I think the question says it all. I have several monthly processes in stored procedures which take anywhere from a minute to an hour. If I declare them WITH RECOMPILE, an execution plan will be generated each time. If the underlying indexes or statistics or views are changed by the DBA, I don't want anyone to have to go in and force a...
Hi, There is a column in a database that is of type INT (Sql server). This int value is used at a bit flag, so I will be AND'ing and OR'ing on it. I have to pass a parameter into my sproc, and that parameter will represent a specific flag item. I would normally use an enumeration and pass the int representation to the sproc, but sinc...
In my SQL Server backend for my app, I want to create history tables for a bunch of my key tables, which will track a history of changes to the rows. My entire application uses Stored Procedures, there is no embedded SQL. The only connection to the database to modify these tables will be through the application and the SP interface. Tra...
I am finding that calling a stored proc in Excel is not as easy as it should be, but calling a view, or a direct table is very easy. So, how can I create a view that will call a stored proc with no params? I know I won't be able to pass any values into the view, and I don't need or want to, Just want to wrap a stored proc in a view. so...
I would like to include CLR stored procedure deployment in our deployment script (powershell at the moment), however I can't figure out a nice way to do this automatically. I would have thought there would be a nice command line option to do the same as right-click deploy in Visual studio ...
I'm implementing some rudimentary SQL Server monitoring to watch for excessive blocking. I have a stored procedure that looks like this: CREATE PROCEDURE [dbo].[bsp_GetBlockedProcessCount] AS BEGIN SET NOCOUNT ON; DECLARE @count INT SELECT @count = COUNT(*) FROM master.dbo.sysprocesses WITH (NOLOCK) WHERE blocked ...
Is there any possibility to directly dive into SQL Server stored procedures from .net code? So when debugging step by step in .net, I'd like to step into a called stored procedure. I know how to debug them individually, but I'd like to join these debug sessions in order to gain some time. ...
Below is my stored procedure. I want use stored procedure select all row of date from tbl_member and insert 2 table. But it's not work. Some one can help me? Create PROCEDURE sp_test AS BEGIN SET NOCOUNT ON; Declare @A Varchar(255), @B Varchar(255), @C Varchar(255), @D int Declare Table_Cursor Cursor For select A, B, ...