Grid display: return

Showing posts with label return. Show all posts

Thursday, March 29, 2012

Get A return value with EXEC?

Hi, I have an sql query like this :

DECLARE
@.TableName varchar(200),
@.ColumnName varchar(200),
@.EmployeeID varchar(200),
@.Result varchar(200);

SET @.TableName = 'Customer';
SET @.ColumnName = 'First_Name';
SET @.CustomerID = 28;

-- This line return Error
SET @.Result = EXEC ('select' + @.ColumnName + ' from ' + @.TableName + ' where Recid = ' + @.CustomerID + '');

Print @.Result;

I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC?

Thanks

I think you can do the following:
EXEC ('SET @.Result = select' + @.ColumnName + ' from ' + @.TableName + ' where Recid = ' + @.CustomerID + '');

Get a return value when calling a SP from within ASP

I'm trying to get a return value (MyResult) from a stored procedure
that's called from within an ASP page. Both the codes in the Stored
Procedure and in the ASP page are rather simple and straightforward.
Any help available?
The error code says:
Microsoft VBScript compilation error '800a03ee'
Expected ')'
/ics/test1.asp, line 23
Set rs = ADODBCon.Execute(sSql, out MyResult)
--^
CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
set @.Result = 2 * @.Input
GO
---
<HTML>
<HEAD><TITLE>test</TITLE></HEAD>
<BODY>
<%
Set ADODBCon = Server.CreateObject("ADODB.Connection")
Dim StrConn, rs, MyInput, MyResult
MyInput = 123
ADODBCon.Provider = "SQLOLEDB"
strConn = "Data Source=MySQLServer;"
strConn = strConn & "Initial Catalog=MyDatabase;"
strConn = strConn & "User Id=JohnD;"
strConn = strConn & "Password=MyPwd;"
ADODBCon.Connectionstring = strConn
ADODBCon.open
sSql = "usp_test " & MyInput
Set rs = ADODBCon.Execute(sSql, out MyResult)
%>
</BODY>
</HTML>"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.co...y/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:

> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> <HTML>
> <HEAD><TITLE>test</TITLE></HEAD>
> <BODY>
> <%
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
> </BODY>
> </HTML>
>|||Thanks david, it worked.|||Alejandro, thank you. It worked.

Get a return value when calling a SP from within ASP

test"ab" <absmienk@.hotmail.com> wrote in message
news:1154434778.587929.301980@.b28g2000cwb.googlegroups.com...
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
TM for your R-ing pleasure:
Calling a Stored Procedure with a Command
http://windowssdk.msdn.microsoft.com/en-us/library/ms676516.aspx
David|||ab,
Use a command object instead.
How to call SQL Server stored procedures from ASP
http://support.microsoft.com/kb/q164485/
AMB
"ab" wrote:
> I'm trying to get a return value (MyResult) from a stored procedure
> that's called from within an ASP page. Both the codes in the Stored
> Procedure and in the ASP page are rather simple and straightforward.
> Any help available?
>
> The error code says:
> Microsoft VBScript compilation error '800a03ee'
> Expected ')'
> /ics/test1.asp, line 23
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> --^
>
> CREATE PROCEDURE usp_test @.Input int, @.Result int OUTPUT AS
> set @.Result = 2 * @.Input
> GO
> ---
> &

> test
>
> Set ADODBCon = Server.CreateObject("ADODB.Connection")
> Dim StrConn, rs, MyInput, MyResult
> MyInput = 123
> ADODBCon.Provider = "SQLOLEDB"
> strConn = "Data Source=MySQLServer;"
> strConn = strConn & "Initial Catalog=MyDatabase;"
> strConn = strConn & "User Id=JohnD;"
> strConn = strConn & "Password=MyPwd;"
> ADODBCon.Connectionstring = strConn
> ADODBCon.open
> sSql = "usp_test " & MyInput
> Set rs = ADODBCon.Execute(sSql, out MyResult)
> %>
>
>

>|||Thanks david, it worked.|||Alejandro, thank you. It worked.

get a return value from an insert without using a stored proc.

hi all,

lets say i have this insert command being executed from C# to a SQL Db.

//store transaction logSqlCommand cmdStoreTrans =new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@.ImportID,@.ProfileID,@.RowID);",conn);cmdStoreTrans.Parameters.Add("@.ImportID",importId);cmdStoreTrans.Parameters.Add("@.ProfileID",profileId);cmdStoreTrans.Parameters.Add("@.RowID",i);try{conn.Open();cmdStoreTrans.ExecuteNonQuery();conn.Close();}catch(SqlException ex){throw(ex);}

I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @.@.Identity

any help appreciated

TIA, mcm

Try doing a SELECT SCOPE_IDENTITY() at the end of your INSERT statement. and use ExecuteScalar instead of ExecuteNonQuery.|||

