Getting a return value from a Stored Procedure

Hi all,
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...

DECLARE @.testval AS INT
SET @.testval = EXEC u_checknew_dwi_limits '163'
IF (@.testval = 0)
BEGIN
PRINT '0 Returned'
END
ELSE
BEGIN
PRINT '1 Returned'
END
...whichas you can see calls a SP called 'u_checknew_dwi_limits'. This SP(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want toassign that value to the '@.testval' variable (as you can see in mycode) - but Query Analyser is throwing an error at me. Is this thecorrect way to do this?
Thanks
Tryst

The SP that you are calling should contain an OUTPUT parameter.
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards

|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst

get value from SQL server 2005 select statement with datareader

I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db.

The code below should work in, but email[calc]= rdr[0].ToString(); when i want to read some data a get a exception saying:

System.InvalidOperationException was unhandled by user code
Message="Invalid attempt to read when no data is present."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
at _Default.Login_Click(Object sender, EventArgs e) in d:\My Documents\Visual Studio 2005\WebSites\WebSite1\Default.aspx.cs:line 47
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

If anybody could advise me where my stupid mistake is then i would highly appreciate it!

SqlConnection conn = new SqlConnection(getConnection());
SqlDataReader rdr = null;

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "SELECT * FROM Customer";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;

try
{
conn.Open();

rdr = cmd.ExecuteReader();

int calc = 0;

Boolean login = false;
string[] email = new string[100];
object[] password = new object[100];

while (rdr.HasRows) // or rdr.Read();
{
rdr.Read();
email[calc]= rdr[0].ToString();
password[calc] = rdr["Password"].ToString();
if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc]))
{
login = true;
}
calc++;
}

}
finally
{
rdr.Close();
}

thanks...

Try this:

if (rdr.HasRows)// or rdr.Read(); {while(rdr.Read()) { email[calc]= rdr[0].ToString(); password[calc] = rdr["Password"].ToString();if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc])) { login =true; } calc++; } }

Hope this helps.

Get the value of a testbox

Hello, I built a report which returns some calculated fields and I would like to know if it's possible to use the returned values in another field.

Exemple:
textbox10 = Fields!MyColumn.Value
textbox11 = Fields!MyOtherColumn.Value

I would like to know if there is a way to do something like this:
textbox12 = textbox10 + textbox11

Has anyone any idea? Thanks in advance for your help

Use the ReportItems collection i.e.

Code Snippet

textbox12 = ReportItems!textbox10.Value + ReportItems!textbox11.Value

get the rank of returned rows (was "SQL Question")

Given the following results:
col0 col1 col2
THY 2,265,850 31
VIE 1,474,994 20
RID 1,221,800 17
ACC 1,124,335 15
FEI 445,184 6
DIR 433,783 6
ROM 324,365 4

What is the best way in a query to get the rank of the returned rows by either col1 or col2. In other words who's the number 1,2,3 etc...

total count col0 = 7
total col1 = 7,290,310
total col2 (would eqaul 100%)= 99%

Looking for a mathmatical solution to this any help would be appreciated.Are you looking for row numbers or running totals? Either (or both) can be done. I assume you are order by COL1 Descending?|||Actaully either but it must be based on the totals. I'm playing with a sub-query at the moment trying to use INTENDTITY(INT,1,1) as myRanK field. Only problem is I don't have control of the resulting inner query. I guess I could use a temp table to query against but I was trying to do this in as few trips as possible.|||What does the desired outpout look like? It looks like you are after

select count(*), sum(col2), sum(col3)
from yourtable|||the desired output would be something like the following:

col0 col1 col2 col3(aka Rank)
THY 2,265,850 31 1
VIE 1,474,994 20 2
RID 1,221,800 17 3
ACC 1,124,335 15 4
FEI 445,184 6 5
DIR 433,783 6 6
ROM 324,365 4 7

So I'd have a rank based on the sum of either col1 or col2 against the totals for the group.

Right now I'm trying something like the following but having trouble controlling my returned records from the inner query:

SELECT IDENTITY (INT, 1, 1) AS rank,q.*
FROM (SELECT col0,col1,col2 FROM mytable) q
ORDER BY q.col1

not working as I'd expect. :confused:|||This is a general solution that numbers the rows of your dataset in descending order of Col0:

Select YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2,
count(SecondInstance.col0) as Rank
from YourDataSet
inner join YourDataSet SecondInstance on YourDataSet.col1 <= SecondInstance.col1
group by YourDataSet.col0,
YourDataSet.col1,
YourDataSet.col2|||SELECT q.*,IDENTITY (INT, 1, 1) AS rank
INTO db.dbo.TEST
FROM (SELECT col0,col1,SUM(CASE WHEN Date >= '01/01/2004' AND Date <= '12/31/2004' THEN someValue ELSE 0 END)
AS col2
FROM tab1 INNER JOIN
tab2 ON tab1.ID = tab2.ID
WHERE (Date >= '01/01/2004') AND (Date <= '12/31/2004')
GROUP BY col0,col1) q
ORDER BY q.col1 DESC

Better example of what I'm working with...|||hey toejam, did you try blindman's suggestion with the theta join?

FYI you guys should read IDENTITY() Function Isn't Reliable for Imposing Order on a Result Set (http://www.winnetmag.com/SQLServer/Article/ArticleID/43553/43553.html)|||r937,

I read blindmans post but I wasn't sure if it would get me the results I'm after but I'll try it.

the link you provided is very on point so I'm going to take a moment to read through it.

Thx!

:D|||see http://forums.devshed.com/t218290/s.html|||It'll get you the results you are after. It is a pretty standard solution to your class of problem.|||r937 -

DevShed (http://forums.devshed.com/showthread.php?p=944761#post944761)

Thx very much... I did it the old fashion way. I did a little VB code to get my answer but I'd prefer to do it in a query to let the db do the work. Awesome job man!

Thx a bunch

Get results from VB SQLCRL Stored Procedure

Hi,

I created a VB SQL CRL Stored procedure for calculating a value. Value
is returned as below

Using sConn4 As New SqlConnection("context connection=true")
sConn4.Open()
scmd = New SqlCommand("SELECT " & var_max, sConn4)
sdrd = scmd.ExecuteReader()
SqlContext.Pipe.Send(sdrd)
End Using

When calling this stored procedure from a TSQL stored procedure for
using the value for further processing the value returned to my
variable is 0. The correct value should be 56. In results tab I get the
correct result, but how can I assign it to my variable @.max ?

DECLARE @.max1 int
DECLARE @.max int

EXEC @.max1 = [dbo].[VBSTP_calculate_MAX_no]
@.vsp_table_name = N'[dbo].[Message]',
@.vsp_table_key = N'message_no',
@.vsp_WHERE = N''

print @.max1 -- value here is 0

SET @.max = (SELECT @.max1)

Thanks a lot.Chris (CLarkou@.gmail.com) writes:
> When calling this stored procedure from a TSQL stored procedure for
> using the value for further processing the value returned to my
> variable is 0. The correct value should be 56. In results tab I get the
> correct result, but how can I assign it to my variable @.max ?
> DECLARE @.max1 int
> DECLARE @.max int
> EXEC @.max1 = [dbo].[VBSTP_calculate_MAX_no]
> @.vsp_table_name = N'[dbo].[Message]',
> @.vsp_table_key = N'message_no',
> @.vsp_WHERE = N''
> print @.max1 -- value here is 0

A stored procedure (no matter if it's written in T-SQL or VB .Net) can
return values in three different ways:

1) Return value.
2) Output parameters.
3) Result set.

Your procedure returns a result set, but above you are retrieving the
return value. In my opinion, return values should be used to indicate
success/failure (with 0 meaning success) and nothing else.

If the purpose of your VB procedure is to compute a single value, you
should not return a result set from it, but you should return an output
parameter. Or maybe even better - you should make it a function.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx