return data from two tables

Hi,
hope im posting in right group...
Im trying to return a value from 1 of two tables depending on the value in
one table.
I.e. TableA has an action_id and action_type fields. Depending on if
action_type = S, return the Desc field from TableB, where action_id =
Desc_ID. if action_type = L return the Desc field from TableC, where
action_id = Desc_ID
i.e.
select a.Type_id, (either b.Desc or c.Desc)
from TableA a, TableB b, TableC c
where 1=1
if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
i just dont know how to get this into an sql query. i can do it VBA, but
that isn't an option.
Any help or direction would be greatly appreciated.
Thanks
Tony Z.select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc
end
from TableA a, TableB b, TableC
where 1=1
"Tony Zappal" wrote:

> Hi,
> hope im posting in right group...
> Im trying to return a value from 1 of two tables depending on the value in
> one table.
> I.e. TableA has an action_id and action_type fields. Depending on if
> action_type = S, return the Desc field from TableB, where action_id =
> Desc_ID. if action_type = L return the Desc field from TableC, where
> action_id = Desc_ID
> i.e.
> select a.Type_id, (either b.Desc or c.Desc)
> from TableA a, TableB b, TableC c
> where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_i
d
> i just dont know how to get this into an sql query. i can do it VBA, but
> that isn't an option.
> Any help or direction would be greatly appreciated.
> Thanks
> Tony Z.|||>select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc endred">
> from TableA a, TableB b, TableC
> where 1=1
Those three tables, listed without any joining, will result in a cross
product result set. If there were 100 rows each, the result set would
have 100*100*100 = 1000000 rows.
Roy|||There are a few ways this could be written. Here is one.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S'
THEN (select B.desc from TableB as B
where A.something = B.something)
WHEN A.action_type = 'L'
THEN (select C.desc from TableC as C
where A.something = C.something)
FROM TableA as A
And another.
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
WHERE A.action_type = 'S'
UNION ALL
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableC as C
ON A.something = C.something
WHERE A.action_type = 'L'
And a third.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S' THEN B.desc
WHEN A.action_type = 'L' THEN C.desc
END
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
JOIN TableC as C
ON A.something = C.something
Roy
On Tue, 21 Feb 2006 18:16:27 -0800, "Tony Zappal"
<TonyZappal@.discussions.microsoft.com> wrote:

>Hi,
>hope im posting in right group...
>Im trying to return a value from 1 of two tables depending on the value in
>one table.
>I.e. TableA has an action_id and action_type fields. Depending on if
>action_type = S, return the Desc field from TableB, where action_id =
>Desc_ID. if action_type = L return the Desc field from TableC, where
>action_id = Desc_ID
>i.e.
>select a.Type_id, (either b.Desc or c.Desc)
>from TableA a, TableB b, TableC c
>where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
>i just dont know how to get this into an sql query. i can do it VBA, but
>that isn't an option.
>Any help or direction would be greatly appreciated.
>Thanks
>Tony Z.|||Thank you John and Roy.
I've got my query returning exactly what i need.
Big thanks.
Tony.sql

Return data from multiple tables

Hi there,

I have tables with such structure

transaction_YYMM
(idx,date,company_id,value)

where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

best regards
RafalI want to define query (maybe view, procedure):

Quote:

Originally Posted by

select * from [?] where date>='2007-01-01' and date<='2007-04-30'

A UNION ALL query will combine multiple result sets:

SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704

You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating a
partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rafa Bielecki" <rafal@.bielecki.infowrote in message
news:f2ubuk$i1o$1@.nemesis.news.tpi.pl...

Quote:

Originally Posted by

Hi there,
>
I have tables with such structure
>
transaction_YYMM
(idx,date,company_id,value)
>
where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one
>
best regards
Rafal
>

|||Uzytkownik "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netnapisal w
wiadomosci news:JOz4i.22889$JZ3.9830@.newssvr13.news.prodigy.n et...

Quote:

Originally Posted by

A UNION ALL query will combine multiple result sets:
>
SELECT *
FROM dbo.transaction_0701
UNION ALL
SELECT *
FROM dbo.transaction_0702
UNION ALL
SELECT *
FROM dbo.transaction_0703
UNION ALL
SELECT *
FROM dbo.transaction_0704
>
You can specify an explicit column list (a Best Practice) and encapsulate
the query in a view to facilitate reuse. You might also consider creating
a partitioned view (or a partitioned table if you are running SQL 2005
Enterprise Edition). See the Books Online for more information.

thank you Dan, your help is very important to me
Rafal|||Rafa Bielecki (rafal@.bielecki.info) writes:

