.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Function with output parameter

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server


is it possible to create user define function with 3 output parameter. something like ref out parameter in C#.

Best Regards. Morteza


View Complete Post

More Related Resource Links

Retrieve function's output parameter




Is it a way to retrieve the particular function's list of output parameter fields?

the following is the sample code for the list of input parameter fields.

  FROM sys.objects vw
  INNER JOIN sys.parameters p ON p.object_id = vw.object_id
  WHERE vw.type IN (N'FN', N'IF', N'TF') and is_ms_shipped = 0


Meng Chew

Enterprize library 4.1 getting output parameter after adding record in db, plz guide



I need to get output parameter (flag) from db after saving record in database. I am using Microsoft Enterprise Library 4.1 for DB. I am inserting record using I Data reader.

Please guide


output cache to be used only when parameter has changed + old question of user control not answered



i have 2 questions about output cache:

1.is it just me , or does it seems that it is used only after the second time that the user reaches the webpage? if so, why, and how can i tweak it to my needs? my guess is that it wouldn't be logical to cache every time a user reach a webpage, but only when it happens enough times.

2.i know that i can use "varybyparam" for using the output cache when the parameter doesn't change , but it seems that it would use the output cache even if there is no parameter , or if the specified paramter is not there. is there a way to overcome this?

3.an old question that somehow marked as "answered" , yet i didn't solve : suppose i created a user control and i gave it values through the CTOR via the aspx file . is it possible to show the values from within the split/design mode? remember that i've talking about ASP.NET and not simply C# winforms . i write this because this question was on :


and the answer that i got was for C# and winforms . i've tried to do the same for ASP.NET , but i've failed.

can anyone please help me?

optional output parameter in CLR Stored Procedure

I'm trying to transpose a Procedure that sends mail via CDO.Message and Ole Automation to a .Net CLR Procedure. this procedure must have the same parameters in the same order as the original to maintain compatibility with everything that already uses it. the declaration goes as follow   <Microsoft.SqlServer.Server.SqlProcedure(name:="SP_ENVIAEMAIL")> _<br/> Public Shared Sub ENVIAEMAIL(<SqlFacet(MaxSize:=100)> Optional ByVal De As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Para As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Copia As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Oculta As String = "", _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Assunto As String = "", <SqlFacet(MaxSize:=-1)> Optional ByVal Mensagem As String = "", _<br/> <SqlFacet(MaxSize:=-1)> Optional ByVal Anexo As String = "", _<br/> <strong><SqlFacet(MaxSize:=1000)> <Out()> Optional ByVal Retorno As String = ""</strong> , _<br/> <SqlFacet(MaxSize:=100)> Optional ByVal Smtp As String = "", <SqlFacet(MaxSize:=100)> Optional ByVal Usuario As String = "",

output parameter sp/c#

 Hi I have a problem getting my code right. I got "String[1]: the Size property has an invalid size of 0." in my c# code. SP:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE sp_GetUserName ( @UserNames UserNamesType READONLY, @tmpUserNames nvarchar(max) OUTPUT) AS BEGIN SET NOCOUNT ON; DECLARE @tmpUser nvarchar(max); SELECT @tmpUser = (FirstName + ' ' + LastName + ',') FROM Users WHERE [Login] = (SELECT UserName FROM @UserNames) SET @tmpUserNames = @tmpUser RETURN END GO   public static string GetUserName(DataTable dtUser) { string err = String.Empty; string userResult; string sqlText = "sp_GetUserName"; try { using (SqlCommand cmd = new SqlCommand(sqlText, connect.GetSqlConnection(out err))) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@UserNames", SqlDbType.Structured).Value = dtUser; cmd.Parameters.Add("@tmpUserNames", SqlDbType.Text).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); userResult = (string)cmd.Parameters["@tmpUserNames"].Value; return userResult; } } catch (SqlException ex) { return null; } } Can someon

SQL Server 2008 Stored Procedure output parameter

I would like to get OUTPUT parameter names from stored procedure without executing stored procedure.  for excample schema API will help to get input parameter metadata. Thanks in advance. Murali

How can i pass function as parameter to show in gridview

