Multiple Return Values
Jun 2, 2004
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
Thanks in advance for your comments.
View 2 Replies
ADVERTISEMENT
Jun 19, 2007
searched all over, couldn't find a solid answer...is it possible to return multiple values from a sql function with sql server 2005?
e.g., I want to do this:
select id, data, whatever, dbo.fnMyFunction(id, whatever) from table
and have the output have columns (id, data, whatever, col1, col2) where col1 and col2 are returned by fnMyFunction
possible? easier way? thanks in advance...
View 4 Replies
View Related
Mar 20, 2014
I'm working on a query that is asking to return data on dependents which a person can have 0-many, in a single row but sep columns. The dependent data I need to include are Dep First Name, Dep Last Name, Dep Relationship.
So my result should look something like this:
EEID| DepFirstName| DepLastName| DepRelationship| DepFirstName| DepLastName| DepRelationship
121 Billy Larson Spouse Alison Larson Child
How do I do this with SQL?
View 1 Replies
View Related
May 5, 2004
Hi,
We Have Been Trying To Convert Some Pf The Procs Into Functions Of Late,but There Is A Problem :-we Have Been Unable To Return More Than 1 Table Value From A Function.
Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),
As
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Return
End
This Works Fine :-
And Gives The Reqd. Results.
But,
If I Am Using The Same Function To Return Two Tables Then It Doesn't Work,could You Pls Chk.
Create Function F_clusters()
Returns @ki Table(names Nvarchar(200),total Int),@k2 Table(names Nvarchar(200),total Int)
As
Begin
Declare @cnt Int
Set @cnt = 1
While @cnt <= 2
If @cnt =1
Begin
Insert @ki
Select Names,count(distinct Chremail) As From Customer
Where Chremail Is Not Null
Set @cnt = @cnt + 1
End
If @cnt =2
Begin
Insert @k2
Select @naamre,count(distinct(a.intcustomerid)) As Pura_ginti From Trcustomerpreference03july A Inner Join Cleancustomer B
On A.intcustomerid = B.intcustomerid
Where Chremail <> ' ' And Chremail Is Not Null
And Intpreferenceid In (6,7,2,3,12,10)
Set @cnt2 = @cnt2 + 1
End
End
Return
End
Can We Return Two Tables Or Is It Not Possible ?
Pls Chk Into This And Tell Me.
Thanks.
View 13 Replies
View Related
Feb 1, 2007
I have a table that contains 5 columns (VarChar); where column(0) is a unique ID. Using the unique ID I would like to get the other 4 columns return to me via a stored procedure. Is it possible to have a sproc that has one input var and 4 output?
View 4 Replies
View Related
Jun 22, 2007
How to return multiple values from stored procedures to reports in sql server 2005
View 5 Replies
View Related
May 9, 2014
Very basically, I need to return a result set based on another value and only if there are multiples of that other value.
Example. select * from mytable
Returns (--column separater)
John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4
So I want a query that would return only
John---1---1
John---1---1
John---3---3
John---3---3
The query cannot be something as simple as this:
Select * from mytable where John=1 or John=3
I have to many possible results to plan like that. I am thinking some kind of aggregate with a group.
View 9 Replies
View Related
Jul 23, 2005
Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.
View 4 Replies
View Related
Aug 19, 2015
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
View 17 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
Mar 30, 2007
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View 11 Replies
View Related
Aug 22, 2007
Hi,
I have multiple columns in a Single Table and i want to search values in different columns. My table structure is
col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))
I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1
ON
TBL.col1=TBL1.[key]
INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result = 1 row
But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4
FROM
TBL
INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON
TBL.col1=TBL1.[key]
Result=0 Row
Any idea how i can write second query to get result?
View 1 Replies
View Related
Jun 6, 2007
If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?
View 2 Replies
View Related
Sep 1, 2007
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
View 3 Replies
View Related
Mar 29, 2007
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View 1 Replies
View Related
Feb 14, 2001
How do you get the return value(in ASP) of @@identity in a stored procedure while inserting a row?
View 2 Replies
View Related
Jul 27, 2004
Using ado, the _CommandPtr::Execute method returns a recordset. When calling a storedproc with this method, does the 'return' statement return the value in a recordset ?
EG:
//c++ ADO
_CommandPtr pCmd(__uuidof(Command));
............
_RecordsetPtr pRs = pCmd->Execute(NULL, NULL, adCmdStoredProc);
// Stored proc
CREATE PROCEDURE usp_my_proc
AS
SELECT .... FROM tbMyTable WHERE x = something
IF(@@ERROR <> 0)
return -1
.....
What in the case of an error would be returned to pRs recordset?
Any thoughts?
Mike B
View 2 Replies
View Related
Jan 25, 2006
hello,
is it possible to return a return value as a numeric datatype
I can successfully return as an integer, but when i change it to numeric i get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]Invalid scale value
/output.asp, line 15
my output value in my asp code is thus
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4)
thanks
Afrika
View 1 Replies
View Related
Jan 25, 2006
hello,
is it possible to return a return value as a numeric datatype
I can successfully return as an integer, but when i change it to numeric i get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]Invalid scale value
/output.asp, line 15
my output value in my asp code is thus
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4)
thanks
Afrika
View 1 Replies
View Related
Feb 4, 2004
I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.
Here is a piece of my SP inside SQL Server that shows the returned values:
…
SELECT @Id = SCOPE_IDENTITY()
SELECT @Id AS user_id
SELECT 1 AS Value
END
GO
In my aspx page I am trying to call the first value like this:
Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
CmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@RETURN_VALUE").Value = nID
And to check if the right value is returned I use:
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = nID
strConnection.close()
But now no value appears in the textbox, How can I achieve it? What is wrong?
View 6 Replies
View Related
Apr 29, 2004
First question :
How can I set the return value of READTEXT to a variable of type nvarchar.
Second question :
I have a table t1 with a ntext column n1.
The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.
How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).
Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.
Any help on this is greatly appreciated.
Please do provide a sample code.
Alok.
View 1 Replies
View Related
Jan 9, 2014
I need to Return values with hierachy sum.
I have provided the tables with data and result expected below
============
CREATE TABLE
============
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
[Code] ....
The parent data will have either QA or QC
Child will have data in QY.
========================
EXPECTED RESULT
========================
Mode_Info |Mode_Detail| QA| QC|QY
Air||4 | |
Sea|||5|
SEA|SHIP|||15
SEA|BOAT|||25
ROAD|||2 |
ROAD|BUS|||20
ROAD|BIKE|||30
ROAD|CYCLE|||4
ROAD|TRAM|||10
ROAD|CAR|3||
ROAD|BMW|||36
ROAD|AUDI|||84
View 3 Replies
View Related
Apr 19, 2014
I am trying to return all the names of employees and their managers
this query returns all the employees except for 1
SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME
FROM EMPLOYEE E,EMPLOYEE M
WHERE E.SUPERSSN=M.SSN
the one that isn't returned has a null SUPERSSN, but when I add in:
OR E.SUPERSSN IS NULL
it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)
How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns?
View 1 Replies
View Related
Sep 15, 2006
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
View 3 Replies
View Related
Aug 22, 2007
Hi,
How do I ensure that DISTINCT values of r.GPositionID are returned from the below??
Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;
Thanks.
View 11 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Sep 27, 2004
Hi,
I have two tables. Both tables have a FirstName and a LastName field. I can return the results where (LastName = LastName) and (FirstName = FirstName). However in one table some of the FirstName values are shortened ie Pat is P etc.
Is there some way (its prob using LIKE but i can't get it right) I can write the below query so that it returns values where the first letter of FirstName in one table equals the first letter of FirstName in the second table.
SELECT tbl2.FirstName, tbl2.LastName, SALARY.[Employee No]
FROM SALARY INNER JOIN tbl1 ON (SALARY.LastName = tbl2.LastName) AND (SALARY.FirstName = tbl2.FirstName)
Cheers for all help.
View 2 Replies
View Related
May 23, 2007
I want the following query to return a row even when table 'X' is empty. How would I do this?
SELECT TOP 1 @Var1, @Var2, @Var3 from X
The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included.
When table is empty, it reurn nothing. It only return a row when table is not empty.
View 16 Replies
View Related
Dec 30, 2003
This is a portion of my stored procedure in T-SQL
CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)
AS
SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)
SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)
*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])
The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?
thx in advance...
View 3 Replies
View Related
Jun 4, 2004
Hello Group
I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page.
Thank you
Michael
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 ),
@fldFullName varchar( 75 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM [tbUsers]
Where fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
GO
code
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim strFullName As String
' Variable Declaration
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
' Set conn equal to the conn. string we setup in the web.config
sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
myConn = New SqlConnection(sqlConn)
' We are going to use the stored procedure setup earlier
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
' Set the default return parameter
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
' Add SQL Parameters
myCmd.Parameters.Add("@fldUsername", strUsername)
myCmd.Parameters.Add("@fldPassword", strPassword)
myCmd.Parameters.Add("@fldFullName", strFullName)
' Open SQL and Execute the query
' Then set intResult equal to the default return parameter
' Close the SQL connection
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
Session("strFullName") = strFullName
myConn.Close()
Response.Write(strFullName)
' If..then..else to check the userid.
' If the intResult is less than 0 then there is an error
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
' Return the userid
Return intResult
End Function
View 1 Replies
View Related
Jan 2, 2006
How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?
View 2 Replies
View Related
Dec 11, 2001
Can someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:
sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2
sp_calc_values receives @var1, @var2 then calculates @var3, @var4
HOW does sp_caller receive the calculated values @var3, @var4 ???
thx in advance
View 2 Replies
View Related
Mar 2, 2006
Greetings,
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END
Then I could call this function by doing something such as:
SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
Any help is very much appreciated.
- Jeff
View 3 Replies
View Related