Quote:

Originally Posted by

I have tables with such structure
>
transaction_YYMM
(idx,date,company_id,value)
>
where YYMM stands for 2digits year and month
I want to define query (maybe view, procedure):
select * from [?] where date>='2007-01-01' and date<='2007-04-30'
which will grab data from
transaction_0701
transaction_0702
transaction_0703
transaction_0704
and return all as one

In additions to Dan's response, I like to point out that from a logical
point of view, the above design is flawd. It's a lot easier to deal with a
single table. If there are enourmous volumes, it can still be motivated
with partitioning, but then we are talking enourmous values like tens
of millions of rows per month.

--
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

Return BIGINT OUTPUT param to VB!?

Please help me on this one.

I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc

CREATE PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage bigint OUTPUT
AS
...
...
SELECT @.WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)
FROM
TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND [PDF].[WD-WE] = @.InWeekDay
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG

When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null

I try to return it with the following code (got the code from a friend)

Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.Recordset
Dim objAdoRecordset As ADODB.Recordset
Dim objAdoCommand As ADODB.Command
Dim lngCtr As Long
On Error GoTo RunCommand_Error

' Create cmd object
Set objAdoCommand = New ADODB.Command
Set objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.ActiveConnection = m_oAdoConnection
objAdoCommand.CommandText = v_strStoredProcName
objAdoCommand.CommandType = adCmdStoredProc
Call objAdoCommand.Parameters.Refresh
'Stop
For lngCtr = 0 To UBound(r_varParamValues)
If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then
objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr)
End If
Next

Set objAdoRecordset = New ADODB.Recordset
objAdoRecordset.CursorLocation = adUseClient

Set objAdoRecordset = objAdoCommand.Execute
'Stop
For lngCtr = 0 To objAdoCommand.Parameters.Count - 1
If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then
r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value
End If
Next
Set RunProcedure = objAdoRecordset
RunCommand_Exit:
' Collect your garbage here
Exit Function
RunCommand_Error:
' Collect your garbage here
Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")
End Function

PLEASE HELP.

Regards,

Sander

Grrrr...If I add the following line (before the DROP) to the sp it returns the value!

SET @.WeightedAverage = 9587558855

?! Que Pasa ?!

Return a value using EXEC

OK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:

CREATE PROCEDURE dbo.ExecFunction ( @.ScalarSELECTString varchar(250), @.@.ReturnVal sql_variant )

set @.@.ReturnVal = ( exec @.ScalarSELECTString )

go

So, I was wondering if someone might be able to suggest a way to re-write the above code to achieve the same effect. Thanks in advance.First, Dynamic SQL should be a last resort option...

Do a google on SQL injection (security problem) and not to mention performance problems...

Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

You say you know db's and SQL...what's you're background?

OK, so now the answer to your question...is it depends on the SQL...

And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row...if you get a set, you'll only store the last value...

But here's a sample...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs
GO

DROP PROC mySproc99
GO|||Actually...I forgot the assignment...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT @.rs=col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs OUTPUT
SELECT RTRIM(@.rs)
GO

DROP PROC mySproc99
GO|||My DB background is mostly in Sybase SQLAnywhere... I never found a way to do dynamic SQL statements in that, so I usually ended up writing them into the front-end (VB.NET) rather than the DB itself; I just think this would be much more useful.

Security shouldn't be a problem for now, since there is no external access and we are a very small operation. As for the performance issue, I figured that might come up since it obviously can't pre-compile, but I wanted to have this as an option anyway so I don't have to repeat code.

I'm in control of the statements. Like I said, the idea is to create a generic tool that can be used repeatedly when a single value is needed.

As for creating separate sprocs... I assume "sprocs" are system procedures. How is this a better solution?

Originally posted by Brett Kaiser
First, Dynamic SQL should be a last resort option...

Do a google on SQL injection (security problem) and not to mention performance problems...

Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

You say you know db's and SQL...what's you're background?

OK, so now the answer to your question...is it depends on the SQL...

And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row...if you get a set, you'll only store the last value...

But here's a sample...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs
GO

DROP PROC mySproc99
GO|||Also, thanks for the solution anyway. I was hoping there would be some way to do it without a temp table, but I guess I'll go with that if I have to.

Originally posted by Brett Kaiser
First, Dynamic SQL should be a last resort option...

Do a google on SQL injection (security problem) and not to mention performance problems...

Second, who's in control of the statements? I'm assuming you are, so why not just create separate sprocs to do what you want? They'll be compiled, effecient, secure, ect...

You say you know db's and SQL...what's you're background?

OK, so now the answer to your question...is it depends on the SQL...

And you need a temp table. Also it seems like you're assuming that you'll always get only 1 row...if you get a set, you'll only store the last value...

But here's a sample...

USE Northwind
GO

CREATE PROC mySproc99 @.SQL varchar(4000), @.rs varchar(255) OUTPUT
AS
BEGIN
DECLARE @.cmd varchar(255)

CREATE TABLE #myTable99 (col1 varchar(8000))


SELECT @.cmd = 'INSERT INTO #myTable99(col1) ' + @.SQL
EXEC(@.cmd)

SELECT col1 FROM #myTable99
END
GO

DECLARE @.rs varchar(255)
EXEC mySproc99 'SELECT CONVERT(varchar(255),MAX(Freight)) FROM Orders', @.rs
GO

DROP PROC mySproc99
GO|||SPROC just mean stored procedure...

And you're SQL statement would have to match the number of columns in the temp table...so I guess you'd also need dynamic sql to build the temp table to match...

seems like a lot of hoop kumping...

and what would you do when you get n rows back?|||The idea is that the SQL statement selects a single value, so I don't need to worry about multiple columns or multiple rows.

I will be creating a stored procedure to do this... that was the whole point from the beginning. However, I just discovered that I can't execute a stored procedure from within a function. It seems like the user-defined function capability is fairly limited in SQL Server as compared to SQLAnywhere. Is there any way to get around that?

Originally posted by Brett Kaiser
SPROC just mean stored procedure...

And you're SQL statement would have to match the number of columns in the temp table...so I guess you'd also need dynamic sql to build the temp table to match...

seems like a lot of hoop kumping...

and what would you do when you get n rows back?|||One other thing... in SQLAnywhere, to do performance tuning, all you have to do is turn on Profiling and then you can view the execution time of any procedure or function. I assume the equivalent is SQL Profiler, but how do I see the execution time of a specific function?|||Never mind, I found it in Query Analyzer.

return a value from SQL server SP back to .net

I have a SP code:
select 'nothing' from tableA where userID = '123'
if @.@.rowcount = 0
return 0
else
return 1

.net code:
Dim myConnection As New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand("StoreP", myConnection)

myCommand.Connection.Open()
Returnvalue = myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Returnvalue shows -1, it doesnt show the return value from SP. how do I fix this problem? thanksHi,

The return value from the ExecuteNonQuery returns the number of rows affected by the query, not the return value from the stored procedure. For a SELECT statement like you're using, it always returns -1.

To get the return value, you have to add a parameter to the ADO.NET command object, and set its Direction property to ReturnValue. Something like this:

myCommand.Parameters.Add("@.RetVal", SqlType.Integer).Direction = _
 ParameterDirection.ReturnValue

Then you can read the value after you run the query:

Dim i as Int32 = myCommand.Parameters("@.RetVal").Value

I've typed the code from memory, so it may need some tweaking.

Don|||I still have two questions
1. If I want to return 2 value from SP to asp.net, how do I do?
2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Thank you|||Hi,

1. If I want to return 2 value from SP to asp.net, how do I do?

Then use output parameters. You can define as many of those as you want. For them, use ParameterDirection.Output for the Direction property.

2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Probably the best way is to raise an error from the SP using the RAISERROR statement. That will generate a SqlException that you can catch and handle in your page.

Another way is to return one or more output parameters from the SP, one for an error number and another for a message. I don't like this option because it's more work and doesn't hook into the natural exception infrastructure of .NET.

Don|||Can you tell me how exactly you do it? I try parameterdirection.output it gives me an error "too many argument specified." how do I code in SP to return 2 values? thank you|||It sounds like you haven't added the output parameters to the stored procedure, right? You have to do that as well as add the ADO.NET code.

Post the complete sp definition and we'll help you make the changes.

Don|||the SP code is

CREATE PROCEDURE test @.aaa as varchar(10) output, @.bbb as varchar(10) output AS
select @.aaa = '111'
select @.bbb = '222'
return
GO

asp.net code is

