Multiple SQL Statements In One SQLCommand
Oct 3, 2005I am just wondering if it is possible using SQL Server 2000 to have multiple SQL Statements executed with one sqlComm.ExecuteNonQuery(); call?
View 1 Replies
ADVERTISEMENT
The Best Overloaded Method Match For 'System.Data.SqlClient.SqlCommand.SqlCommand Error
Sep 21, 2006Hi,I'm new to ASP.NET, and am currently looking into XML.I'm trying to write XML using data from an SQL Server 2000 table. But I seem to be getting the following error regarding the SQL Server connection:Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid argumentsSource Error:Line 23: {
Line 24: SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
Line 25: mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
Line 26: mySqlDataAdapter.Fill(myDataSet);
Line 27: return myDataSet;Source File: c:InetpubwwwrootmappingcreateGeoRSSFile.aspx.cs Line: 25 This is my code:using System;
using System.Data;
using System.Data.SqlClient ;
using System.Configuration;
using System.Collections;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
public partial class createGeoRSSFile : System.Web.UI.Page
{
protected void Page_Load(object sender, DataSet myDataSet, EventArgs e)
{
string connString = "server=SQLSERV1;database=Historical_Statistics;UID=dbuser;PWD=Password";
string queryString = "SELECT Town, PostCode, Latitude, Longitude FROM UKPostCodes";
using (SqlConnection mySqlConnection = new SqlConnection(connString))
{
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = new SqlCommand(queryString, connString);
mySqlDataAdapter.Fill(myDataSet);
return myDataSet;
}
// Create a new XmlTextWriter instance
XmlTextWriter writer = new XmlTextWriter(Response.OutputStream, Encoding.Unicode);
// Start writing!
writer.WriteStartDocument();
writer.WriteStartElement("item");
// Creating the <town> element
writer.WriteStartElement("town");
writer.WriteElementString("PostCode",myDataSet .Tables[1].Columns("PostCode"));
writer.WriteElementString("geo:lat",myDataSet.Tables[1].Columns("Latitude"));
writer.WriteElementString("geo:lon", myDataSet.Tables[1].Columns("Longitude"));
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndDocument();
writer.Flush();
writer.Close();
}
}What seems to be causing this error?Thanks.
Multiple OCmd.ExecuteNonQueries - The SqlCommand Is Currently Busy Open
Jun 9, 2004Hi there,
Just starting out so bear with me thanks. I am developing a ASP.NET web application with supporting ASP.NET Web services. The app sends a webmethod a bunch of values and the web service takes those values and through several stored procedures, updates a SQL database.
I initialise the DB connection right at the top of the web method following the method attributes :
....
ByVal strState As String, _
ByRef nominalExists As Boolean _
) As String
Dim oConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("DBconnString"))
Dim oCmd As SqlCommand = New SqlCommand
Dim workParam As SqlParameter
Dim myTrans As SqlTransaction
Dim isError As Boolean
Dim returnURL As String
....
The function continues into a SELECT CASE routine...
Right... So when the desired CASE fires I enter into the following type of code. Purpose being to fire off several different Stored procedures under the same transaction and the same database connection.
Dim oCheckDBReader As SqlDataReader
Try
oConn.Open()
oCmd.Connection = oConn
oCmd.CommandType = CommandType.Text
oCmd.CommandText = "SOME SELECT STATEMENT"
oCheckDBReader = oCmd.ExecuteReader
If oCheckDBReader.HasRows Then nominalExists = True
oCmd.Connection = Nothing
oCheckDBReader.Close()
oCmd.Dispose()
myTrans = oConn.BeginTransaction()
oCmd.Connection = oConn
oCmd.Transaction = myTrans
oCmd.CommandText = "sp_mystoredprocedure"
oCmd.CommandType = CommandType.StoredProcedure
... oCmd parameter stuff
oCmd.ExecuteNonQuery
oCmd.Dispose()
oCmd = New SQLCommand
oCmd.Connection = oConn
oCmd.Transaction = myTrans
oCmd.CommandText = "sp_mystoredprocedure"
oCmd.CommandType = CommandType.StoredProcedure
... oCmd parameter stuff
oCmd.ExecuteNonQuery
oCmd.Dispose()
....
Catch
myTrans.Rollback()
....
Finally
oCmd.Dispose()
oConn.Dispose()
....
End Try
Can someone please spare some time to tell me:
a) Whether I am going about all of this the right way
b) Explain why I am getting the following error
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: The SqlCommand is currently busy Open, Fetching. at System.Data.SqlClient.SqlCommand.set_Transaction(SqlTransaction value) at System.Data.SqlClient.SqlTransaction.GetServerTransactionLevel() at System.Data.SqlClient.SqlTransaction.Rollback() at
Thanks a million to all that assist...
Hope to hear from you soon....
Dynamic Select Statement Using Parameterized SqlCommand Or Multiple Possible .CommandTexts
Jul 30, 2007I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = “**************string was correct****************� cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser
View 4 Replies View RelatedPassing An SQLcommand To A Asp.net Web Service As Sqlcommand
Feb 10, 2008Hi
Is it possible To pass an SQL command to a ASp.net web service as system.data.SQLclient.sqlcommand?
That means is ispossible to pass the actuall sql command instead of just the string?
If yes how can you do that??
Cheers
How To Do Multiple If Statements
Oct 11, 2015I want the below sql code to force a error if it falls on a saturday or sunday, and there are no rows in the OrderTrans table for the previous day (TransDate) but not sure how to do multiple if statements.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderTrans](
[OrderId] [int] NOT NULL,
[Code] ....
Using Transactions With Multiple SQL Statements
Oct 31, 2006I am writing a fairly complex data automation procedure that basically uses about 5 stored procedures and some additional sql from C# to populate a database. i would like to utilize transactions in C# and sql to help me if something goes wrong. My question is, do i have to maintain the same connection in order to have one transaction for all my Commands. right now i do things like connection.Open();Command.ExecuteNonQuery(); connection.Close(); connection.Open();Command2.ExecuteNonQuery(); connection.Close(); i think i have to keep the same connection open for the duration of all my commands in order to utilize transactions, is this correct? thanks, mcm
View 5 Replies View RelatedHow To Execute Multiple Statements?
Sep 17, 2007Is there a way to execute multiple SQL statements within SQL Server? I'm triyng to write a sql statement that will delete from three tables. I need at least two sql statements to delete from these three tables. Can this be done?
View 12 Replies View RelatedUSING MULTIPLE SELECT STATEMENTS
Apr 23, 2008how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
Code2.....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
thanks in advance
Multiple Select Statements
Dec 22, 2005Hi guys and gals,
I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:
'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
My Code is below:
//Start of sql
CREATE PROCEDURE ADMIN_GetSingleUsers( @userID int) AS
DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE, Cast(Users.Active as varchar(50)) as Active, Cast(Users.Approved as varchar(50)) as Approved, Users.Unit_ID As usersUnitID, * From TITLE, Users WHERE User_ID = @userID AND TITLE.TITLE_ID = Users.Title_ID )
Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL
//End of sql
Can you point to what I am doing wrong? Thanks in advance!
Multiple Case Statements
Mar 14, 2008This query brings back 2 rows for each record, one for each case statement. How can I change this to bring in the contsupp.notes for both types of data? Or could it be fixed in the joins?
select
contact1.accountno,
contact1.key5 as "Ch#",
contact2.uexpsort as "Sort",
contact1.company as "Church",
contact1.contact as "Editor",
contact1.phone1 as "Phone",
contact2.uemerg as "Emergency",
contact1.Address1 as "Address",
contact1.city as "City",
contact1.state as "State",
contact1.zip as "Zip",
contact2.ubulletnqt as "Qty",
contact2.ubullcolor as "BullColor",
contact2.ubarcode as "Barcode",
contact2.udelivery as "Delivery",
contact2.uoutputdev as "OutputDev",
contact2.utimedeliv as "Time",
contact2.ucolor as "DelivColor",
contact2.ucollated as "Collated",
contact2.ustapled as "Stapled",
case when contsupp.contsupref = 'Delivery Notes' then contsupp.notes end as "Deliverynotes",
case when contsupp.contsupref = 'Cover Change Slip' then contsupp.notes end as "CoverChangeSlip"
from
Contact1 inner join contact2 on Contact1.Accountno = Contact2.Accountno
inner join contsupp on Contact1.Accountno=Contsupp.Accountno
where
contact1.key5 not like '' and contsupp.contsupref='Delivery Notes' or contsupp.contsupref ='Cover Change Slip' order by contact1.key5
Thanks for your help
CTE With Multiple Update Statements?
Sep 19, 2006HI Gurus
I have written one CTE (common table expression) and trying to use same CTE with three seperate UPDATE statements which gives me error saying "Invalid Object name" (it works fine when I try to use with 1 update statement (any one from three update statements)
Isnt it possible that I can use 1 CTE with mutiple update statements?
waiting for your reply....
Multiple MDX Statements In A Report
Aug 28, 2007Does anyone know how to execute more than one MDX statement in a single report? For example, If you wanted to create a session set, run some mdx with the set included, then drop the session set. In Management studio the mdx works perfectly if you separate the individual mdx statements with GO. I tried using semicolons as mdx statement terminators and this didn't work either.
Reporting Services does something between when the report begins it's execution and the time the MDX is submitted to AS. And running a trace to see exactly what is passed in doesn't work either - the only message displayed is the same error message as what is passed back to RS. (I ran a trace with all events selected).
Anyone have any ideas?
Thank you in advance,
John
Parameter For Multiple Where Statements
Sep 5, 2007I'm hoping someone out there has a creative solution for my scenario. I have the report code below. I want to create a parameter that allows the user to select which where statement to use in the report. Then I want to use the corresponding where description below ( ex: @WHEREDESC1 ) in my header which will describe (in laymen's terms) which parameter was chosen and what is being reported on.
Has anyone achieved this or have an idea how it may be done?
DECLARE @WHEREDESC1 VARCHAR(50), @WHEREDESC2 VARCHAR(50), @WHEREDESC3 VARCHAR(50),
@WHEREDESC4 VARCHAR(50), @WHEREDESC5 VARCHAR(50)
SET @WHEREDESC1 = 'SRM Admitting and Principal Diagnosis do not match'
SET @WHEREDESC2 = 'SRM and AMPFM Admit dates do not match'
SET @WHEREDESC3 = 'SRM and AMPFM Discharge dates do not match'
SET @WHEREDESC4 = 'SRM and AMPFM Visit Type Codes do not match'
SET @WHEREDESC5 = 'SRM and AMPFM DRG codes do not match'
SELECT LEFT(srm.EPISODES.ADMISSION_DATE, 11) AS SRM_Admit_DT, ampfm.rpt_AdtVisit.AdmitDate AS AMPFM_Admit_DT,
LEFT(srm.EPISODES.EPISODE_DATE, 11) AS SRM_Disch_DT, ampfm.rpt_AdtVisit.DischDate AS AMPFM_Disch_DT,
srm.EPISODES.EPISODE_TYPE AS SRM_Visit_Type, ampfm.rpt_AdtVisit.VisitTypeCode AS AMPFM_Visit_Type,
srm.EPISODES.CHARGES AS SRM_Charges, ampfm.rpt_VisitFinancial.TotCharges AS AMPFM_Charges,
ampfm.rpt_VisitFinancial.VisitChargesActive AS AMPFM_Active_Charges, ampfm.rpt_AdtVisit.PatientFullName,
srm.CDMAB_DRG_INFO.DRG_NUMBER AS SRM_Drg, ampfm.rpt_InsuranceDrg.InsDrg AS AMPFM_Drg,
ampfm.rpt_AdtVisit.Complaint AS AMPFM_Admit_Dx, srm.cdmab_base_info.ADM_DX_CODE, srm.cdmab_base_info.ADM_DX_DESC,
srm.cdmab_base_info.PRIN_DX_CODE, srm.cdmab_base_info.PRIN_DX_DESC, srm.EPISODES.ACCOUNT_NUMBER,
srm.EPISODES.MEDREC_NO
FROM srm.EPISODES INNER JOIN
ampfm.rpt_AdtVisit ON srm.EPISODES.ACCOUNT_NUMBER = ampfm.rpt_AdtVisit.AccountNumber INNER JOIN
ampfm.rpt_VisitFinancial ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_VisitFinancial.IntOOS INNER JOIN
ampfm.rpt_InsuranceDrg ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_InsuranceDrg.IntOOS AND
ampfm.rpt_AdtVisit.VisitTypeCode = ampfm.rpt_InsuranceDrg.VisitTypeCode INNER JOIN
srm.CDMAB_DRG_INFO ON srm.EPISODES.EPISODE_KEY = srm.CDMAB_DRG_INFO.EPISODE_KEY INNER JOIN
srm.cdmab_base_info ON srm.EPISODES.EPISODE_KEY = srm.cdmab_base_info.EPISODE_KEY
--WHERE srm.cdmab_base_info.ADM_DX_CODE <> srm.cdmab_base_info.PRIN_DX_CODE
--WHERE srm.EPISODES.ADMISSION_DATE <> ampfm.rpt_AdtVisit.AdmitDate
--WHERE srm.EPISODES.EPISODE_DATE <> ampfm.rpt_AdtVisit.DischDate
--WHERE srm.EPISODES.EPISODE_TYPE <> ampfm.rpt_AdtVisit.VisitTypeCode
--WHERE srm.CDMAB_DRG_INFO.DRG_NUMBER <> ampfm.rpt_InsuranceDrg.InsDrg
Order By srm.EPISODES.ACCOUNT_NUMBER
Execute Multiple Statements At Once...
Oct 17, 2006I am not even sure if this is possible but I want to be able to excute several statements (or SPs) at once from inside a SPs (or any other method).
What I am doing is a I am taking data from a single column, multiple rows and making it into one row (i.e. data1 + data2 + data3....) But I am doing this to a total of 2.1 million individual rows and the result will be about 204k rows.... what I have written is basically a nested loop and it works fine but very slow... slow as in it has been running for 24 hours now and it is about 60% done... I need this to finish in under 36 hours preferably...
If I could handle more than one set of data at once (there will be no duplicates) I could speed up the process by how ever many I feel like working with...
So is there a way to execute a statement (sp or function) and go to the next statement without waiting for the first to finish??
Thanks Big Time... I hope this is possible.
OH.... SQL 2000 SP4 on Windows 2003.
Combining Multiple Select Statements In A SP
Jul 27, 2007I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
View 2 Replies View RelatedUpdate Multiple Columns In One Sql Statements
Nov 2, 2004I am almost sure I can update variables columns in one select/case type
statement, but having problems working out the syntax.
I have a table with transactions - with tran types as the key.
in this example, types = A,B,C ,D.
in this first example I am updating the sum of QTY to value t_A based on
tran types =A.
can I perform sub query/case to update with the same where clause
but for types B,C and D?? I also have to insert for specific lot numbers each sum values.
Create table #t_reconcile(
t_lot_number int not null,
t_A float,
t_B float,
t_C float,
t_D float)
insert #t_reconcile
select t.lot_number, sum(t.qty)
from i , t
where i._id = t.event_id
i.transaction_type = 'A'
group by t.lot_number
order by t.lot_number
How To Run Multiple Sql Statements From A Single File?
Nov 15, 2005Hi,I am wondering if anyone has any examples of how to run multiple sql statements from a file using .net? I want to automatically install any stored procedures or scripts from a single file on the server when my web application runs for the first time. Thanks for your help in advance!
View 1 Replies View RelatedCondensing Multiple Update Statements
Mar 18, 2008I'm working on a query which involves changing the case of a field from mixed case to all lower case. The field exists in multiple tables, so to do this I have multiple update statements. Is there a way to make this more efficient?
See below for example:
update InvestBroker
set BrokerID = lower(BrokerID)
update InvestFill
set BrokerID = lower(BrokerID)
update InvestBrokerAccount
set BrokerID = lower(BrokerID)
update InvestFIXBroker
set BrokerID = lower(BrokerID)
update InvestUploadBrokerFilter
set BrokerID = lower(BrokerID)
update InvestSettleInstructions
set BrokerID = lower(BrokerID)
Enclosing Multiple Statements In A Variable
Oct 22, 2004Hi all;
How do I enclose multiple filters in a variable, for instance how would I put the following filter into a variable and also is it actually possible or do I have to do something else before performing this type of operation:
tel1 like '072%' or tel1 like '+27 72%' or tel1 like '072-%'
or tel1 like '+2772%' or tel1 like '72%'
and len(tel1) > 7
or tel2 like '072%' or tel2 like '+27 72%' or tel2 like '072-%'
or tel2 like '+2772%' or tel2 like '72%'
and len(tel2) > 7
or tel3 like '072%' or tel3 like '+27 72%' or tel3 like '072-%'
or tel3 like '+2772%' or tel3 like '72%'
and len(tel3) > 7
or tel4 like '072%' or tel4 like '+27 72%' or tel4 like '072-%'
or tel4 like '+2772%' or tel4 like '72%'
and len(tel4) > 7
or tel5 like '072%' or tel5 like '+27 72%' or tel5 like '072-%'
or tel5 like '+2772%' or tel5 like '72%'
and len(tel5) > 7
or tel_other like '072%' or tel_other like '+27 72%' or tel_other like '072-%'
or tel_other like '+2772%' or tel_other like '72%'
and len(tel_other) > 7
the problem is that it's got a couple of apostrophes which when declaring variables pulls it out of that mode, I have looked on the internet but can't seem to find anything
Multiple Case Statements In One Select
Dec 16, 2014I know I should know the answer to this, but I just can't quite get the syntax down
Code:
Select case when zipCode = '10185' Then 'Deliver'
Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '88221' And paid = 'No' Then 'Hold'
Else when zipCode = '34123' Then 'Deliver'
End
From postalDeliveryDatabase
Multiple Statements In Stored Procedure
Apr 29, 2008I believe we can you multiple statements in stored procedures?
Suppose I have a stored procedure and I pass parameters to this SP.
What I am aiming for is to pass some values to the stored procedure, use a select statement to retrieve some values, then have two update statements as below. Updating the same table but with opposite values, both passed as a parameter and retrived, as given below:
CREATE sp_temp_proc
@order_id int,
@order_position int,
@temp_order_id OUTPUT
@temp_order_position OUTPUT,
SELECT order_id AS temp_order_id
FROM <TABLE> WHERE order_position < @order_position
@temp_order_id = temp_order_id
UPDATE <TABLE> SET order_position = @order_position WHERE order_id =
@temp_order_id
UPDATE <TABLE> SET order_position = @temp_order_position WHERE order_id = @order_id
Are Multiple Statements Allowed For CommandText?
Dec 4, 2007Hi,
I'm wondering if SqlCommand.CommandText could be set with multiple statements when CommandType=Text. anyone knows it?
I'm sure storedprocedure is the right way to go. I'm curious if this is one thing that only sp can do.
Thx
Tao
Multiple SQL Statements In A Stored Procedure
Jul 23, 2005Hi!I got 2 stored procedure, proc1 executes proc2,proc2 does some updates and inserts on different tables ...proc1:ALTER PROCEDUREASexecute proc2SELECT * FROM tblFoo______________________my problem is, that when executing proc1, I receive the message:"THE SP executed successfully, but did not return records!"But I need the resultset from "SELECT * FROM tblFoo" that is executedat the end of proc1.I'm not sure, but I think that I solved a similira problem with "setnocount on", I put it into both SP, but it's still the same ... noresultset ...How can I display "SELECT * FROM tblFoo" within a SP, where SQLstatements are executed before?!Thank you!
View 1 Replies View RelatedAnalysis :: Multiple IIF Statements In SSAS?
Jun 2, 2015I am using multiple IIF statements in ssas, it does not produce any output, does not shows any error, simply it processing..
View 3 Replies View RelatedUse Of Multiple Unconnected Or Unassociated IIF Statements
Jun 19, 2007Hello,
I am curious as to whether I can insert more than one IIF statement into a table's visibility expression without nesting one inside the other.
I am rather loath to do so because of the risks that subsequent developers unfamiliar with both the situation and the logic could alter one IIF and not realize that if chokes off the other.
It had been my hope to apply the IIFs such that they would act independent of one another in a sort-of 'OR' statement whereby if one resulted in the table's hidden property being 'True' then it would not matter if the other's was true or false, etc.
Thanks in advance.
Transact SQL :: Can Have Multiple Statements Under CASE-THEN
Jun 3, 2015So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]
Price Of Using Multiple Databases In SELECT Statements
Sep 11, 2007Hi,We are discussing possible implementation for sql2005 database(s). This database will serve one web portal. Part of data will get into it by hand, and part will be replicated from internal system.Some of us are for creating two separate databases, since there are two separate datasources. One, automatic, will change very little over time and requires almost no maintenance. Other datasource will be manual input. Tables and procedures related to this part will change over time.Some of us are for creating single database, since it will serve one web site. More important this group is concerned about performance issues since almost every select will require join between tables that would be stored in two separate databases. Do these issues exist? Can you share some insights, comments, links about this?
View 2 Replies View RelatedStored Procedure With Multiple Update Statements
Jan 31, 2008I have a SP that has the correct syntax. However when I run my web-app it gives me this error: "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. "
The procedure takes in three parameters and retrieves 23 values from the DB to display on my form.
Any ideas?
DataSet With Multiple SELECT Statements To The Same Table
Apr 29, 2008Hi,
OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
Multiple Order By Statements In Union Select
Jan 20, 2005I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.
declare @OrderBy
@OrderBy = 'first_name'
Select first_name,last_name from table1
Union
Select first_name,last_name from table2
if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end
You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help
Execute Multiple SQL Statements In Stored Proc
Nov 1, 2005Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)
Table Test
Id Description
1Insert into test(Id,Name) Values (1,'Ron')
2Update Test Set Name = 'Robert' where Id = 1
3Delete from Test where Id = 1
In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks
Reo
Insert Statement With Multiple Select Statements
Aug 29, 2006hi
first of all is it possible? if so, what am i doing wrong with this
INSERT into TB2
(
ClientCode,
EngagementCode,
EngagementDescription
)
SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),
NULL,
SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)
the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.
this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks
tibor