TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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 ?
Reply
Answers (
6
)
3
Manas Mohapatra
91
20.4k
16.7m
Oct 26 2016 12:56 PM
Try like this:
SELECT
um.UserName,
COUNT
(ut.ID) TotalTran,
CASE
WHEN
COUNT
(ut.ID) > 0
THEN
'TRUE'
ELSE
'FALSE'
,
MAX
(ut.Certifiedon)
as
[
Date
]
FROM
UserMaster um
INNER
JOIN
UserTransactions ut
ON
um.UserName = ut.UserName
GROUP
BY
um.UserName
2
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
0
2.5k
804.7k
Oct 27 2016 12:51 AM
SELECT
a.USERNAME, b.Total_Transactions,
case
when
b.Certified_Status >0
then
'true'
else
'false'
end
,b.Date_on
FROM
EMPLOYEE_MASTER a
left
join
(
SELECT
USERNAME
as
USERNAME1,
count
(*)
as
Total_Transactions ,
sum
(CERTIFIEDSTATUS)
as
Certified_Status,
max
(CERTIFIEDON)
as
Date_on
FROM
EMPLOYEE_MASTER1
group
by
username) b
on
a.USERNAME=b.USERNAME1;
try this
0
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
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
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
81
23.9k
3.5m
Oct 26 2016 2:34 PM
Yes manas answer is corrcet.
sql server table
Log Shipping in SQL