Dim objCOmmand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
objCOmmand.CommandType = CommandType.StoredProcedure
objCOmmand.Parameters.Add(New SqlParameter("@.aaa", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.aaa", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.aaa").Value = "aaa"
objCOmmand.Parameters.Add(New SqlParameter("@.bbb", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.bbb").Value = "bbb"
objCOmmand.ExecuteNonQuery()
Label1.Text = objCOmmand.Parameters("@.aaa").Value
Label2.Text = objCOmmand.Parameters("@.bbb").Value
objConnection.Close()

after SP I should get 111 in stead of aaa in @.aaa and 222 instead of bbb in @.bbb, but I get aaa and bbb in the result. how do I get the value return from SP?|||Your are not using the correct ParameterDirection. ReturnValue is solely to return the value that appears after the RETURN keyword in your stored procedure. Valid ParameterDirection values for stored procedure parameters are:
Input
Output
InputOutput

In your case, you should be using InputOutput for @.aaa and @.bbb since you are supplying data to the stored procedure (input) and are new receiving data back from the stored procedure (output).

Terri|||I try:

objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput

but it gives me an error:

Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0|||Since you are using a VarChar datatype, you need to specify the length. And I don't know if it matters, but I usually take 2 lines to add a parameter and set the direction:

objCommand.Parameters.Add("@.aaa", SqlDbType.VarChar, 10)
objCommand.Parameters("@.aaa").Direction=ParameterDirection.InputOutput
objCommand.Parameters.Add("@.bbb", SqlDbType.VarChar, 10)
objCommand.Parameters("@.bbb").Direction=ParameterDirection.InputOutput

Terri

Return a value from EXEC

Hi everybody

How to return a value like that
I dont want to use Stored proc.
Thanks

DECLARE @.cpt as integer
DECLARE @.pTable as varchar(40)

Select @.pTable = 'Salaires'
SELECT @.cpt = EXEC('Select Count(*) FROM ' + @.pTable)

Print @.cptdeclare @.i int
declare @.sql nvarchar(1000)
select @.sql = 'Select @.i = Count(*) FROM ' + @.pTable
exec sp_executesql @.sql, N'@.i int out', @.i out
select @.i

see
http://www.nigelrivett.net/sp_executeSQL.html

Return a value from EXEC

I have the following code:

DECLARE @.StartTimeStamp as datetime
DECLARE @.sqlcmd as varchar(8000)
DECLARE @.LogTable as varchar(20)
DECLARE @.PlantID as varchar(20)
DECLARE @.GroupIdent as varchar(20)
set @.LogTable = 'LOG_APPLE'
set @.PlantID = 'RETY'
set @.GroupIdent = '242'
BEGIN
SET @.sqlcmd = '
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM ' + @.LogTable + '
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = ''' + @.PlantID + ''' AND GroupIdent = ''' + @.GroupIdent + ''')
ORDER BY timestamp DESC)
'
SELECT @.sqlcmd
EXECUTE (@.sqlcmd)
SELECT @.StartTimeStamp
END

When I run it from Query Analyzer, I get the following message on the last SELECT statement:

Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.StartTimeStamp'.

If I copy the value of @.sqlcmd into Query Analyzer like the following with a DECLARE stament, I get the correct output:

DECLARE @.StartTimeStamp as datetime
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM LOG_PX90J
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = 'USFL' AND GroupIdent = '242')
ORDER BY timestamp DESC)
SELECT @.StartTimeStamp

This is the output I expect to receive:
----------------
NULL

(1 row(s) affected)

My question is, since the statement I am creating in @.sqlcmd is syntactically correct, how do I return the value from that command to @.StartTimeStamp via the EXECUTE statement?

P.S. I am forcing the variables to values just for testing purposes. I realize I should not be using "timestamp" as a field name also but I am dealing with legacy databases.

Thanks...This is because when you execute dynamic SQL the script is actually executed in a different process. As such any variables you declare do not exist when you call EXECUTE.

Look up the system stored proc sp_executesql in Books Online (BOL) to resolve this problem.

macka.|||Took your advise and did the following:

BEGIN
SET @.sqlcmd = N'
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM @.LogTable
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = @.PlantID AND GroupIdent = @.GroupIdent)
ORDER BY timestamp DESC)
'
SELECT @.sqlcmd
EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime, @.LogTable varchar(20), @.PlantID varchar(20), @.GroupIdent varchar(20)',
@.StartTimeStamp, @.LogTable, @.PlantID, @.GroupIdent
SELECT @.StartTimeStamp
END

Now I get the following error:
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 21
Cannot convert parameter '@.statement' to ntext/nchar/nvarchar data type expected by procedure.|||I have been unable to test this as I don't have the table definitions or data, but give this a go.

DECLARE @.StartTimeStamp as datetime
DECLARE @.sqlcmd as nvarchar(4000)
DECLARE @.LogTable as varchar(20)
DECLARE @.PlantID as varchar(20)
DECLARE @.GroupIdent as varchar(20)
set @.LogTable = 'LOG_APPLE'
set @.PlantID = 'RETY'
set @.GroupIdent = '242'

