Tag: stored-procedures

Case Statement with different operator in Where Condition

I am trying to figure out how to have following condition in a SQL Server Store procedure. I have a parameter of @Variance which can have either ‘Y’ or ‘N’ or ‘A’ The where condition has to be like: IF @Variance = ‘Y’ THEN flag_d ≠ ‘Y’ OR flag_c ≠ ‘Y’ IF @Variance = ‘N’ […]

Check date interval with SQL Server

Hi in a stored procedure I want to select rows that are in an interval of a month where @Anno is the year of the start and @Mese is the month of the date start. I try this: WHERE FinePeriodo >= CAST((@Anno + ‘-‘ + @Mese + ‘-01 00:00:00’) as datetime) AND FinePeriodo < DATEADD(month, […]

mssql_bind empty string converting to NULL

I am currently using SQL Server 2000 Stored Procedures with PHP. Following the PHP doc, I use mssql_bind to assign value of parameters and then execute the Stored Procedure. The problem is that I got this bug which prevents me to bind empty strings to parameters (they are converted to NULL when the Stored Proc […]

Null value being returned from a Stored Procedure using Linq to SQL

If I run a stored procedure in my database (SQL 2000) and return the SCOPE_IDENTITY, I receive a value. However, using LINQ to SQL, I get a null value for the Column1 property. Anyone have a suggestion?

Best way to implement a stored procedure with full text search

I would like to run a search with MSSQL Full text engine where given the following user input: “Hollywood square” I want the results to have both Hollywood and square[s] in them. I can create a method on the web server (C#, ASP.NET) to dynamically produce a sql statement like this: SELECT TITLE FROM MOVIES […]

SP TimeOut in .NET code, not in Management Studio

If I run the following in Management Studio (SQL Server 2008) : exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] ‘4AFD6633-CB90-4165-913D-EE3EA74708DA’, ‘7EF7CCB2-E09F-4408-AE2D-F857C063F2C1’ I get the result back in less than a second I however I run it in VB.Net like this : Using aConnection = New System.Data.SqlClient.SqlConnection(*** Some Connection String ***) aConnection.Open() Dim aCmd = aConnection.CreateCommand() aCmd.CommandText = “exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] ‘4AFD6633-CB90-4165-913D-EE3EA74708DA’, […]

Fastest, easiest way to create a table from a stored procedure

I use system stored procedures all the time, but always need/want to filter or sort the results of the sproc. I typically use a table variable for this, but defining the fields/datatypes for the table variable is a pain. For instance… DECLARE @SpWhoResults TABLE ( spid INT, STATUS VARCHAR(100), loginname VARCHAR(2000), hostname VARCHAR(2000), blkby VARCHAR(100), […]

Need SQL Server Stored Procedure for This Query

I have a ASPX.NET DataGrid and im trying to USE a select LIKE ‘X’% from a table that has 1 field called location. im trying to display the locations that start with a certain letter (example wxxx,axxx,fxxx,) in different columns in my data grid. I am trying to display more than 1 column in my […]

SSRS report to display Outlook type calendar – People x Days x Activities

I am after a pattern/view/opinion/tip/weblink from an SSRS/SQL expert on how I might create a report that enables me to list something like the following: [—-Person—-]  | [29-Sep-11] | [30-Sep-11] | [01-Oct-11] | [02-Oct-11] | [03-Oct-11]… and so on… Bob Bobertson |   Activity A   |   Activity B    | —-Empty— |   Activity C   | —Empty— […]

Tool for converting stored procedures between Oracle<-> SQL Server

What tools are you using to convert stored procedures/functions/triggers etc. between Oracle and SQL Server Maybe somebody got a lint to “best practices” doc ? 🙂

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