Hi,
I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:Luca de Angelis
7 -- Create date: 22/08/2007
8 -- Description:Inserimento dei dati contabili nella tabella di log
9 -- =============================================
10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili
11 -- Add the parameters for the stored procedure here
12 @id_gestore tinyint
13 AS
14 SET NOCOUNT ON
15 BEGIN TRANSACTION
16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
17 SELECT CEL_traffico_temp.numero_telefonico
18 , CEL_traffico_temp.anno
19 , CEL_traffico_temp.mese
20 , @id_gestore as id_gestore
21 , 1
22 FROM CEL_traffico_temp
23 INNER JOIN
24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella
25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
26
27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
28 SELECT CEL_traffico_temp.numero_telefonico
29 , CEL_traffico_temp.anno
30 , CEL_traffico_temp.mese
31 , @id_gestore as id_gestore
32 , 2
33 FROM CEL_traffico_temp
34 INNER JOIN
35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella
36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
37
38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
39 SELECT CEL_traffico_temp.numero_telefonico
40 , CEL_traffico_temp.anno
41 , CEL_traffico_temp.mese
42 , @id_gestore as id_gestore
43 , 3
44 FROM CEL_traffico_temp
45 INNER JOIN
46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella
47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
48
49 IF @@error <> 0
50 BEGIN
51 ROLLBACK TRANSACTION
52 END
53 ELSE
54 BEGIN
55 COMMIT TRANSACTION
56 END
Help me please...
Hi all, I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me. Thank You
Hi I use the next StoredProcedure in Access, Inserts Users to tblUsers: INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments ) VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
I am using SQL Server 2005 now and I have a table with following columns. ID, FirstName, LastName, Email "ID" is the primary key (int) and is set auto generated (1 increment) I have a StoredProcedure to insert a new record. CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
Visual Studio 2008 Code VB I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table. Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
AS BEGIN IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID, Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID, @Status,@CaseType,@Offense)
To all, I looked at the MS-SQL pubs sample database and execute the example stored procedure reptq2 and I got 17 results set back. Where can I find an example using Visual Studio DataGrid or any means to get all these results from this SP.
AS BEGIN IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number) BEGIN UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID END ELSE BEGIN print 'CANNOT UPDATE' END END
This my storedproc. My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number Then the update should take place.
but if any other row other than CustomerID=1 having name=@name and number=@number Then the update is should not take place.
but The above stored procedure is not working like that.
so please some one help me with this. Thankyou Ramya.
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets) Here is the function to fill the RBL: 1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet 2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 cmd.CommandText = "usp_responses_sel" 5 cmd.CommandType = Data.CommandType.StoredProcedure 6 7 ' Fill usp_ with Parameters 8 cmd.Parameters.AddWithValue("QuestionID", QuestionID) 9 cmd.Parameters.AddWithValue("LocaleID", LocaleID) 10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum) 11 12 Dim da As New SqlDataAdapter 13 da.SelectCommand = cmd 14 Dim ds As New DataSet 15 da.Fill(ds, "response") 16 Return ds 17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist. Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2) 2 rblResponses2.DataSource = ds 3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound? Thanks
hi i have a stored procedure witch Returns 0 or 1 dependig if exists some rows how can i acces that value in code behind? i tryied all, command.ExecuteScalar(), command.ExecuteNonQuery() but none works i need something like if(command.GetReurnValue) .... else .... thanks in advance
Can I know the best method to connect the sql server 2008 with Visual studio 2008, it will be helpful if I know how to access the stored procedures in the sqlserver via LINQ.
I need to execute stored procedure which is suppose to return GUID to my IF statement and if it is Nothing I execute other Stored procedures else some other procedures. My problem is that even though by looking at the data I know that after the execution of the procedure it should return some guid value it doesn't anybody who had the same issue??? That is the code block where I am trying to return guid from my stored procedure: getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If Thank you
Hello everyone,I am developing forums (Discussion Board) in C#.net 2005 with SqlServer. Right now i am having problem in fetching data from two tables.Here are the tables from which i want to fetch data.Topics Table Threads TableTopicID ThreadIDForumID TopicIDTopicName SubjectTopicDescription Replies UserID LastPostDatenow i want to fetch all the topic talbe data as well as total no of threads,Lastpostdate,UserID per topic.I am able to fetch topic table data and Total no of threads per topic through the following query.SELECT TopicID, ForumID, TopicName, TopicDescription,(SELECT COUNT(ThreadID) FROM Portal_Threads WHERE (TopicID = Portal_Topics.TopicID)) AS Threads FROM Portal_Topicsbut i am not able to fetch anoter two detail with subquery as i am getting error likeonly one expression can be specified in the select listorsubquery returned more than one value.can anyone tell me how can i fetch these two values per topic. should i use stored procedure and create temporary table and after fetching these values i can store it in temporary table and i can fetch values from that temporary table...please provide code snippet if possible as i've never used sqlserver before..Thanks in advance...Regards,Nil
I have a DTS package (AdIns) that inserts to an administrative table. The Administrative table utilizes the "with ignore_dup_key" option on the index. There are other admin jobs in the DTS that are based on the return code of a parent package.
The "3604:duplicate key ignored" is an expected result of the parent package, yet it sends an failure return code to the dependent (AdIns) package, causing erroneous entries to the final audit table.
How can I reset the return code from the parent package?
I need to create a StoredProcedure to calculate a field auto-inc. Who helps me? I tried many code, but i didn't have sucess.
My sample:
CREATE PROCEDURE SP_CT_ITEM AS
DECLARE @CONTADOR NUMERIC(008)
SET NOCOUNT ON
BEGIN TRAN
SELECT @CONTADOR = CAST(NM1_PARAMETRO AS NUMERIC(008)) FROM PARAMETRO (UPDLOCK) WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
UPDATE PARAMETRO SET NM1_PARAMETRO = @CONTADOR + 1 WHERE SIG_PARAMETRO = 'CT' AND GRU_PARAMETRO = '001' AND COD_PARAMETRO = 'ITEM'
I have to write a stored procedure.I have a search page having four fields.Giving any of the field should fetch the whole record and display in the gridview. My trouble starts here I have a button field in gridview1 . when i click on the button there should be another gridview which displays refunds of particular customer i.e from another table.There is only one common colum in the two tables. based on that colum value we have to fetch from second table. now my question is : how to capture the colum value of first select statement and give it as input to second select staement.
my code is here :
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[search] (@val1 varchar(225), @val2 varchar(50), @val3 varchar(50), @val4 varchar(50)) AS BEGIN Select*From customer where ((@val1 IS NULL) or (CNo = @val1)) AND ((@val2 IS NULL) or(LastName = @val2)) AND ((@val3 IS NULL) or(FirstName = @val3)) AND ((@val4 IS NULL) or(PhoneNumber = @val4)) Select * From refunds where CNo = @val1 END
here if i fill CNo text box in my search page its giving the value but all the time user may not give CNo.He may search customers based firstname ,lastname etc. so what should i do to capture CNo from first select statement and give it as input to second select statements
In a DataFlowTask with several OLE DB Destinations, how can I "last", before ending this DFT execute a storedProcedure?
This storedprocedure is used for saving metadata (taskname, rowcounts etc) regarding this DFT and I dont want to add an ExecuteSQLTask after the DFT in the Control Flow
I am developing ASP.NET 2.0 website. I need to know some about using stored procedure. I searched through google. But could now find a favourable repLy. Here is ..
Which way is efficient, using SQL inside the code or as SRORED PROCEDRE, which one to use with ASP.NET? Is the Stored procedure must be created withing the server or from my application?Can anyone please give some practicle explaination about this? My advance thanks for all...
Hi Experts,I m using a stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[SP_Table_SELECT]( @TableName VarChar)ASset nocount onset ansi_warnings offDECLARE @sql varchar(2000)SET @sql = 'SELECT* FROM (' +@TableName + ')'EXEC (@sql)when I run It it shows return value = o and Query Completed with ErrorsThere are data in My Table. Help Plzzzz!!!!!!
Hi to all , I wrote a stiored procedure like below SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE SELET_AND_RETURN ( @opId Nvarchar(50) output)ASBEGIN SET NOCOUNT ON; SELECT @opId = NAME FROM TABLE1 PRINT @opId RETURN @opId;ENDGONow i include this procedure into my project, my problem is i dont know how to pass the output parameter from front end coding,
Halloi have a problem to send a parameter via QueryString to my storedProcedure.If i run the procedure in VisualWebDeveloper everything works fine, when i enter the parameter with the DialogBox: 1 ALTER PROCEDURE dbo.StoredProcedure2 2 @appart varchar(50)3 4 AS5 BEGIN6 7 SELECT category FROM immovables WHERE category = @appart 8 END9 /* SET NOCOUNT ON */ 10 RETURNThen i have a simple Page with a link <a href="Test.aspx?objtxt=34" target="_self">send</a> that send the QueryString to the Following Site: <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Unbenannte Seite" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="StoredProcedure2" SelectCommandType="StoredProcedure" SortParameterName="objtxt"> <SelectParameters> <asp:QueryStringParameter Name="appart" QueryStringField="objtxt" Type="String" /> </SelectParameters> </asp:SqlDataSource> <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px" AutoGenerateRows="False" DataSourceID="SqlDataSource1"> <Fields> <asp:BoundField DataField="category" HeaderText="category" SortExpression="category" /> </Fields> </asp:DetailsView> </asp:Content> Now, when i run the application, i get the following message, when i fire up the Link:Für die Prozedur oder Funktion StoredProcedure2 wurden zu viele Argumente angegeben.I'm german so i get this message in german, tranlated: to many aguments wher passed to the procedure or funktion StoredProcedure2 I think the problem has thomething to do with the parameter deklaration in the storedProcedure.am I wrong?can somebody help me?Regards from Cologne (Germany) Caspar
i want to store a date into sqlserver database using stored procedure. when i run app. it will give this error....... ---------------------------------- Server Error in '/aspnet/espms' Application. --------------------------------------------------------------------------------
String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: String was not recognized as a valid DateTime.
Source Error:
Line 349: cmd.Parameters(6).Value = CType(ddldeltype.SelectedValue, Integer) Line 350: cmd.Parameters(7).Value = txtshipnm.Text Line 351: If cmd.ExecuteNonQuery Then Line 352: add = True Line 353: Else
My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName '@total' is not contained by this SqlParameterCollection. Here is my stored Proc and my VB program is right below I- Stored Proc: CREATE PROCEDURE dbo.totalsub @account bigint,@total bigint outputASselect total=sum(SubPhnNmbr) from tblsub where SubAccNmbr=@accountreturnGO II- And my pogram in VB is: Dim totsub As Int64 Dim cm As New SqlCommand Dim cn As New MyConnection cn.open 'my connection is defined by me don't worry about it cm.CommandType = CommandType.StoredProcedure cm.CommandText = "totalsub" cm.Connection = cn Dim pm As SqlParameter pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Account", System.Data.SqlDbType.BigInt)) pm.Value = 100000165 pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4)) pm.Direction = ParameterDirection.Output totsub = cm.Parameters("total").Value cm.ExecuteScalar() totsub = cm.Parameters("total").Value I also tried using @total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output No Luck, someone pls can help
My situation is this: I have a requirement table,in the requirement table i have a field which is allow_multiple,if it allows multiple the value is 1 and 0 if it will not allow and also I have an insert storedprocedure for inserting licenses which will be added in the Staffl table.When i insert a new license for the staff,it should check in the requirement table if the new inserted lincense does have 0 or 1 value in the allow multiple field.If it has a value of 0 the new license should not be inserted.What would be the good way to compare the existing licenses of the staff and the newluy added?
I know it's unbelievable, but i didn't ever use SP before. Sorry for this noobish question, don't beat me for this - please ;-)
In my SELECT Query i like to have some (WHERE) paramteters le's say: ID, NAME and AGE. I allways programmicali generate a WHERE statement in relation to which parameters I really get, because not allwasy i get all the parameters together. This is fine for "normal" Tables.
How does this work with SP? If I don't get NAME, do I set the SP-Parameter NAME to null to unconsider it? Or do I have to make some IF statements in the SP to check, if NAME has some value?
Hi, I do have a stored procedure with parameters inside. I just want to ignore those parameters that are NULL so that my WHERE clause will not execute them anymore. Here's my code:
CREATE PROCEDURE SEARCHPATIENT @patnCode varchar(10) = NULL, @patnSurname varchar(20) = NULL, @patnGivenName1 varchar(20) = NULL AS SELECT.... FROM ... WHERE patnCode=@patncode AND patnSurname =@patnSurname AND patnGivenName1 =@patnGivenName1
BUT... If the user passes @patnSurname as NULL, I got an SQL error message: "There was an error executing the query.. Timeout expired... ". I was hoping I could write an WHERE clause where IT WILL ONLY EXECUTE those parameters WITH VALUE and IGNORE those NULLs.
Can someone help me on this? Can I use IF THEN ELSE statement inside the WHERE clause? Can I use CASE STATEMENT inside the WHERE clause?