SET @.sqlcmd = N'
SET @.StartTimeStamp =
(SELECT TOP 1 timestamp
FROM ' + @.LogTable + '
WHERE timestamp <
(SELECT TPMSummaryProcessTime
FROM tblPRSGroup
WHERE PlantID = ''' + @.PlantID + ''' AND GroupIdent = ''' + @.GroupIdent + ''')
ORDER BY timestamp DESC)
'

EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime', @.StartTimeStamp

macka.|||MODIFY:

EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime', @.StartTimeStamp
SELECT @.StartTimeStamp

TO:

EXECUTE sp_executesql @.sqlcmd, N'@.StartTimeStamp datetime', @.StartTimeStamp OUTPUT
SELECT @.StartTimeStamp

Enjoy your query:)|||Thanks for everyone's help. Your tips helped me solve the problem.sql

Return a value after insert the query

Hi!

create table testReturn
(
id int identity(100,1),
name varchar(10)
)

How can I return the value of identity column after inserting the value.

Dim objConn As SqlConnection
Dim SQLCmd As SqlClient.SqlCommand
Dim ds As New DataSet
Dim strsql As String

Try

objConn = New SqlConnection
objConn.ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=localhost;" & _
"Initial Catalog=mydb;" & _
"User ID=userid;" & _
"Password=pass"

objConn.Open()

strsql = "insert into testReturn values ('a')"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
Dim rowsAffected As Integer = 0
rowsAffected = SQLCmd.ExecuteNonQuery

Dim rv As String
rv = SQLCmd.Parameters(0).Value.ToString()
Response.Write(rv)

Catch ex As Exception
Response.Write(ex.ToString)
End Try

SeeHow do I get the IDENTITY / AUTONUMBER value for the row I inserted?

|||

strsql = "insert into testReturn values ('a'); select @.@.Identity"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)

dim Identiy as Object

Identity = SQLCmd.ExecuteScalar

|||

You need to put "Select @.@.identity" statement immediately after your insert query so it would return the ID value of the record inserted, using the preceding Insert statement.

Cheers
Ritesh

|||

Using @.@.IDENTITY is an vulnerable approach, since it returns the last inserted id of any table. If you are using SQL 2005 then you can use the OUTPUT clause.

SeeHow to get an Identity value with SQL Server 2005

|||

You can either use @.@.identity or scope_identity().

@.@.identity gives you the last generated identity value.

scope_identity() gives you the last generated identity value for current scope.

Just have a quick look at BOL for further understanding.

Hope this will help.

return a TABLE

hello all

I need a function with a return value TABLE...

this is not the problem..

but i need the returned table dynamic..

example:

i call the function getTable(schema_name, TableName)

the 1st value is the schema. the 2nd is the table i need...

now i must have a return value like this

select * from schema_name.TableName

is this possible? can i build by return value dynamically?

i tried a lot but nothing worked..

thx

greg

Give a look to CREATE FUNCTION in books online and you will find that in functions two things that you are not allowed to do are:

EXEC ( ' (any SQL statement)' ) EXEC aStoredProceduresql

return a Scalar value...

