Tag: stored-procedures

Books on optimizing and refactoring SQL stored procedures

Can you please recommend resources to write efficient store procedures, refactor and optimize them? Thank you.

how to pass multiple unique identifiers to stored procedure

Possible Duplicate: Parameterizing an SQL IN clause? Hi how can we send multiple uniqueidentifiers to a stored procedure. Ex: Create procedure showall (@empids uniqueidentifier(50) ) as select * from emp where empid in (@empid) like this?

Multiple COUNT(*) with join

I have to COUNT some rows from multiple tables. Before I can do multiple COUNT I will have to subselect. The problem here is that I need to JOIN some values in order to get the right result. SELECT sponsor.Name As SponsorName, COUNT(participants.[Table]) AS ParticipantCount, ( SELECT COUNT(guestcards.[Table]) FROM guestcards WHERE guestcards.EventID = @EventID AND […]

Moving stored procedure from Firebird to SQL Server

There are databases on Firebird and SQL Server. I have working stored procedure in Firebird. Question is: how to translate it to T-SQL syntax? begin IF ((:PHONE_N is NULL) or (STRLEN(:PHONE_N)<1)) THEN BEGIN O_PHONE_N = NULL; EXIT; END IF (STRLEN(PHONE_N) > 3) THEN BEGIN O_PHONE_N = ‘(‘ || SUBSTR(:PHONE_N, 1, 3) || ‘) ‘; PHONE_N […]

How to use stored procedures output in IN clause in SQL Server

There is a view in my application which is having an INNER JOIN with another view and a table. view ViewName select * from AnotherView inner join TableName ON conditions This view is taking too much of time to execute specially with the INNER JOIN statement. So I am trying to improve the performance using […]

Having issues updating a column in my db using a SPROC

This stored procedure doesn’t update the column FailedLoginAttempts if the conditions are true. Can anyone tell me what might be wrong with this? ALTER PROCEDURE UpdateFailedLoginAttempts ( @username varchar(100), @failureType varchar(100), @maxInvalidPasswordAttempts int ) AS BEGIN DECLARE @failureCount int IF (@failureType=’Password’) BEGIN SELECT @failureCount=FailedLoginAttempts FROM Users WHERE Username=@username UPDATE Users SET FailedLoginAttempts=@failureCount+1 WHERE Username=@username IF(@failureCount […]

How to Alias a query in SP and use the result throughout the Procedure

m writing a stored procedure in which i have 4 select queries i want some data from first select query to use in where clause of rest of three queries. SELECT TOP 100 * FROM dbo.[Order] WHERE (dbo.[Order].[CreatedOn]<(GetDate()-7)) AND ((@OrderId IS NULL)OR(dbo.[Order].[OrderId] BETWEEN (@OrderId) AND (@orderId+100))) ORDER BY dbo.[Order].[OrderId] SELECT * FROM dbo.[OrderItem] WHERE dbo.[OrderItem].[OrderId] […]

How to join multiple selects in a stored procedure with MSSQL?

I have a database with dynamically generated tables. I’m writting a stored procedure to execute queries over the generated tables and I need to see the results in the query analyzer tool (or alike). What I currently have is: DECLARE @TableName sysname DECLARE TableNameCursor CURSOR FOR SELECT TableName FROM [xxxTables] WHERE xxx… OPEN TableNameCursor FETCH […]

Stored procedure to call a different stored procedure based on parameter

I would like to create a stored procedure in Sql Server that calls one of a number of different stored procedures and chooses which one based on the parameter supplied to it, but I can’t seem to get the choosing syntax right. Currently attempting: Begin Set nocount on; exec iif(param=’individual’, sp_FindIndividualRespondents, iif(param=’org’, sp_FindOrgRespondents, sp_FindGroupRespondents)) End […]

Trouble with stored procedure for deleting least recent entries in a table

The query I have written is: delete from featured where featuredID IN( select top(@removeAmnt) * from featured order by featured.createdon asc) which doesn’t appear to work. The error I get is, “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.”. I’m not to familiar with sql […]

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