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 RepliesHow to return multiple values from stored procedures to reports in sql server 2005
View 5 Replies View RelatedUsing JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.
View 4 Replies View RelatedI 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?
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.
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...
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?
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.
Whenever I run this DTS package that has a SQLTask, I get the following error message: "Maximum stored procedure nesting level exceeded(Limit 32)"
What does this mean? My query was working fine, but now I'm recieving this error message. I won't complet my required task. Is there any way to change the nesting level to be unlimited. I am totally confused. If you can please help. Thanks again.
Dre
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.
I have the following stroed procedure and need someone to tell me what i am doing wrong. First off i am passing the value of sName from ASP to my sql stored procedure.
the following will work if i do (select distinct * )
Code:
SQL:
CREATE PROCEDURE Get_codes
@sName varchar(255)
AS
Select DISTINCT @sName
From CheckDetail
Where @sName is not Null and @sName <> ''
/*Order by @sName*/
GO
I am using a stored procedure to update data while in detailsview.I am having a rough time. Dont know why this wont workthanks a lotheres my codeALTER PROCEDURE UpdateCustomerInfo ( @CustomerID as INT, @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @OfficeType as varchar(50) = NULL, @Ext as Int=NULL, @BridgeExt as int=NULL, @Workstation as varchar(50)=NULL, @OldPrinters as varchar(50)=NULL, @NewPrinters as varchar(50)=NULL, @PhoneImg as varchar(50)=NULL, @OfficeImg as varchar(50)=NULL, @Complete as Bit=NULL, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, @ConfPhone as varchar(50)=NULL, @ConfExt as varchar(50)=NULL, @ReutersDacsId as Varchar(50)=NULL, @Bloomberg as Varchar(50)=NULL, @NewFloor as Int=NULL, @OldFloor as Int=NULL )AS UPDATE CustomerInfo SET LName = @LName, FName = @FName, Dept = @Dept, PhoneType = @PhoneType, Ext = @Ext, BridgeExt = @BridgeExt, Workstation = @Workstation, OldPrinters = @OldPrinters, NewPrinters = @NewPrinters, OfficeImg = @OfficeImg, PhoneImg = @PhoneImg, Complete = @Complete Where CustomerId = @CustomerId UPDATE Extras SET CustomerID = @CustomerID, FaxModel=@FaxModel, FaxNumber=@FaxNumber, ConfPhone=@ConfPhone, ConfExt=@ConfExt, ReutersDacsId=@ReutersDacsId, Bloomberg=@Bloomberg where CustomerID = @CustomerID RETURN
View 2 Replies View RelatedHi Sir,
I want to call one linked server stored procedure from another stored procedure.
--BATCH FOR LINKED SERVER SETUP
SET @IBatchNum = 2
EXEC PR_Linked_Server
@VServer = 'Linked_Amisys'
,@VSrvProduct = ''
,@VProvider = 'SQLOLEDB'
,@VDataSrc = 'DTCORPMXDV9'
,@VCatalog = 'pubs'
,@VCurrentUsr = 'CORPajohri'
,@VServerUsr = 'dba'
,@VServerPwd = 'dba'
END TRY
The linked server was not created When I executed the query. However, I was able to create the linked server when I executed this query(batch 2) alone.
Is there any limitation of SP which I am not aware of?
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
Hi,
I am trying to get the combination of results in my stored procedure, I am not getting what I need. Following are the things which I need to return from my stored proc.
1. I need to select distinct categories and their record count
2. I also need to select the records from the table.
3. Need to send both category, record counts and records.
First is it possible in stored procedure?
Following is helpful information.
Data in tables looks like this.
prod id, prod_name, prod_category
1, T shirts, Mens Apparel
2 , Shirts, Mens Apparel
3 , Pants , Mens Apparel
4, Tops , Women Wear
5, Bangles, Women Wear
And in User Interface I need to show like this.
Mens Apparel (3)
1 T Shirts
2 Shirts
3 Pants
Women Wear (2)
4 Tops
5 Bangles
Please help me if there is any way to return the complete data structure using stored procedure. If I do something in java code, I can get this, but I am trying to get directly from stored procedure only.
Thanks in advance...
Chandrasekhar
I have a quick question for you about SQL stored procedures. If I'min a stored procedure and want to call another stored procedure andreturn values from the second stored procedure what is the procedure?I know you do the following to run the second stored procedure and passin any parameters:EXEC GetAuthorBooks @AuthorIDSo if I wanted the GetAuthorBooks to return all the books for an authorand then populate a temp table in the original stored procedure, how doI return those records, and populate the temp table?What do I have to add to this line: EXEC GetAuthorBooks @AuthorID?
View 4 Replies View Related
Hi,
I have a requirement to get two count values from a stored procedure and use those values in other stored procedure.
How can I do that. I'm able to get only 1 value if i use the return key word.
Eg:
create proc test1 as
Begin
Declare scount int
Declare scount2 int
-- statements in stored procedure
return scount
return scount2
End
create proc test2
Declare variables...
Exec Test1
// here i want the values (scount and scount2 ), processed in stored procedure Test1 .
How can i get this.. please let me know..
Thanks,
srikanth
I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!
View 3 Replies View RelatedI came across an article in SQL Mag about Crosstab Queries. It worksgreat in Query Analyzer, but I'm stuck on how to use it in an AccessADP. I need to use it as a Recordsource in a form and report. Cansomeone tell me how to use it, and please try to be as descriptive aspossible. I'm new to Stored Procedures.Thanks*****************************************CREATE PROC sp_CrossTab@table AS sysname, -- Table to crosstab@onrows AS nvarchar(128), -- Grouping key values (on rows)@onrowsalias AS sysname = NULL, -- Alias for grouping column@oncols AS nvarchar(128), -- Destination columns (on columns)@sumcol AS sysname = NULL -- Data cellsASDECLARE@sql AS varchar(8000),@NEWLINE AS char(1)SET @NEWLINE = CHAR(10)-- step 1: beginning of SQL stringSET @sql ='SELECT' + @NEWLINE +' ' + @onrows +CASEWHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsaliasELSE ''ENDCREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)DECLARE @keyssql AS varchar(1000)SET @keyssql ='INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +'FROM ' + @tableEXEC (@keyssql)DECLARE @key AS nvarchar(100)SELECT @key = MIN(keyvalue) FROM #keysWHILE @key IS NOT NULLBEGINSET @sql = @sql + ',' + @NEWLINE +' SUM(CASE CAST(' + @oncols +' AS nvarchar(100))' + @NEWLINE +' WHEN N''' + @key +''' THEN ' + CASEWHEN @sumcol IS NULL THEN '1'ELSE @sumcolEND + @NEWLINE +' ELSE 0' + @NEWLINE +' END) AS c' + @keySELECT @key = MIN(keyvalue) FROM #keysWHERE keyvalue > @keyENDSET @sql = @sql + @NEWLINE +'FROM ' + @table + @NEWLINE +'GROUP BY ' + @onrows + @NEWLINE +'ORDER BY ' + @onrows-- PRINT @sql + @NEWLINE -- For debugEXEC (@sql)GO
View 6 Replies View RelatedIf I create a stored procedure and do not specify a return value or type, why does SSMS show that the stored procedure returns an int in the object explorer? Is that simply the success flag?
View 5 Replies View RelatedHi People, i'm having some trouble with a stored proceddure used for an updat from a Formview.
Error message i'm getting is Must declare the scalar variable "@CategoryID".
I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-)
Here is the whole store procedure.
ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update
@ID int,@CategoryID varchar(10),
@RegionID int,@CompanyName nvarchar(25),
@Email nvarchar(25),@PDFFront nvarchar(50),
@PDFBack nvarchar(50),@ThumbFront nvarchar(50),
@ThumbBack nvarchar(50),@Notes nvarchar(max)
AS
DECLARE @SQL varchar(1000)
SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '')
SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '')
SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '')
SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '')
SET @SQL = @SQL + ', ThumbBack = @ThumbBack'
SET @SQL = @SQL + ' WHERE (ID = @ID)'
Print '@SQL = ' + @SQLEXEC(@SQL)
RETURN
Can anyone let me know the prefered method for handling stored procedure errors. I usually trap the error in the stored proc and then return a value using an output parameter
e.g stored proc
if @@error <> 0 beginset @returnValue = -1returnend
c#com.Parameters.Add("@returnValue", SqlDbType.Int).Direction = ParameterDirection.Output;con.Open();com.ExecuteNonQuery();
int result = (int)com.Parameters[0].Value;
if (result == -1){//throw exception}else{//do whatever}
I need to check whether procedure found any matches or not. If not it has to return the column name where matching value was not found. For example, if there was no record found in the table "Addresses" column "customer" with the value @username, it should return "street". If id with value @prod_id was not found in the table "Products", the "productname" must be returned as well.
CREATE PROC sp_test
@id INT,
@username VARCHAR(50),
@prod_id INT
AS
SELECT name FROM Customers WHERE id=@id
SELECT street FROM Addresses WHERE customer=@username
SELECT productname FROM Products WHERE id=@prod_id
It is kind of check, which has to find out if users have inserted all the necessary values or not.
Thanks for any advice.
I am using sql 2005 beta
View 23 Replies View RelatedI have read a lot in favor and recommendation of returning multiple resultsets.
But now I have to implement it with a scenario.
I have a Parent Table named "Books" and a Child one named "Volumes".
A book can have multiple volumes.
Now I want to display the list of Books with their Volumes pagewise.
How can I implement that procdure.
hi
i read that stored procedures can return multiple result sets?how is that?
thanks in advance.
I am looking out for sample stored procedures returning multiple records
Example: GetOrderDetailsByOrderId
The above stored procedure should take orderId as parameter and should return the the order details along mutiple line item details.
Hi all,
I want to know how to handle multiple result sets return from Stored Procedure? I know one way is to insert the result sets into the table, but the limitation is the result sets must have the same data structure. If the result sets have different data structure, how can I handle it.
Thanks,
Hi all,
I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have:
ExecuteNonQuery(cmd);
return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower();
Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so:
return (int)ExecuteNonQuery(cmd);
Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help.
Thanks
my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column.
Is there a way to return the content of these two tables?
Is there a way to return a table from the stored procedure?
Thanks in advance
waamax
hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e)
{
try
{foreach (ListItem item in authorsListBox4.Items)
{if (item.Selected)
{
AddConfSqlDataSource.Insert();
}
}saveStatusLabel.Text = "Save Successfull: The above publication has been saved";
}catch (Exception ex)
{saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message;
}
}
I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:
CREATE PROC sp_test @in_company_code nvarchar(1024)
AS
select company_code, name, description
from member_company
where company_code in (@in_company_code)
However, I tried the following :
exec sp_test 'abc', 'rrd', 'bbc'
Procedure or function sp_test has too many arguments specified.
and SQLServer doesn't like it.
Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?
Thanks!
HiI'm not sure what the best approach for this is:I have a stored procedure which I would like to use to return severaloutput values instead of returning a recordset.CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 intOUTPUT) ASSELECT field2, field3 FROM Table WHERE field1 = @param1I would like to return @param2 as field2 and @param3 as field3How do I do this without using SELECT multiple times?THanks in advanceSam
View 6 Replies View Related