Search Results

Search found 2818 results on 113 pages for 'corporate procedures'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 24/113 | < Previous Page | 20 21 22 23 24 25 26 27 28 29 30 31  | Next Page >

  • Insert Stored Procedure, Using Asp.Net WebForm to Insert new [Customer]

    - by user2953815
    Can someone please help me create a stored procedure to insert a new customer from a web form. I am having difficulty making the state a drop down list for customer to pick a state from the list and having that inserted into the database. INSERT INTO Customer ( Cust_First, Cust_Middle, Cust_Last, Cust_Phone, Cust_Alt_Phone, Cust_Email, Add_Line1, Add_Line2, Add_Bill_Line1, Add_Bill_Line2, City, State_Prov_Name, Postal_Zip_Code, Country_ID ) VALUES ( @Cust_First, @Cust_Middle, @Cust_Last, @Cust_Phone, @Cust_Alt_Phone, @Cust_Email, @Add_Line1, @Add_Line2, @Add_Bill_Line1, @Add_Bill_Line2, @City, @State_Prov_Name, @Postal_Zip_Code, @Country_ID )"> <InsertParameters> <asp:Parameter Name="Cust_First" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Middle" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Last" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Phone" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Alt_Phone" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_Email" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Line1" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Line2" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Bill_Line1" Type="String"></asp:Parameter> <asp:Parameter Name="Add_Bill_Line2" Type="String"></asp:Parameter> <asp:Parameter Name="City" Type="String"></asp:Parameter> <asp:Parameter Name="Postal_Zip_Code" Type="String"></asp:Parameter> <asp:Parameter Name="Cust_ID" Type="Int32"></asp:Parameter> <asp:Parameter Name="State_Prov_Name" Type="String"></asp:Parameter> <asp:Parameter Name="Country_Name" Type="String"></asp:Parameter> </InsertParameters>

    Read the article

  • error invoking store procedure with input parameter from ADO.Net

    - by George2
    Hello everyone, I am using VSTS 2008 + C# + .Net 3.5 + ADO.Net. Here is my code and related error message. The error message says, @Param1 is not supplied, but actually it is supplied in my code. Any ideas what is wrong? System.Data.SqlClient.SqlException: Procedure or function 'Pr_Foo' expects parameter '@Param1', which was not supplied. class Program { private static SqlCommand _command; private static SqlConnection connection; private static readonly string _storedProcedureName = "Pr_Foo"; private static readonly string connectionString = "server=.;integrated Security=sspi;initial catalog=FooDB"; public static void Prepare() { connection = new SqlConnection(connectionString); connection.Open(); _command = connection.CreateCommand(); _command.CommandText = _storedProcedureName; _command.CommandType = CommandType.StoredProcedure; } public static void Dispose() { connection.Close(); } public static void Run() { try { SqlParameter Param1 = _command.Parameters.Add("@Param1", SqlDbType.Int, 300101); Param1.Direction = ParameterDirection.Input; SqlParameter Param2 = _command.Parameters.Add("@Param2", SqlDbType.Int, 100); portal_SiteInfoID.Direction = ParameterDirection.Input; SqlParameter Param3 = _command.Parameters.Add("@Param3", SqlDbType.Int, 200); portal_RoleInfoID.Direction = ParameterDirection.Input; _command.ExecuteScalar(); } catch (Exception e) { Console.WriteLine(e); } } static void Main(string[] args) { try { Prepare(); Thread t1 = new Thread(Program.Run); t1.Start(); t1.Join(); Dispose(); } catch (Exception ex) { Console.WriteLine(ex.Message + "\t" + ex.StackTrace); } } } thanks in avdance, George

    Read the article

  • Export products and variants from SQL Server

    - by mickyjtwin
    I have a SQL Server DB that has a table of products, and another table which contains a list of the sku variants of each product if it has one. I want to export all the products and their SKU's into excel. At the moment, I have a helper SQL function which performs the subquery against a product_id and concatenates all the SKU's into a comma-delimited string, e.g: Product Code, Name, SKUs 111 P1 77, 22, 11 Is there an easier way to do this, so that each SKU is a row which the associated product code as well, i.e: Product Code, Name, SKUs 111 P1 77 111 P1 22 111 P1 11

    Read the article

  • SQL Query to get Count within a Stored Proc

    - by sia
    So i need to figure out how i can get a record count value and use that count as a new value to insert into a table. Ex: In My Stored Procedure @Count int What im looking for @Count to equal "select top (1) _fieldName from _someTable order by _fieldName Desc" Finally insert into _newTable (_fieldName) values (@Count) End I dont have to use a variable, just trying to demonstrate what im really trying to do. My SQL knowledge is pretty limited so no laughing, or smirking! ;)

    Read the article

  • MySQL db Audit Trail Trigger

    - by Natkeeran
    I need to track changes (audit trail) in certain tables in a MySql Db. I am trying to implement the solution suggested here. I have an AuditLog Table with the following columns: AuditLogID, TableName, RowPK, FieldName, OldValue, NewValue, TimeStamp. The mysql stored procedure is the following (this executes fine, and creates the procedure): The call to the procedure such as: CALL addLogTrigger('ProductTypes', 'ProductTypeID'); executes, but does not create any triggers (see the image). SHOW TRIGGERS returns empty set. Please let me know what could be the issue, or an alternate way to implement this. DROP PROCEDURE IF EXISTS addLogTrigger; DELIMITER $ CREATE PROCEDURE addLogTrigger(IN tableName VARCHAR(255), IN pkField VARCHAR(255)) BEGIN SELECT CONCAT( 'DELIMITER $\n', 'CREATE TRIGGER ', tableName, '_AU AFTER UPDATE ON ', tableName, ' FOR EACH ROW BEGIN ', GROUP_CONCAT( CONCAT( 'IF NOT( OLD.', column_name, ' <=> NEW.', column_name, ') THEN INSERT INTO AuditLog (', 'TableName, ', 'RowPK, ', 'FieldName, ', 'OldValue, ', 'NewValue' ') VALUES ( ''', table_name, ''', NEW.', pkField, ', ''', column_name, ''', OLD.', column_name, ', NEW.', column_name, '); END IF;' ) SEPARATOR ' ' ), ' END;$' ) FROM information_schema.columns WHERE table_schema = database() AND table_name = tableName; END$ DELIMITER ;

    Read the article

  • query in sql server for retrieving rows

    - by Arash khangaldi
    I have a table that contains the following 4 columns: id name lastname phone I want to write a stored procedure that gets an id as parameter, and then gets the name of that id and then use that name to get all the rows that their name is equal to the names that i found in last step! here it is my query, i know it's wrong but i'm new to sql commands: ALTER PROCEDURE dbo.GetAllNames @id int AS select name as Name from Users where id = @id -- i don't how to retrieve the names that are equal to Name select * from Users where name = Name can you correct my query and help me? Thanks.

    Read the article

  • how to use a parameterized function for the Default Binding of a Sql Server column

    - by Walt Gaber
    I have a table that catalogs selected files from multiple sources. I want to record whether a file is a duplicate of a previously cataloged file at the time the new file is cataloged. I have a column in my table (“primary_duplicate”) to record each entry as ‘P’ (primary) or ‘D’ (duplicate). I would like to provide a Default Binding for this column that would check for other occurrences of this file (i.e. name, length, timestamp) at the time the new file is being recorded. I have created a function that performs this check (see “GetPrimaryDuplicate” below). But I don’t know how to bind this function which requires three parameters to the table’s “primary_duplicate” column as its Default Binding. I would like to avoid using a trigger. I currently have a stored procedure used to insert new records that performs this check. But I would like to ensure that the flag is set correctly if an insert is performed outside of this stored procedure. How can I call this function with values from the row that is being inserted? USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FileCatalog]( [id] [uniqueidentifier] NOT NULL, [catalog_timestamp] [datetime] NOT NULL, [primary_duplicate] nchar NOT NULL, [name] nvarchar NULL, [length] [bigint] NULL, [timestamp] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_id] DEFAULT (newid()) FOR [id] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_catalog_timestamp] DEFAULT (getdate()) FOR [catalog_timestamp] GO ALTER TABLE [dbo].[FileCatalog] ADD CONSTRAINT [DF_FileCatalog_primary_duplicate] DEFAULT (N'GetPrimaryDuplicate(name, length, timestamp)') FOR [primary_duplicate] GO USE [MyDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[GetPrimaryDuplicate] ( @name nvarchar(255), @length bigint, @timestamp datetime ) RETURNS nchar(1) AS BEGIN DECLARE @c int SELECT @c = COUNT(*) FROM FileCatalog WHERE name=@name and length=@length and timestamp=@timestamp and primary_duplicate = 'P' IF @c > 0 RETURN 'D' -- Duplicate RETURN 'P' -- Primary END GO

    Read the article

  • MS SQL Cursor data as result of stored procedure

    - by Dmitry Borovsky
    Hello, I have some stored procedure DECLARE cursor FOR SELECT [FooData] From [FooTable]; OPEN cursor ; FETCH NEXT FROM cursor INTO @CurrFooData; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CurrFooData AS FooData; INSERT INTO Bar (BarData) VALUES(@CurrFooData); FETCH NEXT FROM cursor INTO @CurrFooData; END; CLOSE OldestFeeds DEALLOCATE OldestFeeds But in result I have a lot of tables, not one. How can I return one table with 'FooData' column and all '@CurrFooData' rows?

    Read the article

  • SQL Server store procedure line number issue

    - by George2
    Hello everyone, I am using SQL Server 2008 Enterprise. I met with issue which says line 9 of store procedure foo is meeting with dead lock issue. My question is how to find exactly the 9th line of the store procedure? My confusion is because of coding format issue, how to locate 9th line correctly. thanks in advance, George

    Read the article

  • How to salvage SQL server 2008 query from KILLED/ROLLBACK state without waiting half a day?

    - by littlegreen
    I have a stored procedure that inserts batches of millions of rows, emerging from a certain query, into an SQL database. It has one parameter selecting the batch; when this parameter is omitted, it will gather a list of batches and recursively call itself, in order to iterate over batches. In (pseudo-)code, it looks something like this: CREATE PROCEDURE spProcedure AS BEGIN IF @code = 0 BEGIN ... WHILE @@Fetch_Status=0 BEGIN EXEC spProcedure @code FETCH NEXT ... INTO @code END END ELSE BEGIN -- Disable indexes ... INSERT INTO table SELECT (...) -- Enable indexes ... Now it can happen that this procedure is slow, for whatever reason: it can't get a lock, one of the indexes it uses is misdefined or disabled. In that case, I want to be able kill the procedure, truncate and recreate the resulting table, and try again. However, when I try and kill the procedure, the process frequently oozes into a KILLED/ROLLBACK state from which there seems to be no return. From Google I have learned to do an sp_lock, find the spid, and then kill it with KILL <spid>. But when I try to kill it, it tells me SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 554 seconds. I did find a forum message hinting that another spid should be killed before the other one can start a rollback. But that didn't work for me either, plus I do not understand, why that would be the case... could it be because I am recursively calling my own stored procedure? (But it should be having the same spid, right?) In any case, my process is just sitting there, being dead, not responding to kills, and locking the table. This is very frustrating, as I want to go on developing my queries, not waiting hours on my server sitting dead while pretending to be finishing a supposed rollback. Is there some way in which I can tell the server not to store any rollback information for my query? Or not to allow any other queries to interfere with the rollback, so that it will not take so long? Or how to rewrite my query in a better way, or how kill the process successfully without restarting the server?

    Read the article

  • SQl Server: serializable level not working

    - by Zé Carlos
    I have the following SP: CREATE PROCEDURE [dbo].[sp_LockReader] AS BEGIN SET NOCOUNT ON; begin try set transaction isolation level serializable begin tran select * from teste commit tran end try begin catch rollback tran set transaction isolation level READ COMMITTED end catch set transaction isolation level READ COMMITTED END The table "test" has many values, so "select * from teste" takes several seconds. I run the sp_LockReader at same time in two diferent query windows and the second one starts showing test table contents without the first one terminates. Shouldn't serializeble level forces the second query to wait? How do i get the described behaviour? Thanks

    Read the article

  • SQL Server stored procedure return code oddity

    - by gbn
    Hello The client that calls this code is restricted and can only deal with return codes from stored procs. So, we modified our usual contract to RETURN -1 on error and default to RETURN 0 if no error If the code hits the inner catch block, then the RETURN code default to -4. Where does this come from, does anyone know...? IF OBJECT_ID('dbo.foo') IS NOT NULL DROP TABLE dbo.foo GO CREATE TABLE dbo.foo ( KeyCol char(12) NOT NULL, ValueCol xml NOT NULL, Comment varchar(1000) NULL, CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (KeyCol) ) GO IF OBJECT_ID('dbo.bar') IS NOT NULL DROP PROCEDURE dbo.bar GO CREATE PROCEDURE dbo.bar @Key char(12), @Value xml, @Comment varchar(1000) AS SET NOCOUNT ON DECLARE @StartTranCount tinyint; BEGIN TRY SELECT @StartTranCount = @@TRANCOUNT; IF @StartTranCount = 0 BEGIN TRAN; BEGIN TRY --SELECT @StartTranCount = 'fish' INSERT dbo.foo (KeyCol, ValueCol, Comment) VALUES (@Key, @Value, @Comment); END TRY BEGIN CATCH IF ERROR_NUMBER() = 2627 --PK violation UPDATE dbo.foo SET ValueCol = @Value, Comment = @Comment WHERE KeyCol = @Key; ELSE RAISERROR ('Tits up', 16, 1); END CATCH IF @StartTranCount = 0 COMMIT TRAN; END TRY BEGIN CATCH IF @StartTranCount = 0 AND XACT_STATE() <> 0 ROLLBACK TRAN; RETURN -1 END CATCH --Without this, we'll send -4 if we hit the UPDATE CATCH block above --RETURN 0 GO --Run with RETURN 0 and fish line commented out DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' SELECT @rtn; SELECT * FROM dbo.foo DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar2 />', 'testing2' --updated OK but we get @rtn = -4 SELECT @rtn; SELECT * FROM dbo.foo --uncomment fish line DECLARE @rtn int EXEC @rtn = dbo.bar 'abcdefghijkl', '<foobar />', 'testing' --Hit outer CATCH, @rtn = -1 as expected SELECT @rtn; SELECT * FROM dbo.foo

    Read the article

  • Problem with passing parameters to SQL procedure using VBA

    - by Stefan Åstrand
    Hi, I am trying to pass @intDocumentNo and @intCustomerNo to a stored procedure using VBA but only @intCustomerNo is updated in dbo.tblOrderHead. I don't think the problem is with the procedure because if I enter the values manually it runs properly. What am I doing wrong? VBA Code: Private Sub intCustomerNo_Click() Dim cmdCommand As New ADODB.Command With cmdCommand .ActiveConnection = CurrentProject.Connection .CommandType = adCmdStoredProc .CommandText = "uspSelectCustomer" '@intDocumentNo .Parameters("@intDocumentNo").Value = Forms![frmSalesOrder].[IntOrderNo] '@intCustomerNo .Parameters("@intCustomerNo").Value = Me![intCustomerNo] .Execute End With DoCmd.Close Forms![frmSalesOrder].Requery End Sub Procedure: UPDATE dbo.tblOrderHead SET dbo.tblOrderHead.intCustomerNo = @intCustomerNo , dbo.tblOrderHead.intPaymentCode = dbo.tblCustomer.intPaymentCode, dbo.tblOrderHead.txtDeliveryCode = dbo.tblCustomer.txtDeliveryCode, dbo.tblOrderHead.txtRegionCode = dbo.tblCustomer.txtRegionCode, dbo.tblOrderHead.txtCurrencyCode = dbo.tblCustomer.txtCurrencyCode, dbo.tblOrderHead.txtLanguageCode = dbo.tblCustomer.txtLanguageCode FROM dbo.tblOrderHead INNER JOIN dbo.tblCustomer ON dbo.tblOrderHead.intCustomerNo = dbo.tblCustomer.intCustomerNo AND dbo.tblOrderHead.intOrderNo = @intDocumentNo

    Read the article

  • How to return a record from function executed by INSERT/UPDATE rule?

    - by seas
    Do the following scheme for my database: create sequence data_sequence; create table data_table { id integer primary key; field varchar(100); }; create view data_view as select id, field from data_table; create function data_insert(_new data_view) returns data_view as $$declare _id integer; _result data_view%rowtype; begin _id := nextval('data_sequence'); insert into data_table(id, field) values(_id, _new.field); select * into _result from data_view where id = _id; return _result; end; $$ language plpgsql; create rule insert as on insert to data_view do instead select data_insert(new); Then type in psql: insert into data_view(field) values('abc'); Would like to see something like: id | field ----+--------- 1 | abc Instead see: data_insert ------------- (1, "abc") Is it possible to fix this somehow? Thanks for any ideas. Ultimate idea is to use this in other functions, so that I could obtain id of just inserted record without selecting for it from scratch. Something like: insert into data_view(field) values('abc') returning id into my_variable would be nice but doesn't work with error: ERROR: cannot perform INSERT RETURNING on relation "data_view" HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause. I don't really understand that HINT. I use PostgreSQL 8.4.

    Read the article

  • Stored procedure call in method with OperationBehavior attribute: problems with transactions

    - by Gerrie Schenck
    I'm using ADO.Net's ExecuteNonQuery to call a stored procedure, works like a charm stand-alone but when implementing it where it should be called I'm running into problems concerning transactions. For example System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. and also a timeout right after that. I've just found out the method which calls the stored procedure is marked with the following WCF attribute: [OperationBehavior(TransactionScopeRequired = true, TransactionAutoComplete = true)] How will this influence the call my stored procedure? How can I tell .Net to execute the stored procedure outside this transaction? The stored procedure contains insert statements and also a transaction, but removing them doesn't change the behavior...

    Read the article

  • Inline Conditional Statement in Stored Procedure

    - by Jason
    Here is the pseudo-code for my inline query in my code: select columnOne from myTable where columnOne = '#variableOne#' if len(variableTwo) gt 0 and columnTwo = '#variableTwo#' end I would like to move this into a stored procedure but am having trouble building the query correctly. I assume it would be something like select columnOne from myTable where columnOne = @variableOne CASE WHEN len(@variableTwo) <> 0 THEN and columnTwo = @variableTwo END This is giving me a syntax error. Could someone tell me what I've got wrong. Also, I would like to keep it to only one query and not just have one if statement. Also, I do not want to build the sql in the stored procedure and run Exec() on it.

    Read the article

  • Stored procedure optimization

    - by George Zacharia
    Hi, i have a stored procedure which takes lot of time to execure .Can any one suggest a better approch so that the same result set is achived. ALTER PROCEDURE [dbo].[spFavoriteRecipesGET] @USERID INT, @PAGENUMBER INT, @PAGESIZE INT, @SORTDIRECTION VARCHAR(4), @SORTORDER VARCHAR(4),@FILTERBY INT AS BEGIN DECLARE @ROW_START INT DECLARE @ROW_END INT SET @ROW_START = (@PageNumber-1)* @PageSize+1 SET @ROW_END = @PageNumber*@PageSize DECLARE @RecipeCount INT DECLARE @RESULT_SET_TABLE TABLE ( Id INT NOT NULL IDENTITY(1,1), FavoriteRecipeId INT, RecipeId INT, DateAdded DATETIME, Title NVARCHAR(255), UrlFriendlyTitle NVARCHAR(250), [Description] NVARCHAR(MAX), AverageRatingId FLOAT, SubmittedById INT, SubmittedBy VARCHAR(250), RecipeStateId INT, RecipeRatingId INT, ReviewCount INT, TweaksCount INT, PhotoCount INT, ImageName NVARCHAR(50) ) INSERT INTO @RESULT_SET_TABLE SELECT FavoriteRecipes.FavoriteRecipeId, Recipes.RecipeId, FavoriteRecipes.DateAdded, Recipes.Title, Recipes.UrlFriendlyTitle, Recipes.[Description], Recipes.AverageRatingId, Recipes.SubmittedById, COALESCE(users.DisplayName,users.UserName,Recipes.SubmittedBy) As SubmittedBy, Recipes.RecipeStateId, RecipeReviews.RecipeRatingId, COUNT(RecipeReviews.Review), COUNT(RecipeTweaks.Tweak), COUNT(Photos.PhotoId), dbo.udfGetRecipePhoto(Recipes.RecipeId) AS ImageName FROM FavoriteRecipes INNER JOIN Recipes ON FavoriteRecipes.RecipeId=Recipes.RecipeId AND Recipes.RecipeStateId <> 3 LEFT OUTER JOIN RecipeReviews ON RecipeReviews.RecipeId=Recipes.RecipeId AND RecipeReviews.ReviewedById=@UserId AND RecipeReviews.RecipeRatingId= ( SELECT MAX(RecipeReviews.RecipeRatingId) FROM RecipeReviews WHERE RecipeReviews.ReviewedById=@UserId AND RecipeReviews.RecipeId=FavoriteRecipes.RecipeId ) OR RecipeReviews.RecipeRatingId IS NULL LEFT OUTER JOIN RecipeTweaks ON RecipeTweaks.RecipeId = Recipes.RecipeId AND RecipeTweaks.TweakedById= @UserId LEFT OUTER JOIN Photos ON Photos.RecipeId = Recipes.RecipeId AND Photos.UploadedById = @UserId AND Photos.RecipeId = FavoriteRecipes.RecipeId AND Photos.PhotoTypeId = 1 LEFT OUTER JOIN users ON Recipes.SubmittedById = users.UserId WHERE FavoriteRecipes.UserId=@UserId GROUP BY FavoriteRecipes.FavoriteRecipeId, Recipes.RecipeId, FavoriteRecipes.DateAdded, Recipes.Title, Recipes.UrlFriendlyTitle, Recipes.[Description], Recipes.AverageRatingId, Recipes.SubmittedById, Recipes.SubmittedBy, Recipes.RecipeStateId, RecipeReviews.RecipeRatingId, users.DisplayName, users.UserName, Recipes.SubmittedBy; WITH SortResults AS ( SELECT ROW_NUMBER() OVER ( ORDER BY CASE WHEN @SORTDIRECTION = 't' AND @SORTORDER='a' THEN TITLE END ASC, CASE WHEN @SORTDIRECTION = 't' AND @SORTORDER='d' THEN TITLE END DESC, CASE WHEN @SORTDIRECTION = 'r' AND @SORTORDER='a' THEN AverageRatingId END ASC, CASE WHEN @SORTDIRECTION = 'r' AND @SORTORDER='d' THEN AverageRatingId END DESC, CASE WHEN @SORTDIRECTION = 'mr' AND @SORTORDER='a' THEN RecipeRatingId END ASC, CASE WHEN @SORTDIRECTION = 'mr' AND @SORTORDER='d' THEN RecipeRatingId END DESC, CASE WHEN @SORTDIRECTION = 'd' AND @SORTORDER='a' THEN DateAdded END ASC, CASE WHEN @SORTDIRECTION = 'd' AND @SORTORDER='d' THEN DateAdded END DESC ) RowNumber, FavoriteRecipeId, RecipeId, DateAdded, Title, UrlFriendlyTitle, [Description], AverageRatingId, SubmittedById, SubmittedBy, RecipeStateId, RecipeRatingId, ReviewCount, TweaksCount, PhotoCount, ImageName FROM @RESULT_SET_TABLE WHERE ((@FILTERBY = 1 AND SubmittedById= @USERID) OR ( @FILTERBY = 2 AND (SubmittedById <> @USERID OR SubmittedById IS NULL)) OR ( @FILTERBY <> 1 AND @FILTERBY <> 2)) ) SELECT RowNumber, FavoriteRecipeId, RecipeId, DateAdded, Title, UrlFriendlyTitle, [Description], AverageRatingId, SubmittedById, SubmittedBy, RecipeStateId, RecipeRatingId, ReviewCount, TweaksCount, PhotoCount, ImageName FROM SortResults WHERE RowNumber BETWEEN @ROW_START AND @ROW_END print @ROW_START print @ROW_END SELECT @RecipeCount=dbo.udfGetFavRecipesCount(@UserId) SELECT @RecipeCount AS RecipeCount SELECT COUNT(Id) as FilterCount FROM @RESULT_SET_TABLE WHERE ((@FILTERBY = 1 AND SubmittedById= @USERID) OR (@FILTERBY = 2 AND (SubmittedById <> @USERID OR SubmittedById IS NULL)) OR (@FILTERBY <> 1 AND @FILTERBY <> 2)) END

    Read the article

  • CTE Join query issues

    - by Lee_McIntosh
    Hi everyone, this problem has me head going round in circles at the moment and i wondering if anyone could give any pointers as to where im going wrong. Im trying to produce a SPROC that produces a dataset to be called by SSRS for graphs spanning the last 6 months. The data for example purposes uses three tables (theres more but the it wont change the issue at hand) and are as follows: tbl_ReportList: Report Site ---------------- North abc North def East bbb East ccc East ddd South poa South pob South poc South pod West xyz tbl_TicketsRaisedThisMonth: Date Site Type NoOfTickets --------------------------------------------------------- 2010-07-01 00:00:00.000 abc Support 101 2010-07-01 00:00:00.000 abc Complaint 21 2010-07-01 00:00:00.000 def Support 6 ... 2010-12-01 00:00:00.000 abc Support 93 2010-12-01 00:00:00.000 xyz Support 5 tbl_FeedBackRequests: Date Site NoOfFeedBackR ---------------------------------------------------------------- 2010-07-01 00:00:00.000 abc 101 2010-07-01 00:00:00.000 def 11 ... 2010-12-01 00:00:00.000 abc 63 2010-12-01 00:00:00.000 xyz 4 I'm using CTE's to simplify the code, which is as follows: DECLARE @ReportName VarChar(200) SET @ReportName = 'North'; WITH TicketsRaisedThisMonth AS ( SELECT [Date], Site, SUM(NoOfTickets) AS NoOfTickets FROM tbl_TicketsRaisedThisMonth WHERE [Date] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0) GROUP BY [Date], Site ), FeedBackRequests AS ( SELECT [Date], Site, SUM(NoOfFeedBackR) AS NoOfFeedBackR FROM tbl_FeedBackRequests WHERE [Date] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0) GROUP BY [Date], Site ), SELECT trtm.[Date] SUM(trtm.NoOfTickets) AS NoOfTickets, SUM(fbr.NoOfFeedBackR) AS NoOfFeedBackR, FROM Reports rpts LEFT OUTER JOIN TotalIncidentsDuringMonth trtm ON rpts.Site = trtm.Site LEFT OUTER JOIN LoggedComplaints fbr ON rpts.Site = fbr.Site WHERE rpts.report = @ReportName GROUP BY trtm.[Date] And the output when the sproc is pass a parameter such as 'North' to be as follows: Date NoOfTickets NoOfFeedBackR ----------------------------------------------------------------------------------- 2010-07-01 00:00:00.000 128 112 2010-08-01 00:00:00.000 <data for that month> <data for that month> 2010-09-01 00:00:00.000 <data for that month> <data for that month> 2010-10-01 00:00:00.000 <data for that month> <data for that month> 2010-11-01 00:00:00.000 <data for that month> <data for that month> 2010-12-01 00:00:00.000 122 63 The issue I'm having is that when i execute the query I'm given a repeated list of values of each month, such as 128 will repeat 6 times then another value for the next months value repeated 6 times, etc. argh!

    Read the article

  • How do I use Entity Framework in a CLR stored procedure?

    - by Ivan
    I am looking forward to move all the logic (which is implemented as manipulating Entity Framework 4 objects) to a server side. It looks going to be simple (thanks to the application structure) and beneficial (as all I have is one oldy laptop as a client and one tough server which runs SQL Server 2008, and building a separate service for the logic can just introduce more latency if compared to doing it inside the database). So how do I correctly use Entities Framework inside a CLR stored procedure and make it using a host-server-provided SqlContext?

    Read the article

  • Recursive COUNT Query (SQL Server)

    - by Cosmo
    Hello Guys! I've two MS SQL tables: Category, Question. Each Question is assigned to exactly one Category. One Category may have many subcategories. Category Id : bigint (PK) Name : nvarchar(255) AcceptQuestions : bit IdParent : bigint (FK) Question Id : bigint (PK) Title : nvarchar(255) ... IdCategory : bigint (FK) How do I recursively count all Questions for a given Category (including questions in subcategories). I've tried it already based on several tutorials but still can't figure it out :(

    Read the article

  • Modify SQL result set before returning from stored procedure

    - by m0sa
    I have a simple table in my SQL Server 2008 DB: Tasks_Table -id -task_complete -task_active -column_1 -.. -column_N The table stores instructions for uncompleted tasks that have to be executed by a service. I want to be able to scale my system in future. Until now only 1 service on 1 computer read from the table. I have a stored procedure, that selects all uncompleted and inactive tasks. As the service begins to process tasks it updates the task_active flag in all the returned rows. To enable scaleing of the system I want to enable deployment of the service on more machines. Because I want to prevent a task being returned to more than 1 service I have to update the stored procedure that returns uncompleted and inactive tasks. I figured that i have to lock the table (only 1 reader at a time - I know I have to use an apropriate ISOLATION LEVEL), and updates the task_active flag in each row of the result set before returning the result set. So my question is how to modify the SELECT result set iin the stored procedure before returning it?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 20 21 22 23 24 25 26 27 28 29 30 31  | Next Page >