How can I return a Scalar value by calling PROCEDURE 2 from PROCEDURE 1.
Something Like:
SET @.nextDataVersion = EXEC(pr_GetNextDataVersion 2)
(this does not work!)different EXEC (you're using the dynamic sql one)
should be:
EXEC @.nextDataVersion = pr_GetNextDataVersion 2
Note: This return value must be an int. [see RETURN in BOL for more]
Evan Camilleri wrote:
> How can I return a Scalar value by calling PROCEDURE 2 from PROCEDURE 1.
> Something Like:
> SET @.nextDataVersion = EXEC(pr_GetNextDataVersion 2)
> (this does not work!)
>|||Thanks! Quite stange syntax!
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eVcKKWq9FHA.3880@.TK2MSFTNGP12.phx.gbl...
> different EXEC (you're using the dynamic sql one)
> should be:
> EXEC @.nextDataVersion = pr_GetNextDataVersion 2
> Note: This return value must be an int. [see RETURN in BOL for more]
> Evan Camilleri wrote:

Return 0 if null

Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:

> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:

> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:

>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

return

whats wrong with this SP? I want @.id to contain the row identity of the newly created row as a return value.

ALTER PROCEDURE setCountry
(
@.name varchar( 50 ) = NULL,
@.alt varchar( 24 ) = NULL,
@.code varchar( 3 ) = NULL,
@.id int = null OUT
)
AS
SET NOCOUNT ON
INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )
@.id = @.@.identity
RETURN

INSERT INTO Countries( CountryName, CountryAltName, CountryCode ) VALUES ( @.name, @.alt, @.code )select@.id = @.@.identity

couple of things :
if you'd like to return this id back to asp.net you need to return it as an OUTPUT parameter..check out BOL for OUTPUT Parameters in stored procs..

also i'd recommend using SCOPE_IDENTITY() rather than @.@.IDENTITY. check out BOL again for the differences between them.

hth|||Thanks - but what is BOL?|||RETURN @.id

??

personally I'd do it this way

SET NOCOUNT ON
-- do insert
...
SELECT @.@.Identity|||BOL = Books On Line - best reference for sql server 2000. Free Download from microsoft.

hth|||Thanks! - I got the BOL acronym too - duh - Books On Line. I will try it now and actually may use SCOPE_IDENTITY() in place of @.@.identity.|||Atrax, I think the "return" method is better as it won't incur a result set. Although I'd use a OUTPUT param rather than return, I prefer to have that indicate some form of "state of the operation".|||Okay - now I can retrieve the result using ExecuteScalar - or DataReader or both?? Because when I run it in VS I dont see the results of the procedures. I mean it adds the row, but I don't see any output in the OUTPUT window.|||if you just need to return the ID you'd be better off using executescalar().

in vb.net

dim userID as integer
...
'open connection
...
userid=sqlcommand.ExecuteScalar()
...
'close connection

and use OUTPUT parameter to return the output form the stored proc...BOL had some samples no how to do it..

hth

Retrning XML raw from Stored procedure - (by using dual table)

Hi ,

I have a problem returning the @.CustomerNo from dual for one of my stored procedures. (by the way I do a some manipulations and assign a value to @.CustomerNo everytime this stored procedure runs)

SELECT @.CustomerNo AS CustomerNumber from dual FOR XML RAW

What I figured out is that the reason no values is returned in my XML file is because the dual table does not have any rows. But ,the dual table always have a dummy row by default. Doesnt it.

Alsoif possible please let me know any alternative way to returnthe following XML :

<row>

<CustomerNumber >

value of @.CustomerNo

<CustomerNumber >

</row>

Thanks in advance.

The dual table is an Oracle (shudders) feature.

If you are using SQL Server 2005, you can do the following:

Code Snippet

SELECT @.CustomerNo AS CustomerNumber

FOR XML PATH('row')

--

Peter DeBetta

MVP - SQL Server

http://sqlblog.com

|||Super.. Thanks for the soln.sql

Retrning XML raw from Stored procedure - (by using dual table)

Hi ,

I have a problem returning the @.CustomerNo from dual for one of my stored procedures. (by the way I do a some manipulations and assign a value to @.CustomerNo everytime this stored procedure runs)

SELECT @.CustomerNo AS CustomerNumber from dual FOR XML RAW

What I figured out is that the reason no values is returned in my XML file is because the dual table does not have any rows. But ,the dual table always have a dummy row by default. Doesnt it.

Alsoif possible please let me know any alternative way to returnthe following XML :

<row>

<CustomerNumber >

value of @.CustomerNo

<CustomerNumber >

</row>

Thanks in advance.

The dual table is an Oracle (shudders) feature.

If you are using SQL Server 2005, you can do the following:

Code Snippet

SELECT @.CustomerNo AS CustomerNumber

FOR XML PATH('row')

--

Peter DeBetta

MVP - SQL Server

http://sqlblog.com

|||Super.. Thanks for the soln.

retriving data from 2 views by the value in a textbox

i have a textbox which a user enters a numeric value

i want it to use SqlDataSource and check if the value exists in any of the tables.

in my text box the users would enter starting from '100000' or '200000'

i want it to check the view that starts the # with '100000' and 2ed view starts '200000'

With this i can check in one of the tables and make the selection.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber)">

<SelectParameters>

<asp:ControlParameterControlID="txtReport"Name="ReportNumber"PropertyName="Text"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

How can i make this possible ?

i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?

You'll need to do a conditional if in your select command:

IF @.ReportNumber > 10000 BEGINSELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber) END ELSE BEGINSELECT [ReportNumber] FROM [AppraisalSummaryTHEOTHERTABLE] WHERE ([ReportNumber] = @.ReportNumber) END

Retrieving values from dynamic SQL

Anyone know if it's possible to retrieve a parameterized value from dynamically executed SQL?

Code Example

Declare @.table varchar(25)
Declare @.somevalue varchar(3)
Set @.table = 'sometable'
Set @.somevalue = 'somevalue'
Declare @.sqlBuild varchar(2000)
Set @.sqlBuild = 'DECLARE @.return varchar(3); ' +
 ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
