Hi all,
I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row.
The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.
Is there any way i could do this?
Exmaple:
In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!
Procedure dbo.AddressInsert(....)
as
BEGIN
Insert INTO Adress ....
SELECT * FROM AdressView WHERE AddressID = @@identity
END
Procedure dbo.CustomerInsert(...)
as
begin
-- transcation begin...
EXEC dbo.AddressInsert
INSERT INTO Customer....
-- end transcation
SELECT * FROM Customer WHERE CustomerID = @@identity
I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.
CREATE PROC proc_Emp
@Sel_Country AS BEGIN
SELECT DISTINCT employee, hrid FROM emp_table WHERE country = @Sel_Country
@SEND_RESULTS = CASE WHEN row_count = 0 THEN
SELECT 'No Country', 4444444444 ELSE
(send original results) END
END
I need to:
Query for the employee list
If the row count is zero/null then return fixed values
Else return the orginal data from Step 1. I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
Hi,I am trying to put SQL Server Stored Procedures into Sourcesafe as perthe Microsoft Knowledge Base article 818368, but have run into aproblem.The web server is SQL Server 2000 running on Windows 2003 Server. Theclient dev environment is Visual Studio 2003 Enterprise DeveloperEdition.I have carried out the following steps successfully:-1. Installed Sourcesafe client tools on the Server (sourcesafe is onanother server)2. Run the MSSQLServer service under a domain account that has Readand Write access to the Sourcesafe database.3. Added the above user to Sourcesafe using the Administrator tool.4. Installed the VS6 Stored Procedure Version Control components onthe Server5. Enabled Version Control for Stored Procedures on the clientHowever when I right-click on the Stored Proc node in Server explorerI do not get any of the Sourcesafe menu options. They are not greyedout, they are simply not there!Any help would be appreciated.Alternatively if anyone has recommendations for other strategies orother tools to use for this purpose than Sourcesafe then this wouldalso be welcomeKarl
I create StringBuilder type forconcating string to create a lot of stored procedure at once However When I use this command BEGIN TRANSACTIONBEGIN TRY--////////////////////// SQL COMMAND ///////////////////////// -------------------- This any command --/////////////////////////////////////////////////////////// --COMMIT TRANEND TRYBEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;END CATCHIF @@TRANCOUNT > 0 COMMIT TRANSACTION; on any command If I use Create a lot of Tablessuch as BEGIN TRANSACTION BEGIN TRY --////////////////////// SQL COMMAND /////////////////////////CREATE TABLE [dbo].[Table1]( Column1 Int , Column2 varchar(50) NULL ) ON [PRIMARY]CREATE TABLE [dbo].[Table2]( Column1 Int , Column2 varchar(50) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY] --/////////////////////////////////////////////////////////// --COMMIT TRAN END TRY
BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION; It correctly works. But if I need create a lot of Stored procedure as the following code : BEGIN TRANSACTION BEGIN TRY --////////////////////// SQL COMMAND ///////////////////////// CREATE PROCEDURE [dbo].[DeleteItem1] @ProcId Int, @RowVersion Int AS BEGIN DELETE FROM [dbo].[ItemProcurement] WHERE [ProcId] = @ProcId AND [RowVersion] = @RowVersion END CREATE PROCEDURE [dbo].[DeleteItem2]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END CREATE PROCEDURE [dbo].[DeleteItem3]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END
--/////////////////////////////////////////////////////////// --COMMIT TRAN END TRY
BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION; It occurs Error ????? Please help me How should I solve them ?
Hi, I have an app where I am calling a stored procedure that runs and returns an integer. I would like to assign this integer to a control on the page. Here is a sample of code and below I will give the errors: Protected Sub NewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewButton.Click Dim tempconnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString) Dim tempproc As New Data.SqlClient.SqlCommand("SP_FetchProviders", tempconnection) Dim tempparam As New Data.SqlClient.SqlParameter tempproc.CommandType = Data.CommandType.StoredProcedure tempproc.Parameters.Add(New SqlParameter("@Agency_ID", Data.SqlDbType.Int)) tempproc.Parameters("@Agency_ID").Value = "8" 'Using a number just to test, will be populated by a session variable later tempparam = tempproc.CreateParameter() tempparam.ParameterName = "@OutValue" tempparam.Direction = Data.ParameterDirection.ReturnValue tempparam.SqlDbType = Data.SqlDbType.Int tempconnection.Open() tempproc.ExecuteNonQuery() Dim labelfill As String labelfill = tempproc.Parameters("@OutValue").Value tempconnection.Close() CType(Me.DetailsView1.FindControl("OmbudINSDDL"), DropDownList).Items.Add(labelfill) 'This is inside of a detailsview templatefield End Sub Here is the stored procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER procedure [dbo].[SP_FetchProviders] @Agency_ID intas Select Name, ProviderSubUnit_ID From ProviderSubunit a join Ombudsman b on a.contractproviders_id=b.contractproviders_id Where b.agency_id = @Agency_ID Order by Name This takes the @Agency_ID as input variable, and has returns an integer (according to SQL management studio). When I try to run my code I get the error that an sqlparameter with parametername @OutValue is not contained by this sqlparametercollection. This may be a simple question, but does the outvalue have to be declared in the stored procedure? If so, can you provide the right syntax? If not, can you offer a suggestion to populate the dropdownlist with the returned value? Thanks in advance.
Hi, I've a ListBox Web Server Control where I select a list of citiesnow I would like to create a stored procedure with the selected values... please note that I use metods like these for execute my Stored procedure public DataSet Getgfdfgh(SqlConnection connection, String IDAttivitaTipo, DateTime DataInizio, DateTime DataFine) { ConnectionState currState = connection.State; if (((connection.State & ConnectionState.Open) != ConnectionState.Open)) connection.Open(); try { SqlParameter[] parameters = new SqlParameter[3]; parameters[0] = new SqlParameter("@IDAttivitaTipo", IDAttivitaTipo); parameters[1] = new SqlParameter("@DataInizio", DataInizio); parameters[2] = new SqlParameter("@DataFine", DataFine); SqlCommand cmd = CreateStoreProcedureCommand("Getfhdfh", connection, parameters); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); return ds; } finally { if ((currState == ConnectionState.Closed)) connection.Close(); } } How Can I manage a list of values ??Thanks for help me!!
Wanted to know which among these options is better and why? Or if theircould be scenarios where we could opt for one of these.a) flags passed from code to control the execution of queries within astored procedure i.e. - where queries within a single stored procedureare controlled by flags passed to them.ORb) Break individual queries into separate stored procedure
I have defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.
Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0"Â get the following error (in German):
OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000. Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.
I have a variable @NetPay as type money, and a stored proc spGetNetPay. The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.
Now I need assgin output from spGetNetPay to user variable @NetPay. How can I do That?
Set @NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?
I have little knowledge about User defided function. Is is the way I should go?
Hi I am running a stored procedure which first puts the data in a temp table and then gives the output... the output is supposed to generate a report based on data from temp table
However when i run it, the first 2 statements are
(15345 row(s) affected)
(407 row(s) affected)
abd then the select statement runs...due to this, the report in ASP returns an error...does anyone know how i can suppress the first 2 lines and get only the actual data as output
When I run a script in query analyzer using a script (A "GO" statement exists after each SQL) I get the results on screen as soon as each query completes. When I run thru stored proc, I can get the result only after the whole procedure completes execution. Is there any way to get the outpout immediately as soon as each query completes? This will be useful in tracking thre progress of a stored proc. Thanks Satish
The procedure will write about 30,000 records or so and then quit writing to the file. Is there a setting I have to modify to write more records or is there a better way to do this?
Dear All,How can I show the resultrecords of a SP.I can be done by doubleclick the SPname?But how to do it by code.I want the following interfaceIn my form the user1 selects a SP (combobox showing a userfrinly name)2 adds the related parameters3 and then click the show result-buttonbut the .execute command doen't show teh records.I want the same output as you have doublclicking the SPname in theobjectwindow.Thanks,Filip
Hi every experts I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput....), after that, whatever I try, I click on Test Query Button at last part, I always get error message The Query did not return any data table. My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web? Thanks anyone, who can give me any advice. Satoshi
I have two stored procedures one generates an output parameter that I then use in the second stored procedure. 1 Try2 Dim myCommand As New SqlCommand("JP_GetChildren", myConn)3 myCommand.CommandType = Data.CommandType.StoredProcedure4 5 myCommand.CommandType = Data.CommandType.StoredProcedure6 myCommand.Parameters.Add(New SqlParameter("@ParentRule", Data.SqlDbType.NVarChar))7 myCommand.Parameters.Add(New SqlParameter("@PlantID", Data.SqlDbType.NVarChar))8 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRulePrefix", Data.SqlDbType.NVarChar))9 myCommand.Parameters.Add(New SqlParameter("@New_ReleasingRuleSuffix", Data.SqlDbType.NVarChar))10 myCommand.Parameters.Add(New SqlParameter("@New_PlantID", Data.SqlDbType.NVarChar))11 myCommand.Parameters.Add(New SqlParameter("@New_RuleSetID", Data.SqlDbType.NVarChar))12 myCommand.Parameters.Add(New SqlParameter("@Count", Data.SqlDbType.Int))13 myCommand.Parameters.Add(New SqlParameter("@IDField", Data.SqlDbType.NVarChar))14 15 Dim OParam As New SqlParameter()16 OParam.ParameterName = "@IDFieldOut" 17 OParam.Direction = ParameterDirection.Output18 OParam.SqlDbType = SqlDbType.NVarChar19 myCommand.Parameters.Add(OParam)20 21 22 myCommand.Parameters("@ParentRule").Value = txtParentRule.Text23 myCommand.Parameters("@PlantID").Value = txtStartingPlantID.Text24 myCommand.Parameters("@New_ReleasingRulePrefix").Value = txtReleaseRuleFromPrefix.Text25 myCommand.Parameters("@New_ReleasingRuleSuffix").Value = txtReleaseRuleFromSuffix.Text26 myCommand.Parameters("@New_PlantID").Value = txtEndingPlantID.Text27 myCommand.Parameters("@New_RuleSetID").Value = txtEndingRuleSetID.Text28 myCommand.Parameters("@Count").Value = 129 myCommand.Parameters("@IDField").Value = " " 30 myCommand.Parameters("@IDFieldOut").Value = 031 32 myCommand.ExecuteNonQuery()33 34 Dim IDField As String = myCommand.Parameters("@IDFieldOut").Value35 If i run this stored procedure in sql it does return my parameter. But when i run this code IDField comes back null. Any ideas
HiI have this code snippet[CODE] string connstring = "server=(local);uid=xxx;pwd=xxx;database=test;"; SqlConnection connection = new SqlConnection(connstring); //SqlCommand cmd = new SqlCommand("getInfo", connection); SqlDataAdapter a = new SqlDataAdapter("getInfo", connection); a.SelectCommand.CommandType = CommandType.StoredProcedure; a.SelectCommand.Parameters.Add("@Count", SqlDbType.Int).Value = id_param; DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { Console.WriteLine(dr[0].ToString()); }[/CODE] When I seperately run the stored procedure getInfo with 2 as parameter, I get the outputBut when I run thsi program, it runs successfully but gives no output Can someone please help me?
I have a stored procedure that inserts a new record, and returns the ID value for the newly inserted record. The procedure works fine, but I'm unable to get that return value in my code. Here's what I have: IF OBJECT_ID ( 'dbo.dbEvent', 'P') IS NOT NULL DROP PROCEDURE dbEvent GO CREATE PROCEDURE @Name varchar(200) ,@Location varchar(200) AS INSERT INTO Event ( NAME , LOCATION ) VALUES ( NAME , @LOCATION ) SELECT SCOPE_IDENTITY()
And my code behind: public Int64 Insert(SqlConnection Conn) { try { using (SqlCommand Command = new SqlCommand("dbo.dbEvent", Conn)) { Command.CommandType = CommandType.StoredProcedure; Command.Parameters.Add("@ID", ID).Direction = ParameterDirection.Output; Command.Parameters.Add("@NAME", SqlDbType.VarChar, 200).Value = PermitName; Command.Parameters.Add("@LOCATION", SqlDbType.VarChar, 200).Value = Location; if (Conn.State != ConnectionState.Open) Conn.Open(); Command.ExecuteNonQuery(); Int64 _requestId = Convert.ToInt64(Command.Parameters.Add("@ID", SqlDbType.BigInt).Value.ToString()); return _requestId; } I'm getting the error that I have "Too many arguments specified" in my Insert() method. When I test the procedure in Query Analyzer, it works fine and returns the correct value. Any suggestions? Don't I need to declare that return value in my .NET code as an output parameter?
i built a stored procedure with inserting in to customers table. i have one column with identity. so want to take that identity column value in the same stored procedure. so how can i write that procedure with insert in to statements in that stored procedures.
can any one tell me. also how to get that value in ado.net 2.0. friends please tell me.
I've an existing SQL 2000 Stored Procedure that return data in many (~20) output parameters.
I'm starting to use it in a .Net c# application and it seems to insist that I setup all the output parameters: SqlParameter param = cmd.Parameters.Add("@BackgroundColour",SqlDbType.TinyInt); param.Direction=ParameterDirection.Output; even if I only need the value of a single one. Is this right? Is there a way to avoid coding every one every time?
Hi,Got an annoying SQL / .net error. I've created a stored procedure to take in an ID and map this to an ID table and get another ID out. So i have one ID in and one out. I get the following error:Procedure 'sp_SvrMapping' expects parameter '@rID', which was not supplied.Any idea why this happens? surely the output ID doens't need to be defined before its retrived? If so how?Thanksdrazic19p.s. code for both below.**************************************************CREATE PROCEDURE dbo.sp_SvrMapping ( @uID int, @rID int OUTPUT ) As SELECT @rID = rID FROM tbl_mapping WHERE uID= @uIDGO************************************************** Dim conStr As String = ConfigurationSettings.AppSettings("str_connAuth") Dim myConn As New SqlConnection(conStr) Dim myComm As New SqlCommand("sp_SvrMapping", myConn) Dim test As String = "1" myComm.CommandType = CommandType.StoredProcedure myComm.Parameters.Add("@uID", CInt(test)) myConn.Open() myComm.ExecuteNonQuery() <--- error called If Not IsDBNull(myComm.Parameters("@rID").Value) Then MappedServerAccount = myComm.Parameters("@rID").Value End If myConn.Close()
EXEC('SELECT COUNT(docid) AS Total FROM docs WHERE ' + @QueryFilter)I want to get the cound as an output parameter.I can get output parameters to work only when I dont use EXEC. I need to use EXEC for this case since @QueryFilter gets generated in the stored procedure based on some some other data.How can I get that count using ouput parameter?
I'm using stored procedures in SQL7 to return a list of items to a website where they are presented. A new requirement is to present these items in a random order on the website. I have two approaches to this one, either making the result from SQL7 random or presenting the ordered results randomly. Since the presentation logic is used several times (different country subsites), it would be the easiest way to get a randomized result from the stored procedure, which would mean that I only have to change the procedure.
Any thoughts on how to accomplish this, that is returning a list of items but in random order, so that the presentation sequence would be different for each access to the website ??
Any help would be appreciated : )
Thanks!
Eirik Kjølsrud Systems Developer, Apropos Internett - Norway
I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.
I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.
create procedure ShowPopStyle @style varchar(30), @artistname varchar(30) output as select @artistname = artistname from artists where style = @style go
Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?
declare @returnname varchar(30) -- variable for the output from the procedure exec showpopstyle 'Pop', @artistname = @returnname output print @returnname
I should validate a USER and if the user is not a valid user it should return a message to the front end application for this I have a procedure like this .
Basically I need to pass an output parameter to the front end application returned by this procedure.
Create procedure test @userid VARCHAR(20) AS Declare c1 cursor AS Select userid from USERS_TBL
Open Cursor C1 FETCH NEXT from C1 INTO @Temp While @fetch_status = 0 BEGIN If @Temp <> @Userid -- IF the USER is not a valid USER ...It should not execute the Futher code(statement) it should exit the procedure by returning the error statement. --- --- --- FETCH NEXT from C1 INTO @Temp END
can we use output parameter with insert query in stored procedure. suppose we insert a record and we want to get the id column of this record which is identity column. we want to get this value in output parameter. ie insert record and get its id column value in output parameter.
I have a stored procedure (named Insert_SRegister) that calls another stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one. The program goes like this: The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored procedure.
The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission;
As Begin Declare @Exists Int, -- Return Value @StudentCode VarChar(50)
----------------------------------------- -- here I call the second proc and assgin it output to variable --@StudentCode ----------------------------------------------------------------------------------- exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT
----Do work and insert info in db
End
Code of the second stored procedure is: ALTER Procedure dbo.Generate_Student_Code
@YGroup VarChar (3), @YDate VarChar (4), @newID VarChar (50) OUTPUT As Begin set nocount on Declare @Exists int, -- Return Value @Mv varchar(5), @IdLen int If Exists(Select SID From SRegisteration) Begin select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration Set @IdLen = DATALENGTH(@Mv)
Set @Mv = case when @IdLen = 1 then '0000' + @Mv when @IdLen = 2 then '000' + @Mv when @IdLen = 3 then '00' + @Mv when @IdLen = 4 then '0' + @Mv else @Mv end
Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv ---------Here I return the ID value select @newID -------------------------------------- Select @Exists = 1
End
Else
Begin Select @Exists = 0 Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001' ---------Here I return the ID value select @newID ------------------ Return @Exists End
I've a large number of stored procedures that output result sets, as part of a test script it would be useful for me to be able to save the results from these stored procedures into temporary tables, without having to pre-create these tables, i.e. use the equivalent of a select * into statement.
I am calling a procedure with two input parameters and one output parameter (third) in the following format. I'm receiving a database error on the return parameter. *result. What is the correct syntax for an output parameter.
I am new to SQL server - Would appreciate any help.
I have a stored procedure (A) that creates a temp table with a single field of varchar(1000).
I populate this table with string data and with contents of another table obtained via the use of a cursor.
In a nutshell I am creating an XML document representation where each line of the temp table is a line of the xml document.
At the end of the stored procedure I select all of the fields from the temp table.
I have another stored procedure (B) that uses xp_sendemail and uses stored procedure (A) as the query for the attachment.
The problem is that the attachment appears to contain all unicode characters and will not open in windows explorer. It will open in XML SPY and other XML editors.
If I look at the data in a simple editor such as editplus, I see that each chracter is followed by a null character which is why I am assuming it is unicode.
Any idea how to get the select output / email attachment to exclude these null characters?
