i am a new bee in SQL Server 2005. I have a stored procedure that accept two parameter , variable @ColumnName as column name and @SearchText as search text.
My stored procedure is written as below:
[Code]....
but the error i get is "The 'usp_GetPlantTest' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead."
ALTER procedure [dbo].[Sample2]
@Col as nvarchar(1000),
@Value as nvarchar(1000)
[code]...
I am trying to write a function that can be called to run a stored procedure. I pass the stored procedure name, followed by as many parameters as I need to run the procedure. I am able to do this by using the params keyword, so my function looks something like this;
[Code]....
How can I determine what the data type of the parameter is? Maybe I need to alter the string[] part, above?
I had a little problem with the value that one of my SPs returns. In my SP, I create some temporary tables to store certain data from multiple tables and contains some queries for an advanced calculationBut precisely yesterday, the value that the SP returns was incorrect. I tried to look at the SP and the data again and everything was OK, but not the result from the SP.And what the heck!!..today the value is totally right ...I truly have no idea what's wrong with the SP..Is there anyone knows what the root of cause is?
View 4 RepliesWhat am i missing or doing wrong here, when i debug, im seeing the selected value in the code behind, but when the page loads, it says the following:
Procedure or function 'Onsite_Report_Procedures' expects parameter '@rptNum', which was not supplied.
<asp:SqlDataSource
ID="SQLOnSiteReport"
runat="server"
ConnectionString="<%$ ConnectionStrings:connectn %>"
SelectCommand="Onsite_Report_Procedures"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="@rptNum" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
[Code]....
I am goig to execute an oracle store procedure using C# through through ODP.NET. The store procedure executes succsfully but it does not return any value in the out parameter.
When I execute the same store procedure with the same input parameteres from within SQL*Plus. It does return values.
This is to be clearify here that in the oracle store procedure the data type for the out parameter is number and in the c# the variavle that will hold its value is Integer. I also tried using Long on the C# side but it is still not working. After the successfull execution of the store procedure the C# variable contains nulll value.
this is my code:
[Code]....
some of my col datatype is nVarchar...
in run time because my value in my table (dt_excel_allRows) are FARSI ; the sequence of my parameter changed and it cant be executed!!!!!
of course the problem is because of FARSI is rtl languges....
i can not user sqlPArameter becuse of some reason....
i think some thing like rtl or ltr can fix my problem....but how?
I have a sql which the data read from the store procedure is very dynamic.
i have finish the first part of the sql in stored procedure but having problem in executing it.
[Code]....
How do i use EXECUTE SP_EXECUTESQL to do this??
I want to execute a Store Procedure and then get the Output in one table. However I am not getting the query. I want something like this execute 'Stored procedure name' 'param1','param2','param3' and then get the result into some table. Is this possible?
select * into #table from (execute 'Stored procedure name' 'param1','param2','param3')
storeprocedure code:
ALTER PROCEDURE sp_CheckForDuplicates
(
@UserName VARCHAR(50) = NULL,
@FirstName VARCHAR(50) = NULL,
-----------------------------
Error show :if (objReturnPara>0)
[Code]....
operator '>' cannot be applied to operands of type 'object' and 'int'
operator '>' cannot be applied to operands of type 'object' and 'int'
I am using SqlDataSource control to query my data via store procedure. Based on which option the user picked from the RadioButtonList control, I want to dynamically at run time to choose which store procedure gets executed. How do I do this? I am already using Query String for my parapmeters. I can could use this for the store procedure as well. The problem is, how do I get the SelectCommand to pick the store procedure name from the parameter passed in the URL?
View 7 RepliesWhen I try to create a stored proc in SQL Server Express 2008 by right clicking on the Stored Procedures folder of my database and selecting New Stored Procedure, then attempt to save, it saves the procedure as a SQL query in the projects folder instead of saving it as a stored procedure in the Stored Procedures folder of my database.
View 2 Replieshow can i pass a session variable in a C# web form to a stored procedure..?
View 1 RepliesI have stored procedure that increments a number. I am supposed to use the newly generated number and insert it into a table. My question is how do I execute the stored procedure, with a hardcoded value of 3 that it expects as a paramter, receive the number it generates as the output parameter and use it in the insert statement.
Here is the stored procedure code:
CREATE PROCEDURE [dbo].[Generate]
@myid int,
@next_no int output
AS
update mytable
set mynumber=mynumber + 1, @next_no = number
where myid=@myid
RETURN
Here is my insert statement, that I tried and it failed:
EXEC @GetNo = Generate '3'
Insert into customers (custid, name, phone ) values
( @GetNo, 'MSFT', '800-345-5678')
I keep getting this error:
Procedure 'cnpcpiFarmFormAssignField' expects parameter '@chUserId', which was not supplied.
[Code]....
Here's a stored procedure I created that uses another stored procedure inside it to return a value:
[Code]....
Note the 'exec dirinfo.dbo.d_searchempbyname @TicketSubName' part. That stored procedure will return a single record. What I need is to get a single column from that recordset (in this case, the email address of the employee), and store that value in a variable.
I have a Datatable and I want to send it in SqlServer as Parameter in stored procedure.
1. How i send datatable
2. How I use that datetable in sqlserver to read its data.
How do you call a stored procedure using C# .NET 3.5 that contains a parameter that is of type hierarchyid in SQL Server 2008?
A basic outline of my current code is as follows:
SqlConnection conn =
new
SqlConnection(DatabaseConnectionString);
SqlCommand StoredProcedureCommand =
new
SqlCommand("AddUser", conn);
StoredProcedureCommand.CommandType =CommandType.StoredProcedure;
SqlParameter ParamUsername = StoredProcedureCommand.Parameters.Add("@Username",
SqlDbType.NVarChar);
SqlParameter ParamPassword = StoredProcedureCommand.Parameters.Add("@Password",
SqlDbType.NVarChar);
SqlParameter ParamMasterNode = StoredProcedureCommand.Parameters.Add("@MasterNode",
SqlDbType.Text);
// HierarchyID type
ParamDynamboUsername.Value = Username;
ParamPassword.Value = Password;
ParamMasterNode.Value = "/";
conn.Open();
StoredProcedureCommand.ExecuteNonQuery();
conn.Close();
I am using DAAB with stored procedures. I use stored procedures more then once if possible. If I add a parameter later in stored procedure, I have to supply value for this in functions where it is even not being used as well, other wise it shows an error that number of parameters are not same. if I add parameter in last of parameter list in stored procedure and set a default value for it and I may have not to provide value for this from functions where I am not using it.
View 1 RepliesMy insert is adding a record to the database in the call to the stored procedure in SQL Server, but a -1 is returned - Shouldn't it return 0 if the insert/update was successful?
Result = myCommand.ExecuteNonQuery();
I spent about 60 minutes sifting through search engine listings trying to figure this out and couldn't find an answer, so I decided to make this post for others.
Situation: Using a Stored Procedure to insert a row and return the new row ID, explained here. In my case, I used the Idenity_Scope to return the new row, like so:
[Code]....
Next, following steps as described in link above, went into the dataset XSD page, configured the table adapter to use this stored procedure. By default, it wanted my return value to be set to some value, but I set to allow Null (makes sense, since I don't want to pass the value in, I want to get it out, so it should be null to begin with).
Problem: Where I ran into an issue was in the method to call this stored procedure. The intellisense displayed the prototype it was expecting, basicly like this:
[Code]....
I have an sp that I would like to us the data from one column to use as a parameter for the following Select Statement
[Code]....
need to set the @customerID parameter = customer_data.CustomerID from the first Select Statement
I want to pass the Array ( object[]) or Structure data type value to the store procedure through
ibatis .net. I can do same by commandtext using SQLDBType.Structure. I am using Table value function in sql server 2008 and .Net 2010.
[URL]
I have setup a job in SQL Server Agent which run a store procedure.
[Code]....
After the job excuted, the temp table have not been created.
If I type "exec data_syn_ClearAllEposData". The temp table has been created.
Global Temp table cannot create in SQL SERVER AGENT?
i want to return two output parameter n temp table from sql server stored procedure
View 6 Replies