exec (@.sqlBuild)

i want to be able to extract the value of @.return for later use. this procdure works fine but I need to grab that value somehow.

Any suggestions?put a the end of your stored procedure select @.return ...
Use a ExecuteScalar in your code and it will be returned ...|||i'm not quite sure what you mean...

can you give me an example?|||You will need to use sp_executesql in order to capture the OUTPUT parameter from a dynamic SQL statement. Something like this (note that your @.sqlBuild needs to be of type nvarchar):

DECLARE @.table varchar(25), @.somevalue varchar(3), @.return integer, @.sqlBuild nvarchar(4000)
SELECT @.table = 'sometable', @.somevalue = 'somevalue'
SELECT @.sqlBuild = ' SELECT @.return = COUNT(COLUMNAME) ' +
' FROM ' + @.table +
' WHERE ' +
' value = ' + @.somevalue
EXEC sp_executesql @.sqlBuild, N'@.return integer OUTPUT', @.return OUTPUT

Terrisql

Retrieving values from a subreport to Body of Parent Report

Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.

Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.

Thanks,
Garick

I want to do something similar.

I want the value of the amount of records retrieved in the sub report.

The table row that the sub report is in needs to be hidden if the value is not greater than 1.

Can it be done?

|||

Jabuka

I think that there's a simple way to do what you want with creating the same dataset that you have in your subreport in the parent one. Then you can evalute the field in your visibility expression.

Hope it helps you

|||

Another way is to create a simple assembly (any language in .Net) and have a static (or shared) variable in it. Set the value of this variable in your subreport and refer to that in your main report.

The only problem with this approach is concurrency as you are using a static variable.

Shyam

retrieving the BigInt value from the Identity Column after inserting

I have a database that has a tble with a field that autoincrements as a primary key. meanig that the field type is BigInteger and it is set up as my Identity Column. Now when I insert a new record that field gets updated automaticly.

How can I get this value in the same operation as my insert? meaning, in 1 sub, I insert a new record but then need to retieve the Identity Value. All in the same procedure.

Waht is the way to achive this please?

Marc

What I do is issue the two commands (the sql insert, and the sql select scope_identity) in the same execute separated by semi colon.

the trick is to set the parameter direction to output for the identity.

David H. has a good article.

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

|||

Thank you very much. that did it!

Marc

Retrieving the autoincremented primary key value

I am using several TextBox Controls, instead of a FormView, for inserting data into a Sql Database. The primary key (ID) is an integer which is automatically incremented by one at each insertion.
At each insertion I need to retrieve the ID value of the newly inserted record.
I have followed a suggestion from a help sample with this code:

Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Data
Partial Class Insert
Inherits System.Web.UI.Page

Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click

SqlDataSource1.Insert()
End Sub

Sub On_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
Dim InsertedKey As SqlParameter
InsertedKey = New SqlParameter("@.PK_GuestList", SqlDbType.Int)
InsertedKey.Direction = ParameterDirection.Output
e.Command.Parameters.Add(InsertedKey)
End Sub

Sub On_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
Dim command As DbCommand
command = e.Command
Label1.Text = command.Parameters("@.PK_GuestList").Value.ToString()
End Sub

End Class

No output appears on the Label1.
If in the last code row I replace "@.PK_GuestList" with the name of any TextBox used for inputting data, its content is correctly shown in Label1.

Where is the problem?

You need to use Select Scope_Identity() in your SQL to get the ID that was just created. Without seeing your SQL, it's impossible to tell if you are doing this correctly.|||

Thanks Mike.

This is my Sql:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [GuestList] WHERE [ID] = @.ID"InsertCommand="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost)"

OnInserting="On_Inserting"OnInserted="On_Inserted"SelectCommand="SELECT * FROM [GuestList]"UpdateCommand="UPDATE [GuestList] SET [IDD] = @.IDD, [UserName] = @.UserName, = @.Email, [Title] = @.Title, [Message] = @.Message, [Reason] = @.Reason, [Accento] = @.Accento, [Views] = @.Views, [Posts] = @.Posts, [Quando] = @.Quando, [LastPost] = @.LastPost WHERE [ID] = @.ID">

