Stored Proceedure Question - Looping? Or Is There A Better Way?
Jan 17, 2008
I am writing a bit of code for our intranet using ASP.NET C# and SQL2005. We have a program called Aboutface that is a web based firm directory. We are using the SQL database it has in order to pull data out of it and integrate it into our intranet as we dont really care for its original interface.
I am most interested in setting up relationships.
An attorney only has one secretary that supports them, but a secretary can have many attorneys they support. This is the nature of my problem. The code below is my stored procedure. I am passing in an int which is the ID, and my goal is to generate the ID of and the name of the person who supports/is supported by. With 1 to 1 relationships, it works fine.. With more than that, it blows up because its finding multuple names (of attorneys being supported by a secretary)
I was told I might want to use a FOR EACH loop in the SP. I assume I would want to also generate a COUNT variable (to be used in the procedure and to also build the rows of my table outside of it.) and a NAME variable for each name it finds to populate the table.... but from there I am not sure if I am on the right lines of thinking or where to begin.
Any thoughts/suggestions would be greatly appreciated.
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)
SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id ) SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
Hi people, I'm using the following SP to return the rank of users but I really want it to return just a single Row column and also a Count() of the numer of Users there are....at the moment it sending me the whole table of ranked users. Any ideas?
ALTER PROCEDURE dbo.RankUsersOnScore ( @UserID INT ) AS SET NOCOUNT ON; SELECT Row, Score, UserID FROM (SELECT ROW_NUMBER() OVER (ORDER BY Score DESC) AS Row, Score, UserID FROM dbo.tblUserStats ) AS tblUsersRanked
I was using a proceedure i created in code but because of the way i am using it i decided that a stored proceedure will work better
i have a few add proceedures that work for insert and update but when i tried a select command, no matter what the data entered it returns the first record in the table when I fill the dataset does any one have a clue as to why it would do this is there something you have to return through the stored proceedure like you do when you use @@Identity
this is what i currently have,
CREATE PROCEDURE Location_Select
@p1 char(15) AS SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1) go
do i need this? and if so what can i return i tried LocationID and it yelled at me saying that is an invalid column name
CREATE PROCEDURE Location_Select
@p1 char(15), @retval int output
AS SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)
How can you run a DST package from a stored proceedure. I am using sql server 2000 i cant find the syntax anywhere it is a DTS that takes a file and imports it into a table in the db
Hope someone can help.I am trying to write a stored proceedure to display sales activity by monthand then sum all the columbs.The problem is that our sales year starts in April and end in March.So far I have been able to get the sales info my using to sp's, one that saymonth >3 and the other says <4. I pass in a year parameter, that for thisyears figures would be 2003 for sp1 and 2004 for sp4.I am sure there is a better way.Below is a copy of one of my sp's.Hope you are able to help.JohnALTER PROCEDURE dbo.sp_SalesAnalFigures_P1(@Year nvarchar(50),@CCode varchar(50),@SCode varchar(50),@OType varchar(50))AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) ASMonth, SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS Sales,SUM(dbo.InvoiceItemsCostQry.TotalCost) AS Cost,SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet -dbo.InvoiceItemsCostQry.TotalCost) AS Margin,COUNT(dbo.InvoiceHeaderTbl.InvoiceNo) AS NoOfInvoices,AVG(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS AverageValueFROM dbo.InvoiceHeaderTbl INNER JOINdbo.InvoiceItemsCostQry ON dbo.InvoiceHeaderTbl.InvoiceNo =dbo.InvoiceItemsCostQry.InvoiceNoWHERE (DATEPART(yyyy, dbo.InvoiceHeaderTbl.InvoiceDate) = @Year) AND(dbo.InvoiceHeaderTbl.CompanyCode LIKE @CCode) AND(dbo.InvoiceHeaderTbl.SalesManCode LIKE @SCode) AND(dbo.InvoiceHeaderTbl.OrderType LIKE @OType)GROUP BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)HAVING (DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) > 3)ORDER BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)
I've got a stored procedure which contains a fast_forward cursor. I was wondering whether it is possible to pass in an sql query string into this stored procedure for the cursor, i.e:
Create Procedure (@sqlString as text) as DECLARE aCursor CURSOR FAST_FORWARD FOR @sqlString
This stored proceedure will not let me save is there any reason why if i take out the set line it says the syntax works if i leave this line in however it says that batchid is not a valid column name can you only return @@ variables? i just cant figure out what its problem is
I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,
ie say i have select projectid, project name from projects into temp from projects.
how can i then loop around all the rows in temp, to call my stored proceedure for each record?
in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.
can this be done for sql?
I am trying to do something like
for each record in #temp (projectid, project name) find the stored sprceedure value
so my end result will look like
projectid, project name, @storedproceedure return value lprojectid, project name, @storedproceedure return value projectid, project name, @storedproceedure return value projectid, project name, @storedproceedure return value
Hope you can give me some advise.I am wanting to build a databse driven website. I am using Access toconnect to an SQL 2000 server to create tables etc.I am using ASP/ASP.Net to build my site.The question I have is on best method to retrive data. Lets say I havea Table of Products, and one of those fileds is CurrentProduct and itsTrue or False. On my web page I want to retreive and list all productsthat are marked as CurrentProduct True.I could do this as part of the SQL statement in the web page ie"Select * From Products Where CurrentProduct=True", I could create aView that only shows Current Products and use Select * FromCurrentProductsView or I could create a stored prodceedure, that looksvery much like a view.I know a bit about Access and Queries which is why I am using accessto manage Sql, but very liitle if anything about Stored Proceedures.When should I use what? And what are the advantages / dis-advantagesof each approach?Many thanks for any help you are able to provide.
I have some data that I am inputing and if the record already exists i would like to add data to the fields that are not populated ONLY if they are not populated for example
Already in the table address= "123 this place" city= "" State="MI" Zip="48462" FirstName="" LastName=""
being added address= "123 this place" city= "Ontate" State="" Zip="48462" FirstName="Person" LastName="Guy"
once i find that this record already exists because of the address and zip (which i already have complete) I would like it to update the City first name and last name in the data that is already in the table. thank you for your help
Can someone please help me....I have created a DNN module that works on the test site but when I upload the module zip to a new site I get an error on creating my stored proceedure as follows:
Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'. Line 51: Incorrect syntax near '@Str_Title'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC
EndJob End Sql execution: 01.00.00.SqlDataProvider file
The SP looks like this:
/* ------------------------------------------------------------------------------------- / ListTAS_Journal / ------------------------------------------------------------------------------------- */ SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
IF ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = ''
SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))
ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
This SP works on the test site. Any help would be creatly apreciated
I have a table from which I need to create a report via MSRS2005, however the data in the table is awful in its construction and I was hoping to be able to use a stored proceedure to create a new table in which I can manupulate the data, but my T-SQL programming skills aren't that clever, so if anyone can offer any advice I'd be most grateful:
In the existing table there are two columns; StartDate and EndDate which is pretty self explanitory - what I would like to do is create a new table with only one date column and if there is more than one day between StartDate and EndDate I would like it to fill in every date in between.
For example, if the StartDate is 01/06/2007 and the EndDate 10/06/2007 I'd like the new table to list dates 01/06/2007 through 10/06/2007 inclusive in one column.
I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..
I have a stored proceedure that is adding a record to a database table. When the record is added using an insert statement, the ID field is autogenerated. I have a second insert statement that inserts into a second table, however, I want/need? to use that ID field in order to link this additional information to the proper record in the initial table. Is there an easy way to do a select or just pull the ID? I was thinking I could do a select before the final insert using 2 or 3 required fields of which used in a select altogether would be unique, but before I did that, I wanted to see if I was missing a better way. I posted the code below.... set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Add_Employee_Data] ( @LastName as varchar(100),@FirstName as varchar(100), @MiddleName as varchar(100),@Position as varchar(100), @Department as varchar(100), @DirectDial as varchar(100), @Ext as varchar(100), @Fax as varchar(100), @HomePhone as varchar(100), @CellPhone as varchar(100), @Partner as varchar(100), @TimeKeeper as varchar(100), @Notary as varchar(100), @Practice as varchar(100), @SummerAddress as varchar(250), @SummerPhone as varchar(100), @LegalNonLegal as varchar(100), @Bar as varchar(100), @OtherEmail as varchar(100), @HomeAddressComplete as varchar(100), @HomeAddress as varchar(100), @HomeCity as varchar(100), @HomeState as varchar(100), @HomeZip as varchar(100), @School as varchar(100), @Degree as varchar(100), @Status as varchar(100), @Floor as varchar(100)) AS DECLARE @Code as varchar(100)
INSERT Into tblMain2(LastName,FirstName,MI,Position,Dept,Extension,DirectDial,[FAX DID],HomePhone,CellularPhone,SpousePartner, TimeKeeper,Notary,PrGroup,SummerAddress,SummerPhone,LegalNonLegal,Bar,OtherEmail,HomeAddressComplete,HomeAddress,HomeCity, HomeState,HomeZip,Floor,Active) Values(@LastName,@FirstName,@MiddleName,@Position,@Department,@Ext,@DirectDial,@Fax,@HomePhone,@CellPhone,@Partner,@TimeKeeper, @Notary,@Practice,@SummerAddress,@SummerPhone,@LegalNonLegal,@Bar,@OtherEmail,@HomeAddressComplete,@HomeAddress, @HomeCity,@HomeState,@HomeZip,@Floor,@Status) <<<<< Put select statement here to pull in @Code where LastName = @LastName and Extension =@Ext ?? INSERT Into Education(Code,CollegeSchool,DegreeCert) Values(@Code,@School,@Degree)
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.
On my site I have a search engine which is used by potential customers to find out what courses we have on offer. Everytime something is written into the search engine the search criteria are logged in a table.
All the courses are held in a different table and I have enabled full text search on this table. Only two cloumns are searched for matches this is the course title and keywords.
What I want to be able to do is write a stored procedure which tells me which search criteria did not match any course titles or keywords. So that I can then assess what word people are using to search for courses and then add any which didn't match any courses to the keywords if they are relevant.
What I have done so far is create a view which contains a distinct list of search criteria. All i need to do now is create a stored procedure which loops through the courses table searching for each distinct search criteria.
I know this is possible to do using asp.net but I would like to do this on the database side to speed the process up a bit.
Any help on this matter would be much appreciated as I seem o be goinbg round and round in circles!!!
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?
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?
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.
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?
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
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
I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.
I have table "dbo.instances" with list of instances. I have databases from "sys.databases".
How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005 TABLES Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046"); con.Open(); cmdselect = new SqlCommand("createpost", con); cmdselect.CommandType = CommandType.StoredProcedure; cmdselect.Parameters.Add("@userID", userID); cmdselect.Parameters.Add("@categoryID", categoryID ); cmdselect.Parameters.Add("@title", title.Text ); cmdselect.Parameters.Add("@newsdate", newsdate.Text ); cmdselect.Parameters.Add("@story", story.Text ); cmdselect.Parameters.Add("@wordcount", "1" ); int valueinserted = cmdselect.ExecuteNonQuery(); Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted); con.Close();
as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?
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 Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak 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.
I want to loop through a recordset and do inserts into another table based on each record.
The way I have been doing it is copy my key data into a temp table, Loop through temp finding the max ID Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.
I know there has to be a better way. The table I am working with is millions of records. Thanks in advance, Chris Reeder