thanks,

will do.

mcm

Tuesday, March 27, 2012

Get 10 records fro each group

Hi
I have a table that has fromNode, toNode and idGroup columns
I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
1000
E.G. The query would return
fromNode, toNode, idGroup
1000,2001,1
1000,2002,1
1000,2003,1
1000,2004,2
1000,2005,2
1000,2006,2
1000,2013,3
1000,2014,3
1000,2016,4
But I am having trouble comming up with such a query. Can any one help.
SQL code and inserts below.
CREATE TABLE [dbo].[memberPathsGroups](
[fromNode] [int] NOT NULL,
[toNode] [int] NOT NULL,
[idGroup] [int] NOT NULL,
CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
(
[fromNode] ASC,
[toNode] ASC,
[idGroup] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into memberPathsGroups values (1000,2001,1)
insert into memberPathsGroups values (1000,2002,1)
insert into memberPathsGroups values (1000,2003,1)
insert into memberPathsGroups values (1000,2004,2)
insert into memberPathsGroups values (1000,2005,2)
insert into memberPathsGroups values (1000,2006,2)
insert into memberPathsGroups values (1000,2007,2)
insert into memberPathsGroups values (1000,2008,2)
insert into memberPathsGroups values (1000,2009,2)
insert into memberPathsGroups values (1000,2010,2)
insert into memberPathsGroups values (1000,2012,2)
insert into memberPathsGroups values (1000,2013,3)
insert into memberPathsGroups values (1000,2014,3)
insert into memberPathsGroups values (1000,2015,2)
insert into memberPathsGroups values (1000,2016,4)
insert into memberPathsGroups values (1001,2001,1)
insert into memberPathsGroups values (1001,2010,1)
insert into memberPathsGroups values (1001,2012,1)
insert into memberPathsGroups values (1001,2016,2)Here's one way:
SELECT
fromNode,
toNode,
idGroup
FROM dbo.memberPathsGroups mpg
WHERE
mpg.toNode IN
(
SELECT TOP 3
mpg1.toNode
FROM dbo.memberPathsGroups mpg1
WHERE
mpg1.fromNode = mpg.fromNode
AND mpg1.idGroup = mpg.idGroup
ORDER BY mpg1.toNode
)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rippo" <info@.rippo.co.uk> wrote in message
news:1131373532.924245.290810@.o13g2000cwo.googlegroups.com...
> Hi
> I have a table that has fromNode, toNode and idGroup columns
> I want to return the TOP 3 toNodes for each IDGroup that the FromNode =
> 1000
> E.G. The query would return
> fromNode, toNode, idGroup
> 1000,2001,1
> 1000,2002,1
> 1000,2003,1
> 1000,2004,2
> 1000,2005,2
> 1000,2006,2
> 1000,2013,3
> 1000,2014,3
> 1000,2016,4
> But I am having trouble comming up with such a query. Can any one help.
> SQL code and inserts below.
>
> CREATE TABLE [dbo].[memberPathsGroups](
> [fromNode] [int] NOT NULL,
> [toNode] [int] NOT NULL,
> [idGroup] [int] NOT NULL,
> CONSTRAINT [PK_memberPathsGroups] PRIMARY KEY CLUSTERED
> (
> [fromNode] ASC,
> [toNode] ASC,
> [idGroup] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert into memberPathsGroups values (1000,2001,1)
> insert into memberPathsGroups values (1000,2002,1)
> insert into memberPathsGroups values (1000,2003,1)
> insert into memberPathsGroups values (1000,2004,2)
> insert into memberPathsGroups values (1000,2005,2)
> insert into memberPathsGroups values (1000,2006,2)
> insert into memberPathsGroups values (1000,2007,2)
> insert into memberPathsGroups values (1000,2008,2)
> insert into memberPathsGroups values (1000,2009,2)
> insert into memberPathsGroups values (1000,2010,2)
> insert into memberPathsGroups values (1000,2012,2)
> insert into memberPathsGroups values (1000,2013,3)
> insert into memberPathsGroups values (1000,2014,3)
> insert into memberPathsGroups values (1000,2015,2)
> insert into memberPathsGroups values (1000,2016,4)
> insert into memberPathsGroups values (1001,2001,1)
> insert into memberPathsGroups values (1001,2010,1)
> insert into memberPathsGroups values (1001,2012,1)
> insert into memberPathsGroups values (1001,2016,2)
>

Thursday, March 29, 2012

Get A return value with EXEC?

Get a return value when calling a SP from within ASP

Get a return value when calling a SP from within ASP

get a return value from an insert without using a stored proc.

Tuesday, March 27, 2012

Get 10 records fro each group

Grid display

Blog Archive

About Me