<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters><UpdateParameters><asp:ParameterName="IDD"Type="Int32"/><asp:ParameterName="UserName"Type="String"/><asp:ParameterName="Email"Type="String"/><asp:ParameterName="Title"Type="String"/><asp:ParameterName="Message"Type="String"/><asp:ParameterName="Reason"Type="String"/><asp:ParameterName="Accento"Type="String"/><asp:ParameterName="Views"Type="String"/><asp:ParameterName="Posts"Type="String"/><asp:ParameterName="Quando"Type="DateTime"/><asp:ParameterName="LastPost"Type="String"/><asp:ParameterName="ID"Type="Int32"/></UpdateParameters><InsertParameters><asp:ParameterName="ID"Type="Int32"/><asp:formparametername="IDD"formfield="IDD"/><asp:formparametername="UserName"formfield="UserName"/><asp:formparametername="Email"formfield="Email"/><asp:formparametername="Title"formfield="Title"/><asp:formparametername="Message"formfield="Message"/><asp:formparametername="Reason"formfield="Reason"/><asp:formparametername="Accento"formfield="Accento"/><asp:formparametername="Views"formfield="Views"/><asp:formparametername="Posts"formfield="Posts"/><asp:formparametername="Quando"formfield="Quando"/><asp:formparametername="LastPost"formfield="LastPost"/>

</InsertParameters>|||May be I missunderstood. I have written no SQL code.|||

nodar10:

May be I missunderstood. I have written no SQL code.

Yes you have, on both counts. The InsertCommand, SelectCommand etc are SQL code. Have a look at this to see if you can work out how to get the value of Scope_Identity() in a stored procedure rather than with the SqlDataSource:http://www.eggheadcafe.com/forumpost.aspx?topicid=1&forumpostid=68123

|||

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

I have solved the problem by simply adding ;SELECT @.PK_GuestList = SCOPE_IDENTITY() at the end of the InsertCommand SQL statement , which becomes:

InsertCommand

="INSERT INTO [GuestList] ( [IDD], [UserName],, [Title], [Message], [Reason],

[Accento], [Views], [Posts], [Quando], [LastPost]) VALUES ( @.IDD, @.UserName, @.Email, @.Title,

@.Message, @.Reason, @.Accento, @.Views, @.Posts, @.Quando, @.LastPost);

SELECT @.PK_GuestList= SCOPE_IDENTITY()"

PK_GuestList is an auxiliary variable present in both .aspx and .aspx.vb files, and can be assume any name, but not ID.

Thank you very mauch for your help.

Federico

|||

nodar10:

Hi Mike!

You were right with the suggestion for SCOPE_IDENTITY.

....

Thank you very mauch for your help.

Federico

Then I don't understand why you unmarked my post as answer.

|||

Hi Mike

Sorry for doing some action inadvertely. I just tried to set the flag that my question has been properly answered.

Federico

Retrieving Return value from stored procedure declaratively

Hi.

I have a stored procedure "sp1" which returns a value (with the sql statement Return @.ReturnValue).

Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code.

Thanks in advance .

i do not know what will you sp return but i suppose that it is and INT

so you write this way;

int retrunvalue=sqlcommad.excutenonequery();

so the returned value will be passed to you int.

hope this will help

|||

this is sample code, it can help you:

Here is a sample sproc that populates output parameters
from the Northwind Products table:

CREATE PROCEDURE CustOrderOne
@.CustomerID nchar(5),
@.ProductName varchar(50) output,
@.Quantity int output

AS
SELECT TOP 1 @.ProductName=PRODUCTNAME, @.Quantity =quantity
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @.CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

And here is an example of some C# code to return and display the output parameters:

using System;
using System.Data;
using System.Data.SqlClient;
namespace OutPutParms
{
class OutputParams
{
[STAThread]
static void Main(string[] args)
{
using(SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;"))
{
SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@.CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@.ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@.Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine(cmd.Parameters["@.ProductName"].Value);
Console.WriteLine(cmd.Parameters["@.Quantity"].Value.ToString());
Console.ReadLine();
}
}
}
}

|||

The above 2 replies does not actually get the return value, which is a special parameter.

The first reply returns the row affected count and the second reply just gets the value out output parameters.

I am afraid I do not know how to retrieve the return value declaratively using controls like object data sources.

However of you are familiar with using SqlCommands then the following code shows you how to get the return values from stored procedures assuming your stored procedure is returning values which is different to result sets, row counts, and output parameters.

SqlCommand cmd =new SqlCommand("this is the query", connection);//create a parameter for the return valueSqlParameter param =new SqlParameter();param.Direction = ParameterDirection.ReturnValue;param.ParameterName ="returnValue";//add to parameter to collectioncmd.Parameters.Add(param);//execute commandcmd.ExecuteNonQuery();//get the return valueint retVal =int.Parse(cmd.Parameters["returnValue"].Value.ToString);