Tag: stored-procedures

Stored procedure unexpected result not desired

ALTER PROCEDURE [dbo].[USP_ViewRegForm] — Add the parameters for the stored procedure here ( @RegFormID Numeric=null, @FDate Date=null, @TDate Date=null, @viewPending int=null ) AS BEGIN — SET NOCOUNT ON added to prevent extra result sets from — interfering with SELECT statements. SET NOCOUNT ON; SET DATEFORMAT DMY; — View statements for procedure here — If (@viewPending […]

Parameter not being passed with SqlCommand

I have a stored procedure that has been working just fine, but I recently added three new params to it: **@HasObits**, **@AlternateName**, and **@Notes**. CREATE PROCEDURE dbo.sp_InsertNewRecord ( @CountryID int, @StateID int, @CountyID int, @RecordsOnline bit = 0, **@HasObits bit = 0,** @Name varchar(100), **@AlternateName varchar(100) = null,** @Address varchar(50) = null, @Address2 varchar(50) = […]

Creating a CSV file in a SQL Server stored procedure

Is it possible to create a CSV file from a stored procedure? I need to create a CSV file with data from a table, that is retrieved in a stored procedure

MSSQL – Create records for entries that don't exist in the last 7 days

I have an ASP.NET website with a C# back-end using MSSQL SQL Server 2008 for its content. I have written the following stored procedure which checks for any records within the last 7 days and then returns what it finds. ALTER PROCEDURE [dbuser].[GetResponses] ( @QUEST_ID int ) AS SELECT DateAdded, SUM(Responses) AS responseCount FROM ActiveResponses […]

Convert select column output into semi-colon seperated list for sp_send_dbmail

I have a complex select statement (the union of several queries) which outputs a single column e.g. | email | | test@domain.com | | test2@domain.com | | test3@domain.com | I would like to feed this into the @blind_copy_recipients of sp_send_dbmail which requires a semi colon (;) separated list. would normally use COALESCE and a variable […]

Using stored procedure in Reporting Services

I have stored procedure which runs perfectly. And when I insert it into query designer in MS Reporting Services, Ii have error: Incorrect syntax near ‘GO’. Incorrect syntax near ‘GO’. Incorrect syntax near ‘GO’. The variable name ‘@s1a’ has already been declared. Variable names must be unique within a query batch or stored procedure. As […]

Drastic difference of the time cost for the same store procedure

I am using SQL Server 2008 R2. The process is actually like this: First, about 2 million records are pulled from a remote server, then a join is done locally, the final result is thousands of records. The time cost varies from less one 1 min to 30 mins. And after I experienced the 30 […]

Stored procedure parameter was not supplied error

I have a problem with a SQL Server stored procedure. Although I supply parameter, and although the parameter is not null, I got ‘parameter is not supplied’ error My C# code is here: staffInfo.DeleteCommand = “deleteStaff”; staffInfo.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure; staffInfo.DeleteParameters.Add(“tcId”, stafftc); staffInfo.Delete(); Stored procedure: ALTER PROCEDURE dbo.deleteStaff ( @tcId varchar(11) ) AS BEGIN DECLARE @memId […]

Stored Procedure fails to see parameters or returns a convert data type error

I’ve run into a rather annoying problem which I cannot seem to get to the root of. I’ve searched the internet for similar problems – and I’ve found a few unanswered in other forums – so I thought I’d give it a go here. The following WebMethod of a webservice contacts a database, which works […]

Stored Procedure Redirect logic

We are developing an application where sometime we need to support more than one versions of that software at a time. For example Release 1-0-300 , 1-0-300-With-Test-FunctionalityA, Release 1-0-300-With-Test-FunctionalityB. Due to such requirements we sometime use the same Stored Procedure with different name in different versions for example “1-0-300” uses Stored Procedures “USP_GetProucts“ “1-0-300-With-Test-FunctionalityA” uses […]

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