I 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?
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:
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
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!
View 1 Replies View Related
Hi all,
I have 2 select statements in my Stored Proc.
I want to display the results of each query in my DataGridView.
However, only the data of the last select query is returned.
Why is this?
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
View 3 Replies View RelatedHey 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
Thanks
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
[code]...
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
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)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'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.
IF EXISTS (SELECT 1 FROM RoleUser WHERE User_Id = 12346 AND Role_Code = 'CRC')
UPDATERoleUser
SETAccess= 1,
Worklist= 0,
Supervisor= 0
WHERERole_Code= 'CRC'
[Code] ....
HI 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....
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
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)
Hi, 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
I am seeking a syntax example for executing multiple T-SQL statements in a single stored procedure. For example, I would like to insert a new client record and immediately insert an associated household record. I don't want to have to hit the database twice to accomplish this, and I prefer not to use a trigger.
View 1 Replies View RelatedWhen working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanks
Hi,
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?
Thanks
Hi all,I have 2 independent stored procedures(I cannot join them because ofthe group condition are different however they have the same cust id)but one for reading the summary and other showing the details of thesummary. Now I want to run these two stored procedures in order toget the both return results for generating a .Net crytral report. CanI get the results form both stored procedures and how can I do that.Please help me. Thanks a lot.Hello
View 3 Replies View RelatedHello all,
I am having a hard time getting this to work.
I am trying to build the where statement dynamically but it is a string and needs quotes. But the problem is the quotes are the problem. Are there any escape characters? or is there a way to make this happen??? Integers are easy keys because they do not need quotes.
SET @wheretmp = 'WHERE SourceIPID = BB901625-5E89-45D4-BD20-25730365A9DA'
Select * from tbl_All_Source @wheretmp
Hi
I have a small problem writing a stored procedure in a SQL Server 2000 database.
I would like to generate som part of the SQL inside this stored procedure that is used in an IN expression of my WHERE clause. There is no problem for me to generate a string containing my expression, the problem is that SQL-Server don´t generate a resulting SQL-statement.
Example:
CREATE PROCEDURE spDynStatement
AS
DECLARE @sPartOfSQLStatement NVARCHAR(100)
-- Some T-SQL that generates the dynamic part of the SQL-statement
-- .
-- .
-- .
-- As substitute I insert the string expression
SET @sPartOfSQLStatement = '''1''' + ', ' + '''1.5'''
-- SELECT @sPartOfSQLStatement results in: '1' , '1.5'
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( @sPartOfSQLStatement ) -- does not work
SELECT * FROM BBNOrganization WHERE OrgStructureID IN( '1', '1.5' ) -- works!!!
GO
Thankfull for ideas on how to solve my problem,
Peter
I'm profiling a specific procedure on a database, I have the start and end times captured but within the same trace I would like to capture all the individual sql statements.
So, given this simple procedureÂ
ALTER PROCEDURE [dbo].[usp_b]
AS
BEGIN
SET NOCOUNT ON;
SELECT @@VERSION;
SELECT GETDATE();
END
In the trace I would like to see the two SELECTs, however all I get is thisÂ
I have the following events selected in the trace.
I want to include GO statements to execute a large stored procedure in batches, how can I do that?
View 9 Replies View RelatedHi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
Hello,
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...
my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...
if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...
this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...
but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??
Cheers,
Justin
Can someone walk me through the code for my update_command event?Every article I read and every tutorial I walk through has a slightly different way of doing this task.It's confusing trying to understand which code-behind variables I need in my update_command event and how to pass them to a stored procedure.
Please help me connect the dots.
I have a SQL server table that looks like this (Both data types are char)
Status_Id Status_DescriptionA ActiveP Planned
I have a SQL stored procedure that looks like this…
create procedure dbo.usp_Update_Status_Master(@status_id char(1),@status_description char(30))asupdate status_masterset status_description = @status_descriptionwhere status_id = @status_idGO
Here is my code behind…
Imports SystemImports System.DataImports System.Data.SqlClientImports System.ConfigurationImports System.Data.OdbcPublic Class WebForm1 Inherits System.Web.UI.Page Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then Call LoadStatusMasterGrid() End If End Sub Public Sub LoadStatusMasterGrid() Dim connection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("sqlConn")) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("usp_Select_Status_Master", connection) Command.CommandType = CommandType.StoredProcedure Dim adapter As SqlDataAdapter = New SqlDataAdapter(Command) Dim table As DataTable = New DataTable adapter.Fill(table) dgStatusMaster.DataSource = table dgStatusMaster.DataKeyField = "status_id" dgStatusMaster.DataBind() Catch ex As Exception Console.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub Private Sub dgStatusMaster_EditCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.EditCommand dgStatusMaster.EditItemIndex = e.Item.ItemIndex dgStatusMaster.DataBind() Call LoadStatusMasterGrid() End Sub Private Sub dgStatusMaster_CancelCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.CancelCommand dgStatusMaster.EditItemIndex = -1 Call LoadStatusMasterGrid() End Sub Private Sub dgStatusMaster_UpdateCommand(ByVal source As Object, _ ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgStatusMaster.UpdateCommand ‘ How do I code this part? End SubEnd Class
Thanks in advance for taking the time.Tim
hi
i have 2 columns linked ( 1 to a textbox and the other to drop down list)
when i try to update i get 1 of the to the update SP but not the other and get this error
Procedure or Function 'Update_ActiveCity' expects parameter '@Cities', which was not supplied.
_________________ this is the code of the aspx ____________
<asp:GridView ID="grdD" runat="server" AutoGenerateColumns="False" DataKeyNames="CountryCode" DataSourceID="dsGrdD" OnRowDataBound="grdD_RowDataBound"><Columns><asp:TemplateField><ItemTemplate>
<asp:TextBox ID="txtCountry" runat="server" Text='<%# Bind("Country") %>' />
<asp:DropDownList ID="ddlCities" runat="server" />
</ItemTemplate></asp:TemplateField></Columns></asp:GridView>
<asp:SqlDataSource ID="dsGrdD" runat="server" ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
SelectCommand="Select_Cities" SelectCommandType="StoredProcedure" UpdateCommand="'Update_ActiveCity' " UpdateCommandType="StoredProcedure" CacheExpirationPolicy="Sliding">
<SelectParameters> <asp:SessionParameter Name="ListCode" SessionField="ListCode" Type="String" /> </SelectParameters>
</asp:SqlDataSource>
_______________________ this is the code behind ____________________protected void grdD_RowDataBound(object sender, GridViewRowEventArgs e)
{DropDownList ddl = e.Row.FindControl("ddlCities") as DropDownList;if (ddl != null)
{
string s = DataBinder.Eval(((GridViewRow)e.Row).DataItem, "Cities").ToString();ddl.DataSource = s.Split(',');
ddl.DataBind();
}
}
_______________________________________________________________________________-
here is the procedureALTER PROCEDURE dbo.UpdateContact
(@ContactId bigint,@FirstName nvarchar(50),
@LastName nvarchar(50),@Telephone nvarchar(50),
@Addressline nvarchar(150),@State nvarchar(100),
@City nvarchar(100),@PostalCode varchar(50),
@Email nvarchar(50),
@MobilePhone varchar(50))
AS
SET NOCOUNT ON
UPDATE ContactSET FirstName = @FirstName,
LastName = @LastName,
Telephone = @Telephone,
MobilePhone = @MobilePhone,
Email = @Email,
Addressline = @Addressline,
City = @City,
State = @State,
PostalCode = @PostalCodeWHERE ContactId = @ContactId
RETURN
what is the problem if i execute the storedprocedure separately it is working but when i call the storedprocedure in the code it fails.
It gives an error as "syntax error near Updatecontact"
Any ideas???
I'm sorry for asking a lot of questions, but it is driving me crazy that I can't figure out how to do this update..
Let say I got two tables, both with an "ID".
TBL 1 TBL 2
ID <--> ID
STATUS
If they match, I want to update the status on tbl1 as "matched"
How would I preform this with a stored procedure??
I am currently using a view and then updating the view (I KNOW ITS BAD!)