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 int
SET @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!
how 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')
I 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?
I 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
Hi,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?
Hi, 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...
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
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
I have used several sql queris to generate a report. This queries pull out data from different tables. But sometimes at the same table too. Basically those are SELECT statements. I have created stored proc for each SELECT statement. now I'm wondering can I include all SELECT statements in one stored proc and run the report. If possible, can anyone show me the format?
Hey Guys. I’m having a little trouble and was wondering if you could help me out. I’m trying to create a custom paging control, so I create a stored procedure that returns the appropriate records as well as the total amount of records. And that works fine. What I’m having problems with is reading the data from the second select statement within the code. Anyone have any idea on how to do this? Also.. how can I check how many tables were returned? Here's my code. I'm trying to keep it very generic so I can send it any sql statement:public DataTable connect(string sql) { DataTable dt = new DataTable();
SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ToString()); SqlDataAdapter SqlCmd = new SqlDataAdapter(sql, SqlCon); System.Data.DataSet ds = new System.Data.DataSet(); SqlCmd.Fill(ds);
dt = ds.Tables[0];
//Here's where I don't know how to access the second select statement
return dt; } Here's my stored procedure: ALTER PROCEDURE dbo.MyStoredProcedure ( @Page int, @AmountPerPage int, @TotalRecords int output )
AS
WITH MyTable AS (
Select *, ROW_NUMBER() OVER(ORDER BY ID Desc) as RowNum From Table where Deleted <> 1 )
select * from MyTable WHERE RowNum > (((@Page-1)*@AmountPerPage)) and RowNum < ((@Page*@AmountPerPage)+1);
Select @TotalRecords = COUNT(*) from Table where Deleted <> 1 RETURN
My goal is to run a bunch of select statements from different tables in one database and have them all insert to the same columns/table in the new database. Do I need a new data source for each statement, or is there a way to run all the statements in one set seeing as they all have the same destination. I keep receiving the SQL statement improperly ended error when trying.
I ran the below 2 select statements and ended up seeing multiple cached instances of the same stored procedure. The majority have only one cached instance but more than a handful have multiple cached instances. When there are multiple cached instances of the same sproc, which one will sql server reuse when the sproc is called?
SELECT o.name, o.object_id, ps.last_execution_time , ps.last_elapsed_time * 0.000001 as last_elapsed_timeINSeconds, ps.min_elapsed_time * 0.000001 as min_elapsed_timeINSeconds, ps.max_elapsed_time * 0.000001 as max_elapsed_timeINSeconds
I 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,
I 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
Is 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?
This 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
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?
Does 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).
I'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?
I 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??
I 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
Hi,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!
I'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)
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
I 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:
Hi!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!
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.
So 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]
