Shubham Srivastava

Shubham Srivastava

  • NA
  • 8.7k
  • 2.3m

Differentiate Multi statement and Inline table valued function in SQl server

Jul 15 2012 2:48 AM
Differentiate Multi statement and Inline table valued function in SQl server ?

Answers (6)

3
Manas Mohapatra

Manas Mohapatra

  • 91
  • 20.4k
  • 16.7m
Oct 26 2016 12:56 PM
Try like this:
  1. SELECT um.UserName, COUNT(ut.ID) TotalTran,  
  2. CASE  
  3. WHEN COUNT(ut.ID) > 0 THEN 'TRUE'  
  4. ELSE 'FALSE',  
  5. MAX(ut.Certifiedon) as [Date]  
  6. FROM UserMaster um  
  7. INNER JOIN UserTransactions ut  
  8. ON um.UserName = ut.UserName  
  9. GROUP BY um.UserName  
 
2
Jignesh Trivedi

Jignesh Trivedi

  • 0
  • 62.3k
  • 46m
Oct 27 2016 4:42 AM
After ExecuteNonQuery method of the command you will get the value of the output parameter. Please refer following link for better idea
http://www.aspsnippets.com/Articles/How-to-return-Output-parameter-from-Stored-Procedure-in-ASPNet-in-C-and-VBNet.aspx
1
Ananth G

Ananth G

  • 0
  • 2.5k
  • 804.7k
Oct 27 2016 12:51 AM
  1.  SELECT a.USERNAME, b.Total_Transactions, case when b.Certified_Status >0 then 'true' else 'false' end,b.Date_on FROM EMPLOYEE_MASTER  a  
  2.  left join   
  3.   
  4. (SELECT USERNAME as USERNAME1,  
  5.  count(*) as      
  6. Total_Transactions ,sum(CERTIFIEDSTATUS) as Certified_Status,max(CERTIFIEDON) as Date_on  
  7. FROM EMPLOYEE_MASTER1 group by username) b  
  8. on a.USERNAME=b.USERNAME1;  
 
 try this
 
0
Riddhi Valecha

Riddhi Valecha

  • 441
  • 3.3k
  • 416.5k
Oct 27 2016 1:56 AM
Hi..Yes - Getting date in output parameter is for this query-
My CS File -
SqlParameter MinDateOut = cmd.Parameters.AddWithValue("@MinDateout", SqlDbType.NVarChar);
MinDateOut.Direction = System.Data.ParameterDirection.Output ;
SqlParameter MaxDateOut = cmd.Parameters.AddWithValue("@MaxDateout", SqlDbType.NVarChar);
MaxDateOut.Direction = System.Data.ParameterDirection.Output;
--------
Stored Procedure -
@MinDateout varchar(max) output ,
@MaxDateout varchar(max) output
select @Mindateout = convert(varchar(max), min(t.CertifiedOn),106) from TBLASSETCERTIFICATIONDETAILS t

select @MaxDateout = convert(varchar(max), max(t.CertifiedOn),106) from TBLASSETCERTIFICATIONDETAILS t
--------
Error - Error in converting varchar to int.
0
Nitin Sontakke

Nitin Sontakke

  • 135
  • 13.6k
  • 14.9k
Oct 26 2016 11:29 PM
I am hoping that another question asked by you is not related to the original question.
 
If yes, there are couple of ways to go around it and I try my best not to use output parameters at all.
 
1/ In the stored procedure, just select both the variables as:
 
select @minDateOut [MinDate], @maxDateOut [MaxDate]
 
and use execute reader in C# and you will get just one record with two columns.
 
2/ Another way is to combine two strings as:
 
select @minDateOut + '|' + @maxDateOut [ReturnValue]
 
and use execute scalar in C# and you will get just one string which is pipe separated. Just split it and you will get two dates in an array. Simple.
0
Riddhi Valecha

Riddhi Valecha

  • 441
  • 3.3k
  • 416.5k
Oct 26 2016 4:32 PM
One more query - How to get minimum and maximum date in output variable (varchar(max)) from stored procedure to asp.net c# ?
My Stored Procedure -
@MinDateOut varchar(max) output ,
@MaxDateOut varchar(max) output
Select @MinDateOut = convert(min(t.CreatedOn),106) from UserTransactions t
Select @MaxDateOut = convert( max(t.CreatedOn), 106) from UserTransactions t
CS File-
SQLParamenter MinDate = cmd.Parameters.Addwithvalue("@MinDateOut",SQLDBType.varchar);
MinDate.Direction = Output;
----------
Error - Error converting datatype string to int
---
Please guide..
 
I also tried -
 
set @Mindateout = ( select convert(varchar(max), min(t.CertifiedOn),106) from UserTransactions t )

set @MaxDateout = (select convert(varchar(max), max(t.CertifiedOn),106) from UserTransactions t ) 
 
---
Error -
Source = ".Net SqlClient Data Provider"
Message = "Error converting data type varchar(max) to int."
 
Please guide ... its a bit urgent... Thanks in advance...
0
Bikesh Srivastava

Bikesh Srivastava

  • 81
  • 23.9k
  • 3.5m
Oct 26 2016 2:34 PM
Yes manas answer is corrcet.