Tag: stored-procedures

SQL Encode string parameter so special characters work

I would like to be able to add a new SQL LOGIN and name it after a person email address. For example “billg@example.com”. When I pass this to the following stored procedure I get an error (error follows procedure). The stored proc: CREATE PROCEDURE [Forms].[AddLogin] @Email nvarchar(2048), @TenantPassword nvarchar(2048) AS BEGIN — SET NOCOUNT ON […]

are stored procedures faster than functions or not

I am using stored procedures at the moment but I have a lot of stuff like this: if … begin select ‘success’ return end if … begin select ‘notgood’ return end if … begin select ‘someelse’ return end so I’m thinking that it would be a better idea to use functions instead, but is there […]

Why might SQL execute more quickly on SQL Server 2000 when NOT using a stored procedure?

I could see nothing wrong with the execution plan. Besides, as I understand it, SQL Server 2000 extended many of the performance benefits of stored procedures to all SQL statements by recognising new T-SQL statements against T-SQL statements of existing execution plans (by retaining execution plans for all SQL statements in the procedure cache, not […]

How can data vary pending how I view them

I have an odd case where when I look at the data through my SQL scripts I have one value, but if I look at the data directly SELECT * FROM table I get another value. My first thought was parameter sniffing, but that didn’t fix the issue. I’m not doing anything with the value […]

How to Model/Document Existing Stored Procedure Flow

I started working with a GPS Tracking System, whose backend logic is almost entirely based on the execution of Stored Procedures. They were implemented on a SQL 2000 database. I received the task to document/model the existing stored procedure flow, but I don’t have experience with such a task. I was used to UML, but […]

Running managed code in SQL Server 2008 – any issues?

I would like to know the performance issues associated with running managed code through SQL Server 2008. I’ve heard about some memory and speed issues. Specifically, I want to implement a SHA256 hashing DLL and execute it as a sproc with SQL Server 2008. Alternately, I could simply execute the hashing from my .Net app, […]

Microsoft SQL Server 2005 Inserting into tables from child procedure which returned multiple tables

I’ve got a child procedure which returns more than table. child: PROCEDURE KevinGetTwoTables AS BEGIN SELECT ‘ABC’ Alpha, ‘123’ Numeric SELECT ‘BBB’ Alpha, ‘123’ Numeric1, ‘555’ Numeric2 END example: PROCEDURE KevinTesting AS BEGIN DECLARE @Table1 TABLE ( Alpha varchar(50), Numeric1 int ) DECLARE @Table2 TABLE ( Alpha varchar(50), Numeric1 int, Numeric2 int ) –INSERT INTO […]

Stored Procedure: Variable passed from PHP alters second half of query string

Basically, we have an ASP website right now that I’m converting to PHP. We’re still using the MSSQL server for the DB — it’s not moving. In the ASP, now, there’s an include file with a giant sql query that’s being executed. This include sits on a lot of pages and this is a simple […]

SQL Server: help with a simple stored procedure?

I’m trying to create a simple stored procedure which I can use to write data to a log table. I’m a noob in stored procedures, so what I’m missing here? I get an error saying: Msg 201, Level 16, State 4, Procedure toLog, Line 0 Procedure or function ‘toLog’ expects parameter ‘@messageTime’, which was not […]

Is it good practice to use one single stored procedure that accepts a variable number of parameters

I am working on a web project where I have to retrieve (let’s say) employee records. In some cases I have to retrieve a single record by providing an EmployeeID. In other cases, I have to retrieve multiple employee records by providing a SectorID. This logic could be expanded to cover additional scenarios: get all […]

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