Can you please recommend resources to write efficient store procedures, refactor and optimize them? Thank you.
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?
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 […]
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 […]
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 […]
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 […]
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] […]
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 […]
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 […]
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 […]