the 1st uses the second sp. to fill a parameter.
the 2nd has one output parameter defined.
When I run the 2nd sp. on its own in MS SQL Server Manangement studio, there is no resultset shown.
BUT when the 2nd sp. is executed inside the 1st sp., I get a '3' in the resultset everytime the 2nd sp. executes. What is causing this?????
This is the way I execute the 2nd sp. inside the 1st sp.:
select 'TEST1'
execute dbo.uspMonthlyHeadCount @soe, @eoe, @Months, @count output
select 'TEST2'
Results are like this:
TEST1
3
TEST2
... (it's displayed more times because the sp. is inside a while)
Why is this happening and what can I do about it? (I'll provide more info if needed!)
Hi,I would like to write a stored procedure that returns a resultset.e.g. select student_id, student_subject, subject_marksfrom student_resultswhere student_class_no = ?The input parameter is student_class_no (see ? as per above SQL).The output is a resultset, with each result consisting of student_id,student_subject, subject_marks.Can anyone advise how this is done in sql server stored procedure?Thanks,June Moore.
I am try to execute a stored procedure via HTML using an XML template. The last statement in the procedure is a select stmt with FOR XML specified which will provide the output back to the calling program. The problem is, the stored procedure I am calling in turn calls other procedures, which may also provide result sets. So, I get this error message back <?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="Streaming not supported over multiple column result" ?>
I would really like a way to suppress all the resultsets produced during the execution of the procedure except the last one. Is there a way to do that within a procedure?
insert into #T1 execute ('execute ' + @SQLString )
select * from #T1 --------------------------------------------
The problem is that I want to call different procedures that can give back different columns. Therefor I would have to define the table #T1 generically.But I don't know how. Can anyone help me on this problem?
I have a sql server resultset that returns productid numbers. I would like to loop thru this resultset and run a stored procedure using the productid as the input parameter. Any samples would be helpful. thanks
SELECTa.Field2, a.Field3, b.Field3 FROM Tbl1 a INNER JOIN Tbl2 b ON a.Field1 = b.Field1 WHERE(a.[Name] LIKE '%@_name%') AND (a.ID BETWEEN @cPos AND @nPos) AND (a.Price BETWEEN @LPrice AND @HPrice) GO
if i remove @_name and put in '%%' it returns rows...but with @_name it doesnt work...any idea why?
check_syntax doesnt return any errors...so i dont kno waht to do
I have a stored procedure I'm using in a c# ASP.NET application. The SP is tied to an SQL DataSource which is, in turn, tied to a gridview control that displays the results obtained from the SP. The SP receives two parameters that are obtained from two dropdown list boxes. ALTER PROCEDURE developers.fort_GetSearchResults ( @propertyType int, @county int ) AS IF ((@propertyType = 1) AND (@county = 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id ORDER BY NEWID() ELSE IF ((@propertyType = 1) AND (@county != 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id WHERE (Experimental_Accommodation.propertyType = @propertyType) ORDER BY NEWID() ELSE IF ((@county = 1) AND (@propertyType != 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id WHERE (Experimental_ContactDetails.county = @county) ORDER BY NEWID() ELSE IF ((@propertyType != 1) AND (@county != 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id WHERE (Experimental_Accommodation.propertyType = @propertyType) AND (Experimental_ContactDetails.county = @county) ORDER BY NEWID() RETURN
Unfortunately only the first and fourth IF statement work. The second and third do not return any values and I'm unable to work out why.
I have a stored procedure I'm using in a c# ASP.NET application. The SP is tied to an SQL DataSource which is, in turn, tied to a gridview control that displays the results obtained from the SP. The SP receives two parameters that are obtained from two dropdown list boxes
Code:
ALTER PROCEDURE developers.fort_GetSearchResults ( @propertyType int, @county int ) AS IF ((@propertyType = 1) AND (@county = 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id ORDER BY NEWID() ELSE IF ((@propertyType = 1) AND (@county != 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id WHERE (Experimental_Accommodation.propertyType = @propertyType) ORDER BY NEWID() ELSE IF ((@county = 1) AND (@propertyType != 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id WHERE (Experimental_ContactDetails.county = @county) ORDER BY NEWID() ELSE IF ((@propertyType != 1) AND (@county != 1)) SELECT Experimental_Accommodation.id, Experimental_ContactDetails.name, Experimental_ContactDetails.address, Experimental_ContactDetails.district, Experimental_ContactDetails.town, Experimental_Counties.County, Experimental_ContactDetails.postcode, Experimental_ContactDetails.telephone, Experimental_ContactDetails.fax, Experimental_ContactDetails.email, Experimental_ContactDetails.contactName, Experimental_Accommodation.gradingDescription, Experimental_Accommodation.sleeps, Experimental_Accommodation.pricing, Experimental_Accommodation.shortDescription, Experimental_Accommodation.description, Experimental_Property_Type.name AS Expr1, Experimental_Grading.grading, Experimental_Images.filename, Experimental_Images.alt FROM Experimental_Accommodation INNER JOIN Experimental_ContactDetails ON Experimental_Accommodation.contactDetails = Experimental_ContactDetails.id INNER JOIN Experimental_Counties ON Experimental_ContactDetails.county = Experimental_Counties.id INNER JOIN Experimental_Property_Type ON Experimental_Accommodation.propertyType = Experimental_Property_Type.id INNER JOIN Experimental_Grading ON Experimental_Accommodation.grading = Experimental_Grading.id INNER JOIN Experimental_Images ON Experimental_Grading.iconId = Experimental_Images.id WHERE (Experimental_Accommodation.propertyType = @propertyType) AND (Experimental_ContactDetails.county = @county) ORDER BY NEWID() RETURN
Unfortunately only the first and fourth IF statements return a value. The second and third IF statements return nothing and I have no idea why.
I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system. I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible? Thanks, Kevin
I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 hasabout 50 input parameters. PROC2 is the main procedure that does somedata modifications and afterwards calls PROC1 using an EXECUTEstatement.The input parameter values for PROC1 are stored in a table in mydatabase. What I like to do is passing those values to PROC1 using aSELECT statement. Currently, all 50 parameters are read and stored ina variable, and afterwards they are passed to PROC1 using:EXEC spPROC1 @var1, @var2, @var3, ... , @var50Since it is a lot of code declaring and assigning 50 variables, I waswondering if there is a possibility to run a statement like:EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)Any help on this is greatly appreciated!
In SQL 2005 I have a stored procedure as below:@sub_no smallint OUTPUTBEGINBEGIN TRANSACTIONINSERT...INTOSET @user_no = (SELECT ...... FROM ....WHERE sub_no = @sub_no)INSERT...INTOEXE another_stored_procedure (it includes also BEGIN...COMMIT)EXE another_stored_procedure (it includes also BEGIN...COMMIT)SET @sub_no = .......COMMIT TRANSACTIONWhen Visual Studio (ASP.NET 2005) is open and I run the program,procedure is executed once without any problem. If I publish theproject and put files on another server (or even use the publishedfiles from my machine) I have an error because stored procedure isexecuted twice. @sub_no is used as input/output parameter.I followed/trace the steps in procedure and it seems that procedure isexecuted once with correct value of @sub_no. The second time procedureis executed, the value that it was assigned before COMMIT is used,which gives an error because the INSERT values have NULL values.In ASP.NET I call the store procedure once.What could be the reason ?Thanks a lot for any help.
I'm working on building a report and asked a developer which table some data comes from in an application. His answer was the name of a 3500 line stored procedure that returns 2 result sets. I could accomplish what I'm trying to do using the second result set, but I'm not sure how to put that into a temporary table so that I could use it.
Here's my plan according to the Kübler-Ross software development lifecycle:
Denial - Ask the developer to make sure this is correct (done) Despair - Look hopelessly for a solution (where I am now) Anger - Chastise developer Bargaining - See if I can get him to at least swap the order that the resultsets are returned Acceptance - Tell the users that this can't be done at present.
I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am using
Hello all, I want to be able to be able to return a resultset from a Stored Procedure.
Something like :
CREATE PROCEDURE LSNOnAJob @MyJobNo AS INT,@MyLsn VarChar(10) OUTPUT
AS
SELECT @MyLsn = dbo.TSample.ISmpShortCode FROM dbo.TJob INNER JOIN dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN WHERE (dbo.TJob.IJobN = @MyJobNo) GO
I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.
I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.
This is a problem for that I lost a weekend to reach to this conclusions:
Here is the scenario: I need a file downloaded from a FTP server on my SQL 2005 DB Server to process it in several ways. This file is tens of MBs (let's say 50MB). I set up a CLR stored procedure to bring it locally on the machine.
Inside this procedure, call it getFtpFile, I use the FtpWebRequest to issue the commands I need to the FTP server (get the file size and download the file). For reading and writing I use a StreamReader sr and a StreamWriter. sw
I read the remote file in block with: sr.ReadBlock(buf, 0, blockSizeBytes);
Everything work file until the last block that, most probably will not be of size blockSizeBytes, but smaller. So, what happens is that when I read this last block there is an exception raised from the call saying: The underlying connection was closed: An unexpected error occurred on a receive.
This happens only if: -> the code runs inside SQL (calling the SP) (it runs perfect on normal tet environment where I have another executable and call the getFtpFile method in the CLR dll); -> 100 seconds (100000 miliseconds) (exactly) have passed (small files do not experiencing this problem)
I thought the reader writer scheme is not perfect and implemented again with a simple FileStream.WriteByte(response.GetResponseStream().Read()); This approach, even if very slow, still has the problem after 100 seconds, in a CLR SQL environment, when it reads the at the last character.
I have used all the combinations of KeepAlive and response.Closose(). Problems still there.
Conclusion: There must be a 100 seconds timeout that immediately after my last byte has been received, closes the connection and the stream of the response crashes during this last call.
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
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
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 have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END. Any help on this would be appreciated.
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames
IF (@i_WildCardFlag=0)BEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (Batch.BatchID = @V_BatchID )) AND ((@V_UserID IS NULL) OR (Batch.Created_By = @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END ELSEBEGIN SET @SQLString='SELECT Batch.BatchID, Batch.Created_By, Batch.RequestSuccessfulRecord_Count, Batch.ResponseFailedRecord_Count, Batch.RequestTotalRecord_Count, Batch.Request_Filename, Batch.Response_Filename, Batch.LastUpdated_By, Batch.LastUpdated, Batch.Submitted_By, Batch.Submitted_On, Batch.CheckedOut_By, Batch.Checked_Out_Status, Batch.Batch_Description, Batch.Status_Code, Batch.Created_On, Batch.Source, Batch.Archived_Status, Batch.Archived_By, Batch.Archived_On, Batch.Processing_Mode, Batch.Batch_TemplateID, Batch.WindowID,Batch.WindowDetails, BatchTemplate.Batch_Type, BatchTemplate.Batch_SubType FROM Batch INNER JOIN BatchTemplate ON Batch.Batch_TemplateID = BatchTemplate.Batch_TemplateID WHERE ((@V_BatchID IS NULL) OR (isnull (Batch.BatchID, '''') LIKE @SSS )) AND ((@V_UserID IS NULL) OR (isnull (Batch.Created_By , '''') LIKE @V_UserID )) AND ((Batch.Created_On >= @V_FromDateTime ) AND (Batch.Created_On <= @V_ToDateTime )) AND Batch.Archived_Status = 1 ' if (@V_BatchStatus IS not null) begin set @SQLString=@SQLString + ' AND (Batch.Status_Code in ('+@V_BatchStatus+'))' end if (@V_BatchType IS not null) begin set @SQLString=@SQLString + ' AND (BatchTemplate.Batch_Type in ('+@V_BatchType+'))' end END PRINT @SQLString SET @ParmDefinition = N' @V_BatchStatus Varchar(30), @V_BatchType VARCHAR(50), @V_BatchID NUMERIC(9), @V_UserID CHAR(8), @V_FromDateTime DATETIME , @V_ToDateTime DATETIME, @SSS varchar(500)' EXECUTE sp_executesql @SQLString, @ParmDefinition, @V_BatchStatus , @V_BatchType , @V_BatchID, @V_UserID , @V_FromDateTime , @V_ToDateTime , @SSS GO SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
The above stored procedure is related to a search screen where in User is able to search from a variety of fields that include userID (corresponding column Batch.Created_By) and batchID (corresponding column Batch.BatchID). The column UserID is a varchar whereas batchID is a numeric. REQUIREMENT: The stored procedure should cater to a typical search where any of the fields can be entered. meanwhile it also should be able to do a partial search on BatchID and UserID.
