Tag: stored-procedures

Pivot table in stored procedure using view manage null value

This stored procedure result store in total on the basis of query , if there in no any record in total then want 0 in result,how can manage null value . CREATE PROCEDURE dbo.Procedure_Summary @UserID INT , @Year INT AS BEGIN /* Procedure body */ SELECT UserID, UserName , [Marks1] as ‘English’, [Marks2] as ‘Maths’, […]

SQL: Procedure not getting a date null

I’m executing a procedure like this: EXEC myProcedure @name = ‘Smith’, @dateDeleted = NULL I need all the Smith that don’t have a deleted date. And this is the procedure: CREATE PROCEDURE [dbo].[myProcedure] @name VARCHAR(8000) = NULL ,@dateDeleted VARCHAR(8000) = NULL AS BEGIN SET NOCOUNT ON; WITH t (name ,dateDeleted ) AS ( SELECT DISTINCT […]

SQL Server performance fast only when refresh the stored procedure

I can run a stored procedure multiple times and it wont hit it’s cache: (1665ms is duration column) But if I then alter the stored procedure changing nothing: (240ms is duration column) Problem: how to get the stored procedure to always be fast (on the second and next calls)

How to map result from stored procedure to object

I’m using EF for fetching data from stored procedure in MS SQL. Stored procedure is returning table. (columns have same names as properties in my object) List<MyObject> result = db.ExecuteStoreQuery<MyObject>(“EXEC [dbo].myProcedure]”).ToList(); Is there a way how to do this without Entity Framework? (with SqlCommand) Thanks.

SQL query works, but SP as a whole does not

When I execute the FIRST and SECOND QUERYs through SQL Server Management Studio 2005 they work correctly and modify the correct data. However, when I try to execute the entire SP, I get no errors, but only FIRST actually modifies the data it is supposed to and SECOND does NOT. I studied THIS Stackoverflow article […]

Excel SSIS query returns null columns in Excel but not in Management studio

I did my best to look around the web but this problem eludes me. I have a stored procedure in SSIS that works fine. It does a bunch of stuff eventually returning some numbers and text. The procedure itself uses #temp tables since the data does not need to exist beyond the proc run and […]

How to save the output of a stored procedure to a log table?

I have a stored procedure that every night will be executed by the windows task schedular. I just need to know the following 3 things about the execution of the stored procedure: 1. is the stored procedure executed by the task schedular? 2. Is the stored procedure executed but with errors? 3. Is the stored […]

Error Handling in Current and Older Versions of SQL Server Stored Procedures

What is the prefered way to handle errors in 2005+ versions of SQL Server? And what about versions prior to 2005? I’m working on some older T-SQL code and I’m finding this expression everywhere: SELECT CustomerID FROM Customers WHERE CustomerNumber = @customer_number SET @ERR = @@Error IF @ERR <> 0 RETURN @ERR Assume CustomerID is […]

Duplicate error in transactions

I have a transaction that updates the table produces. It sets a product to be manufactured by a certain machine. However if this machine already exists in the table I get Violation of PRIMARY KEY constraint ‘produces_PK’. Cannot insert duplicate key in object ‘dbo.TblProduces’. How do I solve this problem? This is the transaction: create […]

One stored procedure to update whole table or individual columns too

I have a table userdata which has these columns: userId userName userEmail userContact userDob userAdd userImage userPass I am using a stored procedure to select data from this table. Now I want to update this table with a stored procedure. I am able to update this table with stored procedure but my requirement is in […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.