Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category
I used to do this with classic asp but I'm not sure how to do it with .net.
Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category.
I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.
Just for an example..
Category Table:
ID Category
1 Saints
2 Falcons
3 Bucaneers
4 Chargers
5 Falcons
Player Table:
ID CategoryID Player News Player Last Updated
1 1 Reggie Bush Poetry in motion 9/21/2006
2 1 Drew Brees What shoulder injury? 9/18/2006
3 5 Michael Vick Break a leg, seriously. 9/20/2006
Basically I would need to display on a page:
Saints
Reggie Bush
Poetry in Motion
Falcons
Michael Vick
Break a leg, seriously.
So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.
I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
View Complete Forum Thread with Replies
Related Forum Messages:
Stored Procedure Using UNION Joins Is Not Displaying Correctly... Can Someone Help Me With My Logic?
I have a stored procedure using UNION joins on three SQL queries. Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :) I need to return one result set, sorted by date... one complete result per day. eg: 5/15/2007 | XX | XX | XX | XX | XX | XX |5/16/2007 | XX | XX | XX | XX | XX | XX |5/17/2007 | XX | XX | XX | XX | XX | XX | Currently, when I run the query, I'm getting three separate date values for each date... eg:5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |etc How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong". Here is the stored procedure if you can help. I'd really really love the help! C R E A T E P R O C E D U R E sp_ApptActivityDate (@strWHERE as varchar(500), @strWHERECANCELED as varchar(500)) as exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_ALL_ApptActivity ' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_Active_ApptActivity' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + ' ORDER BY dbo.vw_ALL_ApptActivity.[Date] ' )GO
View Replies !
Stored Procedure When Calling From Front End Not Displaying Any Data In The Gridview.
Hi i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control. here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values. ALTER Procedure [dbo].[usp_CheckUser] @name nvarchar(50), @ssn nvarchar(50), @accountnumber nvarchar(50) AS BEGIN if(@name!=null AND @ssn!=null AND @accountnumber!=null) select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber else if(@name=null AND @ssn!=null AND @accountnumber!=null) select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber else if(@name=null AND @ssn=null AND @accountnumber!=null) select * from Userinfo where accountnumber=@accountnumber else if(@name!=null AND @ssn=null AND @accountnumber!=null) select * from Userinfo where accountnumber=@accountnumber AND name=@name else if(@name!=null AND @ssn=null AND @accountnumber=null) select * from Userinfo where name=@name else if(@name!=null AND @ssn!=null AND @accountnumber=null) select * from Userinfo where name=@name AND ssn=@ssn else if(@name=null AND @ssn!=null AND @accountnumber=null) select * from Userinfo where ssn=@ssn end table name is userinfo please help me with this. its very urgent. thanx for your help in advance. ramya
View Replies !
Category/Parent Category Design And Querying
Hi, I have a simple table: Categories ---------- CategoryID ParentID Name I want to associate my Products to a category so that I can search by category....ok. If I just have a simlpe table: CategoryProducts ------------------- CategoryId ProductId I can link a product to a category. Now, if I just link a product to a single category, such as the bottom leaf category: Self Help / Personal Development / Spiritual / Meditation I would link a product to the Meditation category. However if I click on Self Help while browsing, I want to see all items underneath Personal Development, Spiritual and Meditiation. So my question is is this a good way to store the product-category relationships, or should I put many entries into CategoryProducts to keep the queries simlpe and faster? Are they faster doing it this way? In this way there would be 4 entries for a product in meditation. My personal idea is that adding all entries up a tree arm of a category path will be cumbersome to manage, but it does solve the problem of clicking on Self Help and seeing all products that exist within sub-categories. I am sure an SQL query would be able to work this out, but I dont know if performance would be something to consider on an ecommerce site? Are there any patterns fo rthis stuff - seems a reasonably repeatable pattern for business sites? Thanks, jr.
View Replies !
Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View Replies !
Product Category Search In Stored Proc
I have a db of products, the table structure consisting of,id, name, description, categoryID, TreatmentTypeIDI originally displayed the products by just the category. Now I have included a Treatmenttype for each product as well, and wish for this type of search to be included.So, to view the products, the querystring would read...products.aspx?cat=1my code would read the querystring, insert into the stored proc, and return a paged dataset back to me.Now I want to sort by another field, being treatment type.How do I place this into my stored proc, or would I have to create a new stored procedure for this instance. My current stored proc is shown below...CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int, @CategoryID int, @TotalRecs int OUTPUT, @CategoryName varchar(50) OUTPUT )AS-- We don't want to return the # of rows inserted-- into our temporary table, so turn NOCOUNT ONSET NOCOUNT ON--Create a temporary tableCREATE TABLE #TempItems( ID int IDENTITY, PID int, Name varchar(100), SDescription varchar(500), Size varchar(10), ImageURL varchar(100), Color varchar(7))-- Insert the rows from tblItems into the temp. tableINSERT INTO #TempItems (PID, Name, SDescription, Size, ImageURL, Color)SELECT Products.ID, Products.Name, Products.SDescription, Products.Size, Products.ImageURL, ProductCategories.Color FROM Products INNER JOIN ProductCategories ON Products.CategoryID = ProductCategories.CategoryID WHERE Products.CategoryID=@CategoryID-- Find out the first and last record we wantDECLARE @FirstRec int, @LastRec intSELECT @FirstRec = (@Page - 1) * @RecsPerPageSELECT @LastRec = (@Page * @RecsPerPage + 1)SELECT @TotalRecs = Count(*) FROM Products WHERE CategoryID=@CategoryIDSELECT @CategoryName = Name FROM ProductCategories WHERE CategoryID=@CategoryIDSELECT *, MoreRecords = ( SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec ) FROM #TempItemsWHERE ID > @FirstRec AND ID < @LastRec-- Turn NOCOUNT back OFFSET NOCOUNT OFF
View Replies !
Querying Most Recent Values Per Category Per Item, Alternatives To Subqueries?
Hopefully someone can suggest a better solution than what I'm currently hobbling along with.Basically, I've got a table that has rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest value for each category, for each particular item. I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or something amazingly obvious. But for the time being, I've got a less-elegant query that returns the correct data. It just uses subqueries.Here's the T-SQL to run my scenario: DECLARE @actionHistoryTable TABLE ( itemID int, actionType int, actionValue nvarchar(50) NULL, actionTime datetime )INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008') -- JUST DISPLAY THE RAW TABLE FOR THIS EXAMPLESELECT * FROM @actionHistoryTable -- THIS RETURNS THE RESULTS I'M WANTING, IT ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMIDSELECT aht.itemID ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '1' ORDER BY aht2.actionTime DESC ) as 'latest type 1 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '2' ORDER BY aht2.actionTime DESC ) as 'latest type 2 value' ,( SELECT TOP 1 aht2.actionValue FROM @actionHistoryTable aht2 WHERE aht.itemID = aht2.itemID AND aht2.actionType = '3' ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'FROM @actionHistoryTable ahtGROUP BY aht.itemID Is there a better way?-Steve
View Replies !
Looping In Stored Procedure
I have a stored procedure which adds a color + colortype to a table. I then need to select from a separate table all tools that support the colortype. Finally I need to insert into a separate table the tools + colortype. I would like to do this all in one stored procedure.My rough code is:INSERT INTO ColorsTable (color, colortype) VALUES (@color, @colortype);SELECT tools FROM ToolsTable WHERE colortype = @colortype[For Each tool] INSERT INTO MyTable tool, @colortypeMy problem is that I don't understand how to do a For Each Thank-you for any advice.
View Replies !
Looping In A Stored Procedure
Hello, I am trying to creating a while loop. I have created a variable called @MyCount which I initially set to 1. For each iteration of the loop, I would like the value of @MyCount to increase by 1. What am I doing wrong? WHILE ( @MyCount <= @DaysInMonth ) BEGIN /* put some code in here *./ @MyCount = @MyCount + 1 END Thanks
View Replies !
Help With Looping In Stored Procedure
help combinign stored procedure queries first get userid select distinct userid from allrecords where stage=1 and datediff(day,0,mydatestage1)=datediff(day,0,getdate()-1) then i need to take this and loop through the results and do queries based on the results. like select @username=username from users where userid=@userid select @totalrecords=count(id) from allrecords where stage=1 and datediff(day,0,mydatestage1)=datediff(day,0,getdate()-1) and more how can i do this looping through the users and then running sub queries?
View Replies !
Looping In Stored Procedure
Hi, I need to code a stored procedure. It will select all the rows from a table. I then need some sort of looping mechanism to filter through all the rows. It is too difficult to handle the filtering with a simple 'WHERE' clause. I will have to do some data manipulation on fields within the rows to determine which ones I could use. I need for the SP to return all the rows which qualify. Could someone provide me with a link and/or some skeleton code to give me somewhere to start on how to code the SP? Thank you.
View Replies !
Looping Through Each Row In An XML Object Sent To A Stored Procedure
I have an XML object (sent as a string, received as an XML datatype) that's in a Stored Procedure. Each row in the XML file will have 1 value from it inserted into one of three tables. The tables are depended upon the other value from the XML file.The XML File is layed out as:<Values> <value> <value>1</value> <key>My_Field</key> </value> <value> <value>3523.2</value> <key>My_other_Field</key> </value></Values>I basically need to go through it row by row, find out what table I need to insert the value into using the key field.Any help with this would rock. I'm using SQL 2005.
View Replies !
Looping A Stored Procedure In A Dts Package
Hello,I have a stored procedure that processes an individual file from adirectory and archives it in a subdirectory.Now, the problem is, when iexecute it , it will only process one file. What i want to do is to checkto see if there are any files in the folder, and if there are , processthem all, and once done, go to the next part in a DTS package, if there areno files, simply go to the next part in the DTS package. I tried an activexscript that would get the filecount in the folder, and if there were morethan 0 files in the folder, then DTS-sUCCESS and on "success" workflow , itwould run the stored procedure, and thus it woould process one file, then"on completion" the workflow connected it back to the activeX script(thuslooping), which would count the files again. Now if there were 0 files, itwould report DTS_FAILIURE, and I had it set up ,"on failiure" to go to thenext step in the package, but it wouldn't run.Someone mind showing me a ray of light?
View Replies !
Looping Through A Recordser In A Stored Procedure
I have a table [myOrders] with three columns. One of the columnscontains text output data [myText] nvarchar(500), one of them containsa filename [myFileName] nvarchar(50), one of the columns is a bit torecord if it has been output yet[isOutput] bit default value = 0.I am creating a SQL Agent job that needs to look at a recordset of[myOrders] where [isOutput] = 0 and create a seperate text file foreach row using [myFileName] as the filename.Then I need to mark [isOutput] of each record in [myOrders] as 1.Ok, so that's the task...What I'm thinking is I construct a stored procedure that starts with aselect statement:Create PROCEDURE JustDoItASset nocount onSELECTmyText, myFileNameFROMmyOrdersWHERE(isOutput = 0)THEN I USE BCP to create the file looping through the recordset above.THIS IS THE PART I AM CLUELESS ABOUT!/* NEED TO LOOP HERE */DECLARE @ReturnCode intDECLARE @ExportCommand varchar(255)DECLARE @FileName nvarchar(50)DECLARE @FileText nvarchar (500)SELECT @FileName = myFileName/*THIS SYNTAX IS PROBABLY TOTALLY OUTA WHACK:)SET @ExportCommand ='BCP @FileText queryout "c:' +@FileName +'" -T -c -S ' + @@SERVERNAMEEXEC @ReturnCode = master..xp_cmdshell @ExportCommand/* NEED TO EXIT LOOP HERE */Then I update all records in [myOrders] to 1BEGIN TRANSACTIONUPDATEmyOrdersSET isOutput = 1WHERE(isOutput = 0)/* err checking here */COMMIT TRANSACTIONI'm hoping someone can help me construct this.Thanks,lq
View Replies !
Stored Procedure Query Question - Looping
I have a stored procedure that I want to basically pull in a code/codes which correspond to people. I then convert the code to the persons actual name that I then want to send back and add to a listbox. Right now with the code I have, it returns just one name. I know I am doing something wrong, but I am not sure if what I want is possible with a stored procedure or if I am on the right track. my code is as follows. The proceedure I am running is as follows... set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int) AS BEGIN DECLARE @First as varchar(200) DECLARE @Middle as varchar(50) DECLARE @Last as varchar(200) DECLARE @Code as varchar(200) Select @Code = [SectyCode] From tblMain2 as M LEFT JOIN tblSectyData as S ON M.Code = S.Code WHERE M.Code = @id SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = @Code SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = @Code SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = @Code Select ISNULL(@First, '') + ' ' + ISNULL(@Middle, '') + ' ' + ISNULL(@Last,'') AS FullName END ////which is called from SqlCommand ShowTeam = new SqlCommand("Exec dbo.GetAttySectyData '" + selectedEmployee + "' ", IntranetConnection); To populate the listbox, I was running this. As I mentioned, I get one name. IntranetConnection.Close(); IntranetConnection.Open(); IntranetReader = ShowTeam.ExecuteReader(); while (IntranetReader.Read()) { TeamList2.Items.Add(IntranetReader["FullName"].ToString()); } IntranetConnection.Close();
View Replies !
Can't Add New Stored Procedure Item In Database Project
I'm trying to create a CLR stored procedure and went ahead and created a Database Project. But when I click on New Item... it doesn't contain "Stored Procedure" as an item, just script items. How do I add this template? At first I was thinking well maybe it knows my SQL Server doesn't have CLR turned on, so I went ahead and turned that on but still doesn't show up. Any ideas? Thanks, Craig
View Replies !
Unable To Execute Stored Procedure When New Item Is Selected In Dropdown.
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
View Replies !
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it? What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results. Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application. However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password. Looking forward for replies from expert here. Thanks in advance. Note: Hope my explaination here clearly describe my current problems.
View Replies !
{fn CURDATE()} Inside Stored Procedure
Hi,I created the stored procedure in SQL Server 2000CREATE PROCEDURE RsaTestASSELECT {fn CURDATE()}and to my surprise found out that it worked..To my surprise, because all the documentationthat I find suggests that {fn CURDATE()} isan ODBC escape clause..I can understand why/how ODBC escape clauseswork in queries, but not how this would workinside a stored procedure.I created the sp by running the above code inqueryanalyzer. sp_helptext 'RsaTest' shows thatthe {fn CURDATE()} is still in there, so itdoesn't appear to get transformed in to SQLServer specific code by whatever means queryanalyzer uses to connect to the database..Can anyone explain how this works? Or whereto find more info on {fn } syntax inside storedprocedures?Cheers,Rsa
View Replies !
If Statement Inside A Stored Procedure!!!!
hi all, i'm wondering if i can use one stored procedure in too cases, this is the senario: i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user if there a way to use if and else to make this happen this is what i did so far: CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as if @indexType = 1 begin SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin) end else begin SELECT * FROM table WHERE user = @user end GO any suggestion will be very helpful thanks
View Replies !
How Can I Use A Stored Procedure Call Inside The WHERE?
Hi folks I nead to call a stored procedure in a where statemene, but MSSQL dont like that. My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code ALTER PROCEDURE dbo.advsp_FilterRecordRights @RequesterGUID Char(20), @EntityGUID Char(20) AS BEGIN if not Exists(Select * from GUIDRightsH where GUIDRightsH.EntityGUID = @EntityGUID and GUIDRightsH.RequesterGUID = @RequesterGUID and GUIDRightsH.RecProp <> 0) Begin Return 1 end if not Exists(Select * from UsergroupMembers where UserGroupMembers.UsergroupGUID = @RequesterGUID and dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1) Begin Return 1 end Return 0 END
View Replies !
RaiseError Inside Stored Procedure
I want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later. here is an example BEGIN procedure Insert something and save it even if error is raised below RaiseError('you made a mistake and need to do this.',16,-1) END procedure
View Replies !
Iterate Result Set Inside Stored Procedure
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail). How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */ SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */ SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/ SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)SET @p_message = @p_message + "Comment: " + @comment + char(13)PRINT "subject: " + @p_subject + char(13)PRINT "recip: " + @p_recipients + char(13)PRINT "msg: " + @p_message + char(13)/*Send the email*/ Execute master..xp_sendmail @recipients = @p_recipients, @message = @p_message, @subject = @p_subject END GO
View Replies !
Using Joins Inside Stored Procedure With Row_Number
This example is working: Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'. I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.
View Replies !
Comparing Two Strings Inside A Stored Procedure
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though. So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>" Is there an way to accomplish this inside a stored procedure?
View Replies !
Executing A Variable Inside A Stored Procedure
Hello :) I need to do something like this: CREATE PROCEDURE SelectCostumers @name varchar(100) Declare @SQL = "SELECT Id, Name FROM Costumers" AS IF (@name IS NULL) @SQL ELSE @SQL += "WHERE Name LIKE @name" See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get. Thank you
View Replies !
How To Dynamically Create SQL Inside A Stored Procedure?
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors. Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ? ALTER PROCEDURE dbo.spGetNextPageRecords ( @pageSize int, @previousMaxId int ) AS /* SET NOCOUNT ON */ SELECT Top @pageSize ProductId, ProductName FROM Products WHERE (ProductID > @previousMaxId) order by ProductId RETURN
View Replies !
How To Query Two MS SQL DB's On The Same Server Inside A Stored Procedure
Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!
View Replies !
Creating A Table Inside A Stored Procedure
I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message: Error 208: Invalid object name '##OPTIONSEX' I am using the following SQL script: CREATE PROCEDURE [dbo].[Test2] AS CREATE TABLE ##OPTIONSEX ( OPTION_PLAN VARCHAR(50), TOT_OPTIONS_EXCHANGED FLOAT NULL ) GO INSERT ##OPTIONSEX SELECT B.COMPONENT, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS) FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B WHERE B.COMPONENTID = A.COMPONENTID GROUP BY B.COMPONENT GO Any help getting this to run correctly would be appreciated.
View Replies !
Query From Parameters Inside A Stored Procedure
Hi, I have to write a stored procedure what repair a table. It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID'). I do not found the way to write this procedure to can create statement from parameter of the procedure. I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'. If the parameter is in the 'WHERE', nothing problem. My procedure is: CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @OrphanRowCount int select @OrphanRowCount = 1 while(@OrphanRowCount > 0) begin DECLARE KWCursor CURSOR FOR SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176); OPEN KWCursor; FETCH NEXT FROM KWCursor INTO @OrphanRowCount CLOSE KWCursor DEALLOCATE KWCursor IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0) BEGIN exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)') END end END GO How can I run fully parameterized queries from an SP. I can make it only like exec('DELETE FROM' + @TableName + ...) Thank you for any idea, Imre
View Replies !
Debugging Stored Procedure Inside SSMSE !!!
Hi for all i just get the news that the Dec. CTP of SQL Server 2005 & tools is released ! but my dream is to see debugging stored procedure support inside the SSMSE as debugging applications inside Visual Studio 2005 Many many many thanks to Microsoft and for the great efforts on the cool products Mohamed
View Replies !
Commit Not Commiting Inside Stored Procedure
Hi Folks! I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records. While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update. Here is my sample record in table :tblMsg SenderRef <space> MsgStatusId <space> MsgType <space>Groupid 1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902 1281341 <space>18 <space>KBC-RCVD-ADM <space>191902<space> and here is my code. CREATE PROCEDURE msg_ResolveADMBeforeBlim AS DECLARE @blimGroupId AS INT DECLARE @admSenderRef AS VARCHAR(50) DECLARE admCursor CURSOR FOR SELECT senderref FROMtblMsg WHERE msgstatusid = 18 AND msgtype = 'KBC-RCVD-ADM' FOR UPDATE OPEN admCursor FETCH NEXT FROM admCursor INTO @admSenderRef WHILE @@FETCH_STATUS = 0 BEGIN --FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM SELECT @blimGroupId = GroupID FROM tblMsg WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM' AND SenderRef = @admSenderRef PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef; --UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3 BEGIN UPDATE tblMsg SET MsgStatusId = 3, GroupId = @blimGroupId WHERE CURRENT OF admCursor END PRINT 'AFTER UPDATE INSIDE WHILE'; END CLOSE admCursor DEALLOCATEadmCursor The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy. Any ideas why this would happen? Thanks for your help. Arun
View Replies !
Problem When Invoking Stored Procedure With Cursor From Inside .net
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,
View Replies !
How To Pass The Value Of A Text Box Inside A FORMVIEW To A SQL Stored Procedure
Hi, I'm new to ASP.NET 2.0. I have a sqldatasource and a formview controls on a web page and inside the formview control I have two textboxes. When I click the UPDATE button in the formview, I'd like to be able to retrieve the values of the textboxes inside the formview control and pass these values to a 'Update' SQL stored procedure defined in the Sqldatasource. Does anyone know how I can do this? Hope my question is clear. Thanks in advance. hakl
View Replies !
To Pass Data To An Stored Procedure Inside Sql Server
I want to use a stored procedure inside Sql Server from my aspx page so that the data entered in the form goes to the database after submit. My stored procedure (inside the SQL Server) inserts several fields in a table and it returns two variables. What code I need to write in order to pass the data from the form to the stored procedure inside the Sql Server? And to store the two returned values? Thanks
View Replies !
SELECT Query Stmt Inside Stored Procedure
Friends, What are the possible usuages of a SELECT query stmt inside a stored procedure ?? How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ?? can any one throw some lite on this .. Thanks, SqlPgmr
View Replies !
Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example Select abc, cde, 'xyz' = Case When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter} When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter} end from MyTable Thanks Arcady
View Replies !
Help-convert Date Inside Stored Procedure Problem
problem convet date Code Snippet DECLARE @y_Date [varchar](4),@M_Date [varchar](2),@d_Date [varchar](2) DECLARE @mydate [datetime] set @y_Date='2008' set @M_Date ='8' SET @d_Date='05' set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS DATETIME) select @mydate 2008-05-08 00:00:00.000 and it look ok but whan i do this Code SnippetEXECUTE [dbo].[testpro] @mydate the date not pass properly !! Code Snippet DECLARE DECLARE @mydate [datetime] set @mydate = getdate() 2008-05-08 01:20:35.870 like this it work ok what is the problem with tis line ? set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS tnx
View Replies !
Insertion And Deletion Of Records Inside A Single Stored Procedure
Hi, I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days. Thanx in Advance
View Replies !
How To Use A Stored Procedure Inside Select Query (sql Server Version 8)
Hi, Please help me in this problem... i am new to sql server.. i am using sql server version 8...(doesnot support function with retun values..) so i have created a procedure... -----------procedure------------------(to find next monday after 6 months)------------------- [code] create proc next_Monday ( @myDate DATETIME ) as BEGIN set @myDate = dateadd(mm, 6, @myDate) while datepart(dw,@myDate) <> 2 begin set @myDate = dateadd(dd, 1, @myDate) end select @myDate end go [/code] -------------------------------------------------------- i can able to execute this procedure separately.... working well... but don't know how to call it inside another query.... the following throws error.... select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster please help me... how to fix this problem...
View Replies !
Using Multiple Distinct Queries Inside Single Stored Procedure
Hello, I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella. For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So: IF @QUERY_ID = 1 BEGIN SELECT [whatever] FROM [tbl1] WHERE [conditions] GROUP BY [something] ORDER BY [somethingelse] END ELSE IF @QUERY_ID = 2 BEGIN SELECT [whatever] FROM [tbl2] WHERE [conditions] GROUP BY [something] ORDER BY [somethingelse] END END I hope that makes sense. Thanks in advance.
View Replies !
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Hi all, I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE): (1) /////--spTopSixAnalytes.sql--/// USE ssmsExpressDB GO CREATE Procedure [dbo].[spTopSixAnalytes] AS SET ROWCOUNT 6 SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName FROM LabTests ORDER BY LabTests.Result DESC GO (2) /////--spTopSixAnalytesEXEC.sql--////////////// USE ssmsExpressDB GO EXEC spTopSixAnalytes GO I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--/////////// Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;") Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection) sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure 'Pass the name of the DataSet through the overloaded contructor 'of the DataSet class. Dim dataSet As DataSet ("ssmsExpressDB") sqlConnection.Open() sqlDataAdapter.Fill(DataSet) sqlConnection.Close() End Sub End Class /////////////////////////////////////////////////////////////////////////////////////////// I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1) Please help and advise. Thanks in advance, Scott Chang More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View Replies !
How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?
Hi! I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit. I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected. There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link. I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example =First(Fields!StoredProcedure.Value, "ds_ReportInfo") That gives an error I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried: Exec (@StoredProcedure) In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query. Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown. The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that. I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure. Any suggestions? Is anyone else trying to do this? Thanks Karen
View Replies !
|
TRACKER
