I am trying to create a stored procedure to be called from an ASP.Net page.
There are two sets of stats that I want to be able to pull off for a given ID - firstly a list of all the names given and the number of times that name has been given and secondly a list of all the reasons given and the number of times for each reason.
With the queries there is clearly different Group By required to get the necessary stats off.
I dont want to have to make two round trips to the server to get the two different results but cannot see how to otherwise to get the results out and into ASP.Net to consume?
Hi ya'll! First off, please let me know if I'm totally doing the wrong thing here ...
I have set up two different simple queries in one stored procedure that I'd like to reference from one fetch from my ASP.Net/VB.Net web template. Can I do this? I think I can, 'cause most of the data is showing up.
Code follows: [stored procedure]
CREATE PROCEDURE dbo.sp_admin_vStatistics AS BEGIN
SELECT COUNT(metric_ID) AS device_totalRequests, MAX(request_due_date) AS device_maxRequest_due_date, MIN(request_due_date) AS device_minRequest_due_date, MAX(metric_log_dtTime) AS device_maxLog_dtTime, FROM dbo.metrics
SELECT COUNT(user_ID) AS user_total, MAX(user_lastlogin_dtTime) AS user_LastLogin FROM dbo.users
END GO
[snippet of .Net page where I'm actually trying to display the output]
'Loop over our query results & print it Do While (dr.Read()) lblUser_Total.Text = CType(dr("user_total"), Integer) lblUser_LastLogin.Text = CType(dr("user_LastLogin"), Date) lblDevice_TotalRequests.Text = CType(dr("device_totalRequests"), Integer) lblDevice_minRequest_due_date.Text = CType(dr("device_minRequest_due_date"), Date) lblDevice_maxRequest_due_date.Text = CType(dr("device_maxRequest_due_date"), Date) lblDevice_minLog_dtTime.Text = CType(dr("device_minLog_dtTime"), Date)
Loop
My output shows nothing for user_total and user_lastlogin, but the other "device" information gets properly displayed. Running SQL Query Analyzer shows it all. I just think I'm referencing it incorrectly.
Hi all,Sorry for HTML, there is a lot of code & comments I tried to create a stored procedure from 3 queries .. to reduce # of times DB gets access from 1 asp page. The result procedure only works 1/2 way (does return the rest of the SELECT statement) :( Please help me figure out what stops it mid way? I need it to return all the results from the SELECT statements AND the number of rows (ScriptsNo) from the count(*): Here is my stored procedure:CREATE PROCEDURE csp_AuthorAccountInfo@CandidateID int,AS DECLARE @ScriptsNo int, @ManuscriptID int SELECT count(*) as ScriptsNo FROM Manuscripts WITH (NOLOCK) WHERE CandidateID = @CandidateID/* this is where it stops all the time :(Theoretically speaking, next SELECT will only return 1 row with Candidate's info*/SELECT c.*, l.LocationID, @ManuscriptID=m.ManuscriptID, l.State, cn.Country FROM Candidates c INNER JOINManuscripts m ONc.CandidateID = m.CandidateID INNER JOINLocations l ON c.LocationID = l.LocationID INNER JOINcn ON l.CountryCode = cn.CountryCodeWHERE c.CandidateID = @CandidateID/* next SELECT should normally return manu rows with Candidate's submitted manuscripts */SELECT m.ManuscriptID, m.IsReceived, msn.StageName, ms.DatePosted, ns.CommentsFROM Manuscripts m INNER JOINManuscriptStages ms ON m.ManuscriptID = ms.ManuscriptID INNER JOINManuscriptStageNames msn ON ms.StageNameID = msn.StageNameIDWHERE m.ManuscriptID = @ManuscriptIDORDER BY ms.DatePosted DESCGO
Hi, I currently have three queries running seperately which I'd like to join up..
However I'm sure it can be done..
Here goes:
I have one table which lists payments made (it stores a PaymentID from another table, a payment amount and an invoiceID)
Therefore there can be several records with the same PaymentID referenceing different invoices (i.e a user paying off several invoices with one payment)
I have one query which lists all of the invoices paid against one payment.
SELECT PaymentID, InvoiceID, PaymentAmount WHERE PaymentID = xxx AND PayType <> 'Credit'
I then have a second query which is ran against each individual invoice which shows the other payments which have been made against this invoice already
SELECT PaymentID, InvoiceID, PaymentAmount WHERE PaymentID <> xxx AND PayType <> 'Credit' AND InvoiceID = XXX
and final I have one query which lists the credits SELECT PaymentID, InvoiceID, PaymentAmount WHERE PayType = 'Credit' AND InvoiceID = XXX
All of the above lets me see an payment, which invoices have been paid against that payment.. and then for each invoice, any other payment which were made beforehand, and finally any credits against that invoice.
I run these from an ASP page in a loop which is pretty inefficient way of doing it.
I would much prefer to amalgamate the three queries above so I could see what I was paying now, what had already been paid and what was credited against each invoice from a PaymentID.. all in query.
when I try and insert something like sp_PRO '123154', 'it's good', '23.23', 1
I can't insert "'" and "," because that is specific to how each item is delimited inorder to insert into the stored procedure. But if I hard code this into a aspx page and don't create a stored procedure I can insert "'" and ",". I have a scenario where I have to use a stored procedure...confused.
If I were to create a stored procedure that searches a table using (optional) multiple parameters, what would be the best way to do the search. I want to try and avoid using several "IF" statements (like IF @FirstName IS NOT NULL, etc). How would I do it, or would I just be better off using several "IF" statements? Thanks... CREATE PROCEDURE intranet_search_GetEmployeesBySearch ( @FirstName NVarChar(100), @LastName NVarChar(100), @Phone NVarChar(50), @Cell NVarChar(100), @Pager NVarChar(100), @Ext NVarChar(50), @Email NVarChar(100), @Department NVarChar(200), @Position NVarChar(100), @IsManager Bit ) AS BEGIN
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.
They tend to look like this:
create procedure UberProc (@QueryId varchar(50)) as
if @QueryId = 'First Horrible Idea' begin select stuff from something end if @queryid = 'Second really bad idea' begin select otherstuff from somethingelse end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately 2) They are harder to debug 3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
I have 4 sets of select queries under 1 stored proc, now on the report calling the stored proc via dataset. when i run the dataset the only first set of the select query related fields appearing under the dataset.
But on the back end sql server, if i execute the same stored proc, i get 4 resultsets in one single executioln.
i am not seeing the remaingin 3 resultsets, on the reports dataset.
Is it possible on the reports or not.
In the asp.net project i was able to use that kind of stored procedures whcih has multiple select statements in 1 procedure., i use to refer 0,1,2,3 tables under a dataset.
I need to create a stored procedure that will have about 10-15 queries and take 3 parameters. the variables will be: @lastmonth, @curryear and @id @lastmonth should inherit Session variable intlastmonth @curryear should inherit Session variable intCurrYear @id should inherit Session id One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "' AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id") The rest of the queries will be similar and use all 3 variables as well. How can I go about this and how will queries be seperated.
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 am having one querry regarding the same line. In my stored procedure i am fetching the data from one table containing upto 5 to 6 million rows I made use of index in my database but then also I cant optimise my execution time of that sp. Please help me out of this problem.
My task is to bind and show 3 different values coming from three different queries into three different columns of GridView. I had done this as mention in below. Program was successful. But I want to excute these three queries in same Stored Procedure. I can do that and stored in seperated variables. I need help how to call these three different values in data adapters and store each value in three different columns of grid view.
Simply I want to below statement in stored procedures and call from program. Can any one help me plz.
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da.Fill(ds,"Dep");
SqlDataAdapter da1 = new SqlDataAdapter("select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID", con); da1.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da1.Fill(ds,"Exp");
SqlDataAdapter da2 = new SqlDataAdapter("select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID", con); da2.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = Convert.ToInt32(Session["PA_UID"]); da2.Fill(ds,"Inc");
// This is another way I had tried. But failed. I am getting Index out of bound error. Can any solve this if possible to u. SqlCommand cmd = new SqlCommand("select isnull(sum(PA_DAmt),0) from PA_Deposits where PA_UID = @PA_UID", con); cmd.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; GridView1.Rows[0].Cells[1].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_EAmt),0) from PA_Expenses where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[2].Text = cmd.ExecuteScalar().ToString();
cmd.CommandText = "select isnull(sum(PA_IAmt),0) from PA_Income where PA_UID = @PA_UID"; GridView1.Rows[0].Cells[3].Text = cmd.ExecuteScalar().ToString();
ASPX Code for Grid View <h2>Account Summary</h2><br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" emptydatatext="There are no data records to display." Width="238px" > <Columns> <asp:TemplateField >
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?
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.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
Hi, I have a quick question about pushing things onto the database to do. It is supposed to be more efficient, and it is more secure. The downside as far as I can tell is that one can end up with incredibly expensive and complex database deployment using costly servers and software. Is there an approach that minimizes database query time by moving much of the processing to the (less efficient) web server calling the database, and then only using simply queries. One would then process the data in code, stripping it down to exactly what is needed, rather than doing complex things on the db itself. An example of this might be that you want to parse a field on a particular char. and return only the first part of the string, up until this char occurs. This can be done on the db server, but... I understand that one might return extra information, but it seems there is a large potential payoff in less complexity and expense. Anyway, if you know of such an approach and the name of it, I would appreciate hearing it, Thanks
Hello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE
In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.
Could you help me please, because my code isn't working:
CREATE PROCEDURE dbo.stp_Test ( @tablename AS VARCHAR(50) )
DECLARE c1 CURSOR FOR SELECT column_name FROM information_schema.columns WHERE table_name = @tablename OPEN c1 FETCH NEXT FROM c1 INTO @columnname WHILE @@fetch_status = 0 BEGIN IF (@strsql is null) BEGIN SET @strsql=@columnname END ELSE BEGIN SET @strsql = @strsql + ',' + @columnname END
FETCH NEXT FROM c1 INTO @columnname END CLOSE c1 DEALLOCATE c1
We have our DWH built in SQL Server 2005. We are doing reporting against the DWH and most of query logic is complex. For this purpose we have proposed to write SPs to encapsulate the logic and which in turn can be called from the Report Data Tab. But our IT Architect suggest us to have inline queries instead of SPs as that will reduce the overhead of going to the DBAs everytime the logic changes. As I am new to DWH+Reporting, I was really pondering over which way to go. Can anyone suggest a way out on this. Thanks, S Suresh
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
I am able to step into a sql stored proc through the VS IDE. I am able to look at some values in the quick watch window. But I need to examine the results of certain queries. Like a select statement that gets placed into a temp table. How can I view the results of the query? If their is not an elegant way of doing this in the debugger, then should I put sql statements in the stored proc, that would display the results of the query?(Assuming I would remove them after debugging) If the answer is yes, then maybe someone can give me some ideas. Like how would I print to the screen the results of the query, or be able to view the results of the query? My sql skills are a bit weak, so I would appreciate any help I can get. Ralph Goodwin
Help! I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.
I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.
How can I create functions to be used in my query?
We have just implemented our new app and I need to improve performance. We are the victims of not having a very adequate stress testing tool prior to launch. Is there an easy way to track all queries or stored procs longer than a specified time?
We have just implemented our new app and are expericing some slowness but no blocking issues. Is there an easy way to track all queries taking over x seconds to run?
I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
