Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w ten sposob co napisalem kilka postow wyzej (select ID as UID from aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
hi need help do this i have table that generate Serial i get the code from this link http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2734123&SiteID=1
and i must to get the same sn + date so whan i do this
Code Snippet select * from tbl_1a WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009 select * from tbl2_2B WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009
i get the current state DATE + SN
i need to insert into other 2 table this Serial+date like this --need help fix this code
Code Snippet DECLARE @sn DECLARE @date set @date =Getdate() -- insert 1 new records tb sn INSERT INTO ConfirmationNumber([ConfirmationDate]) SELECT GETDATE() ---select last value sn set @sn = SELECT top 1 SequenceNumber FROM vw_ConfirmationNumber ---- get the last sn order by SequenceNumber desc --insert into tb1 insert into tbl_1a (fld1,fld2,sn,date) select from tbl_2a fld1,fld2,@sn,@date --insert into tb2 insert into tbl2_2B (f1,f2,date) select from tbl2_1B f1,f2,@sn,@date
I have a list of articles stored in my sqlserver 2000 db. To get all the articles I have a stored procedure which just contains a simple select statement. When a user click to download an article, I want to increase the download count. I can do this with a separate stored procedure with an insert statement and passing in the document id, but I'm sure I could do it within one stored procedure, just not sure how. This is my current sp for getting the articles.CREATE PROCEDURE [sp_GetArticles] ASSELECT [DocumentID], [Title], [Description], [Downloads], [UploadDate], [Filesize] FROM Documents WHERE CategoryID = 1
In a stored procedure, I've created a temp table, and am trying to populate it using: insert #myTable exec p_someProc
p_someProc returns 2 record sets, the first is the one I want to stuff into #myTable, the second is a single field. I think that is the reason that I'm getting: "
Insert Error: Column name or number of supplied values does not match table definition.
"
#myTable has the same structure as the first record set. Is that second recordset causing the error, if so, how to I either incorporate it into the temp table or just ignore it?
1.First i need to update the row if the status column is 0 to 1 2.Need to insert the row IF SegmentId=@SegmentId and SubjectId<>@SubjectId and StaffId=@StaffId 3.Need to insert the row IF StaffId<>@StaffId And ClassId=@ClassId and SegmentId<>@SegmentId and SubjectId<>@SubjectId
I have wrote the stored procedure to do this. But the problem is If do the update. It is reflecting in the database by changing 0 to 1. But it shows error like cannot insert the duplicate
Here is the stored Procedure what i have wrote
ALTER PROCEDURE [dbo].[InsertAssignTeacherToSubjects]
We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.No Begin and End Transaction used in the stored procedure.
Select columnname from tablename order by ordercolumn
We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.
The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:
Select @userCount = COUNT(*) from permissions where UserID = @userID and (projectid = @projectID or projectid=0) and clientid = @clientID and folderpermissions in (dbo.sp_FoldersOfFile(@fileID))
The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.
Hi All, As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery) that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery ( @select_query nvarchar(500) ) as begin
exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out
I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.
I would like to only import data since the last import so I was thinking of my logic to be like this:
INSERT INTO Table_2 (Field1, Field2, ... , FieldN) VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))
Does this make sense? Or do you all suggest a different mannger of accomplishing this?
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
USE [International_Forecast_New] GO /****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this Period Quarter Year Period Start Period End 2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?
SELECT o.name, o.object_id, ps.last_execution_time , ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds, ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds, ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example: SELECT 'Asset', 'House', TotalPrice * -1 FROM Accounts WHERE AvgAmount > 0 UNION SELECT 'Balance', 'Cover', TotalPrice FROM Accounts WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
Hi,I need to write a stored procedure to check if the string = "web_version" exists in another string called FromCode.The stored procedure accepts 2 parameters like this:Create proc [dbo].[spGetPeerFromCode]( @FromCode VARCHAR(50)) (@WebVersionString VARCHAR(50))as please assist.
Hi all, I want to write a stored procedure for the following, The table have the following field, CategoryID, CategoryName, Parent_categoryID 123 Kids Dresses 0 321 Kids Boys 123 322 Kids Baby 123 401 Kids Boys school Uniform 321 501 Kids Boys Formals 321 541 Kids Baby school Uniform 322 542 Kids Baby Formals 322 601 Household Textile 0 602 Bathrobe 601 603 Carpet 601 604 Table Cloth 601
From the above example the categoryID acts as a parent_categoryID for some products, when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID, subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID. For example when i pass categoryID as 123 it should return the following subcategoryIDs 321,322,401,501,541,542 because these are all subcategory of categoryID 123. How to write stored procedure for this?
Hai Friends, I heard that stored procedures can be written using two servers how to write stored procedures by using two servers CAN ANYONE GIVE INFORMATION ABT THIS
How to write stored procedure with two parametershow to check those variables containing values or empty in SP if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one Select * from orders plz write this SP for me thanks
I want to write a stored procedure for the following, The table have the following field, CategoryID, CategoryName, Parent_categoryID 123 Kids Dresses 0 321 Kids Boys 123 322 Kids Baby 123 401 Kids Boys school Uniform 321 501 Kids Boys Formals 321 541 Kids Baby school Uniform 322 542 Kids Baby Formals 322 601 Household Textile 0 602 Bathrobe 601 603 Carpet 601 604 Table Cloth 601
From the above example the categoryID acts as a parent_categoryID for some products, when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID, subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID. For example when i pass categoryID as 123 it should return the following subcategoryIDs 321,322,401,501,541,542 because these are all subcategory of categoryID 123. How to write stored procedure for this?
Hai friends, The project i'm working on is an asp.net project with SQL Server 2000 as the database tool. I've a listbox (multiline selection) in my form, whose selected values will be concatenated and sent to the server as comma separated numbers (Fro ex: 34,67,23,60).Also, the column in the table at the back end contains comma separated numbers (For ex: 1,3,7,34,23). What i need to do is - 1. Select the rows in the table where the latter example has atleast one value of the former example (In the above ex: 34 and 23). 2. Check the text value of these numbers in another table (master table) and populate the text value of these numbers in a comma separated format in a grid in the front end. I've programmed a procedure for this. but it takes more than 2 minutes to return the result. Also the table has over 20,000 rows and performance should be kept in mind. Suggessions on using the front-end (asp.net 2.0) concepts would also be a good help. Anybody's helping thought would be greatly appreciated... Thanks lot...
I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?
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)
'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.
hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP .. let me this post ..................anybody ??????????
Could you write a special stored procedure for me in SQL 2005? When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= SportTagName= NewTagName= Health I hope there is a stored procedure which can join all TagName into a single string and return,it means when I pass @IDTopic to the stored procedure, it return "Sport,New,Health" How can write the stored procedure?
I have a sp which saves the necessary information regarding the status of action(whether success or failure, rows affected etc) to a log table say( StatusLog )After this, I was sending a database mail with information taken from the log table via sp_send_dbmail. Now I would like to write the status information to a 'txt' file instead of sending via mail.How can I write to a text file from a stored procedure in ms sql server 2005?
Can you, and if yes, how do you, write an xml file using a stored procedure. For example the sp below writes this new record to a sql table. How would I change it to write it to an xml file ?
I need to write a clr stored prodeure.i have to call that stored procedure from my prediction query.My Application is going to make use of that query to get the prediction output.
Is it possible to write clr stored procedure with out using adomd server.How?
For example:
In My Assembly i am having a function PredictPerformance(),Which is having my DMX Query.I need to execute that function from my analysis service by calling like this,
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= http://www.hothelpdesk.com TagName= http://www.hellocw.com TagName= http://www.supercoolbookmark.com
I hope there is a stored procedure which can join all TagName into a single string and return, it means when I pass @IDTopic to the stored procedure, it return "http://www.hothelpdesk.com, http://www.hellocw.com, http://www.supercoolbookmark.com"