Hello, I have one common Data Access function which returns output as dataset which is as below   Public Shared Function ExecuteDataSet(ByVal sql As String, ByVal type As CommandType, ByVal ParamArray parameters As SqlParameter()) As DataSetDim RetVal As Object = Nothing   Try Cmd = Cmd.Connection = OpenConnection() Cmd.CommandType = type Cmd.CommandText = sql Cmd.Parameters.AddRange(parameters) Sda = Ds = Sda.Fill(Ds)       New SqlCommand()New SqlDataAdapter(Cmd)New DataSetCatch ex As ExceptionThrow New Exception(ex.Message.ToString())Finally CloseConnection() Sda.Dispose() End Try   Return Ds End Function   this function normally i use to get data from database and returns to dataset   now,I want to created one more function which output type is gridview and its parameter is  dataset which i got output in previous funtion   i want like this public funtion BindDatatoGridView(ExecuteDataSet("SELECT...",commandType.text,Parameter....)) as GridView End Function   how can i solve this, please help me out

SP Output parameter help

Hi,I hope someone can help me with sorting this out... I'm trying to build a stored procedure that returns multiple members' names when fed their memberIds.So far this is what I've got - it works fine with one exception - I'm not sure how to write the syntax so I can get the output parameter, @MemberNames? The code below outputs the names but without a column heading. I just want the names to be a string in the output parameter @MemberNamesCREATE PROCEDURE [dbo].[sp_NameResolution] --Add the parameters for the stored procedure here @ProfileId numeric, @MemberIds varchar(8000), -- this might be a string of ids numbers like 34,56,345 @MemberNames varchar(8000) Output, @SQL AS Varchar(8000) = NULL AS BEGIN SET NOCOUNT ON; SET @SQL = 'SELECT (RTRIM(Fname) + '' '' + RTRIM(Lname)) FROM MEMBERS WHERE (MemberIds IN (' + @MemberIds + ')) AND (ProfileID = '+ CAST(@ProfileId AS VarChar)+ ')' PRINT @SQL EXEC(@SQL) END I've tried different variations like..SELECT @MemberNames = (RTRIM(Fname) + '' '' + RTRIM(Lname)) ... etcbut because I'm executing it using EXEC(@SQL) it has a cow and won't output the parameter...Any ideas?Thanks a bunch,Jason

Report Parameter Values - Can I use getdate() or now() or any other function?

Hi all,      I have a report that utilizes a calendar function but I need to make it a subscription report. Is it possible to use getdate() or now() on the Report Parameter Values? I have a text box that says Enter a Date and that is where I want to have the default value to be run with the subscription. Please let  me know if possible. Thank you.

Rounding problem for money output parameter from stored proc

I have a GridView sourced by a SQL Server stored procedure with several databound fields, including a money column for item values. My stored proc returns the data through a SELECT and everything looks good. The stored proc also has a single output parameter that sums the total value of the displayed data, and this value is displayed in a label above the GridView. The output parameter (defined as money on the SQL Server side) is passed back to the app in good shape, but is rounded when displayed in the DataSource_Selected event handler. So a total of $30,155.22 in SQL Server shows up as $30155 on my aspx.In debugging, I have confirmed that the value leaving SQL Server is correct (30155.2200), and I can see that the value coming IN to the ASP-side event handler is already rounded. What do I need to do here to coerce/force it to show as money/currency?Relevant snippets:Stored Proc:... @onHandValue money OUT...  CREATE TABLE #tempValueOnHand(... valueOnHand money )... set @onHandValue = (select round(sum(valueOnHand), 2) from #tempValueOnHand) set @onHandValue = (select sum(valueOnHand) from #tempValueOnHand)And again, the output parameter @onHandValue is coming back, just already rounded.On the ASP side:    Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourc

Exposing data contract without passing as input or output parameter for any operation contract


I have a method in  wcf which returns stream of custom object. I need to deserialize this stream at client side, but my custom class which is exposed as data contract is not visible through client proxy. When I add a dummy operation contract which returns the custome object I am able to get this custom class. I dont want to write this dummy method as there are many such custom classes.

Is there any way to expose data contract without writing any operation contract that returns or accepts data contract type object?





Can MVC attributes with a string parameter be fed a function returning a string?


Ok so take for instance this attribute:

[PropertiesMustMatch("NewPassword", "ConfirmPassword", ErrorMessage = "La nuova password e la password di conferma non corrispondono.")]

You can see that the third parameter is a string that I would want to localize, hence I would like to delete that message and replace it with a function call, say something along the lines of this:

[PropertiesMustMatch("NewPassword", "ConfirmPassword", ErrorMessage = GetLocalization("strErrPassMustMatch")]

Do you think it would be possible? If I got *any* chance to do it, then good, I will be setting up a system to do this :-P otherwise, I'll have to find some other way :-)

What can you tell me?

Thank you!


Exposing data contract without passing as input or output parameter for any operation contract


I have a method in  wcf which returns stream of custom object. I need to deserialize this stream at client side, but my custom class which is exposed as data contract is not visible through client proxy. When I add a dummy operation contract which returns the custome object I am able to get this custom class. I dont want to write this dummy method as there are many such custom classes.

Is there any way to expose data contract without writing any operation contract that returns or accepts data contract type object?

How to get total record count in an OUTPUT Parameter


I couldn't get the total record count in a, in my stored proc, in a given scenario. Please help


My SP looks like this:

create proc RecordCount

       @CustomerID varchar(10),

       @RecCnt int OUTPUT


begin tran

if(CustomerID = 'ABC')

@RecCnt = select count(*) from Customers where CustomerID='ABC'



begin tran


@RecCnt = select count(*) from Customers where CustomerID='XYZ' and TranDate > '05/01/2009'




-- This is how I called my SP

declare @rc int

exec RecordCount 'XYZ', @RecCnt = @rc OUTPUT

select @rc

Capturing SQL OUTPUT parameter


Hi All,

I'm trying to capture an output parameter from an SQL insert.


CREATE TABLE [dbo].[Test](
 [TestID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
 [Test1] [int] NULL,

INSERT INTO [Test] ([Test1]) OUTPUT inserted.TestID as TestOut VALUES 1))  works fine in SQL Server and throuws no errors asp.net, bu tI can't find a way to capture TestOut.

I create a label and ds:

<asp:Label ID="lbl_ID" runat="server" Text=""></asp:Label

<asp:SqlDataSource ID="ds_Ins2" runat="server" ConnectionString="<%$ ConnectionStrings:cs_StatusBuildTest %>" 
        InsertCommand="INSERT INTO [Test] ([Test1]) OUTPUT inserted.TestID as @TestOut VALUES (@Test1))"  oninserted="ds_Ins2_Inserted"
        SelectCommand="SELECT [TestID], [Test1] FROM [Test]" 
            <asp:Parameter Name="Test1" Type="Int32" />

Invalid length parameter passed to the SUBSTRING function


Hi all,

I am having a weird issue after we upgraded our DB server to SQL 2005.

I have a SP used to extract exchange rate, and a job calls this SP daily. This job worked fine on SQL 2000, and works very well in Management studio if I call this SP seperately, but failed in sql job in 2005.

The error statement pointed to:

select left(@row, charindex(',', @row)-1),  REVERSE(left(@reversedrow, charindex(',', @reversedrow)-1))

The error message is:

Invalid length parameter passed to the SUBSTRING function.

Anyone knows what's the difference for LEFT function between sql 2000 and 2005?


sql average function giving zero output



WHAT sould i edit imn my QUERY to get below required output..I TRIED SO MANY OPTION BUT IT DIDNT WORK..PLEASE HELP

i have table name "report" which has 4 column 3 are time T1,T2,T3 AND FOURTH IS MONTH

T1  T2  T3  MONTH

2   2    1         1

3   2    2         1

2   1   6          2

3    4   6         2

1    2   1         2



.31               1

.68               2

if i use query select sum(t1+t2+t3)as sum,month(reportdate) from report group by month(reportdate)

i get the output

sum month

12      1

26      2

but if i use query"select  avg(t1+t2+t3) as average ,month(reportdate) as month from r

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend