Hi, is it possible to combine Stored Procedure and Text Command Type in Select Statement?
I have a stored procedure that compute available items, it quite a bit long select statement,
now i need to call this statement in another select statement which is in text command type, how will i do it?
Select Barcode, ItemCode, Get_itemCount From Product
where Get_itemCount is my storedProc
Im using vb.net and Sql server 2008 R2
thanks in advance
Do it in two steps. First step, get return value fromt he stored procedure. Second step: format the select statement that includes the return value of the stored procedure. This means making two calls to the database, which can be somewhat time consuming.
Another option might be to create another stored procedure which does all that. The advantage is there is only one call to the database, not two.
Edited by Ancient Dragon
thanks for the idea however i haven't tried to do the first step, can you please show me example code returning the value of the stored proc then including it in single select statement and make it as a single query.
the second option I think would be not ideal since my query will be very long
I already called a store proc in sql select Statement and as far as I know we include a CommandType.StoredProcedure for it to return a value. how will I add this stored proc in single select statement which now has text as commandType?
I tried this codes but nothing works
Select Barcode, sp_Get_count From Products
Select Barcode, Exec Get_count @st = N'Value' From Products
How will I fix the query?
the usual error is near exec, near From, cannot find sp_Get_count stored Proc..
Thanks again for helping
Does your Stored procedure use an Output Parameter? (Example below:)
CREATE PROCEDURE GETVISITCOUNT @UserID bigint, @NoVisits int OUTPUT AS SELECT @NoVisits = COUNT(*) FROM Visits WHERE (UserID = @UserID) ...
Then in your code you would add the parameters but specify the NoVisits parameter is an output parameter:
Function GetNoOfVisits(byref UserID as integer, ByRef MyConn as SqlConnection) As integer dim Visits as Integer = 0 Try 'Set Up Command dim cmd as SqlCommand cmd.Connection = MyConn 'You are executing a stored Procedure cmd.CommandType = CommandType.StoredProcedure 'The Text will be the name of the Procedure cmd.CommandText = "GETVISITCOUNT" 'Add Parameters cmd.Parameters.Add("@UserID", SqlDBType.BigInt) cmd.Parameters.Add("@NoVisits", SqlDBType.Int) 'Specify Value of Input Parameter cmd.Parameters("@UserID").Value = UserID 'Specify direction of output parameter (default is input) cmd.Parameters("@NoVisits").Direction = ParameterDirection.Output 'Check Connection is open If MyConn.State <> ConnectionState.Open Then MyConn.Open() 'Open connection End if 'Our output parameter will be populated with the data we need so no dataset required cmd.ExecuteNonQuery() Visits = cmd.Parameters("@NoVisits").Value return Visits Catch return 0 Finally cmd.dispose() if MyConn.State <> ConnectionState.Closed then MyConn.Close() end if end try
You could then input this result into your next query as a parameter...
cmd2.Parameters.Add("@NoVisits", SqlDbType.Int) cmd2.Parameters("@NoVisits").Value = GetNoOfVisits(MyUserID)
thanks for the reply, I've tried your code, what is the MyUserID in
cmd2.Parameters("@NoVisits").Value = GetNoOfVisits(MyUserID)?
My data comes from different tables and I guess the function would not work. My required output should be
Code AvailFor Sale AvailStock
G1 25 50
G2 10 15
The code there came from table, and another table contains transactions for every code, like purchased, sold etc. To get the availFor Sales and AvailStock I need to query those transactions tables, like getting the remaining inventory and the query for AvailFor sales and AvailStock are different StoredProc and are quite long, to display all these results I need to create another query which I need to add the results of those storedProc for every code. How will I do this considering those codes comes from my sql table?
I based my answer on an example from some code I wrote not on your specifc code.
If you look at my code entries you will see that
GetNoOfVisits is a function that takes in a userID and returns the number of visits from the database using a stored procedure with an output parameter. I should have also passed in a sql connection to the function - my bad!
I'm then feeding the result of this function into another parameter on another query.
I've been trying to do your code, i cant make it til now. how will i feed the data from my select statement to the function,
this is my select statement
Select Code, Size, @sale, @stock From Products - orig select statement
where @sales and @stock should be the result from the functions where it contains the select statement for the stored procedure
How will i feed the code from my select statement to those functions when running it generates error that @sale and @stock is not supplied? (the storedproc also contains the 'Code' in its select statement)
I also tried putting a value to those functions manually, it outputs the result of the function's select statement (the storedproc), not the orig statement with the value of @sale and @stock.
OK lets take this slowly, to recap:
1. You want to return a value from a Stored Procedure
2. You want to assign this value to a variable that you then pass into another Stored Procedure or Select Statement
OR are we misunderstanding you?
Select Barcode, ItemCode, Get_itemCount From Product
You said Get_itemCount was the result of your first procedure can I ask what the select statement behind that procedure is? For instance, are you trying to do something like this get the barcode, itemcode and count of orders (or something similar,) from another table for each item?
If so you can do this with a select that uses a nested query:
SELECT itemcode, Barcode, ISNULL(Orders.Item_Count, 0) As Item_Count From Product LEFT OUTER JOIN ( SELECT COUNT(*) As Item_Count, itemcode FROM OrdersTable GROUP BY itemcode ) As Orders ON Product.itemCode = Orders.itemcode
Thank you for helping me solved this out
To clarify this,
I have a transaction Table which contains every transaction for every product including purchase, sales order, return, delivery, etc. ("Transaction" Table)
Now I need to check remaining product based from Transaction Table which is now in my first storedProc @Get_count
Select Code, (purchase - sales order + return - delivery) From Transaction Table (This if for every product type)
Where those inside the parenthesis are Joined Tables with different conditions mostly from transaction table also, which makes my statement long so I placed it inside storedProc
another storedProc named @Get_all
Select Code, (purchase - sales order + return - delivery) From Transaction Table (This if for every product regardless of type)
The same from the first but regardless with product type
Now on my final query I need to show product info with count of every product considering its type and count of it regardless of its type
Select Barcode, ItemCode, Size, Type, @Get_Count, @Get_all From Product
The barcode in this statement should the also the code for the storedProcs so that I can get the right counts, something like joining table in case i put this all queries in one statement
So the result should be
Barcode ItemCode Size Type @Get_count @Get_all
123 block 20x20 M 10 35
124 block 20x20 L 15 35
156 abc 10x10 T 15 15
Hopes this clarifies my problem
Please somebody help me on this
OK sorry, been busy at work.
Could you show me the code for the stored procedures?
I think you may have to modify them...
I think I have several ways of solving this but it really depends on how exactly you want this to work... which way suits best
Feed the results of each proceedure into each other (if you are only looking at one item at a time this could be the way to go)
If you have several items you want to display let SQL do the hard work and look at using views to generate the data.
If you have several items you want to view at a time another possibility is to use a dataset and datatables with relationships to display the data. Not recommended if you are bringing back whole tables though.
Something else I haven't though of yet...
Hi G waddell
Thanks for being supportive, for the ideas you gave, for sharing for precious time even your busy at work, appreciate it. I made some work arounds to accomplish this.
For those who will maybe encounter something related to this, this is how I did it
Alter Procedure Sales (@in nvarchar(100), @out nvarchar(100) OUTPUT)ASDeclare @result nvarchar(100)My Select Statement here to get CountReturen @resultEnd`
Then in my Vb, I've created a sub to call the sp then show the result with my original select statement.
Private sub Sales
cmd = new sqlcommand("Sales")
cmd.CommandType = CommandType.StoredProcedure
cmd.parameters("@out").Direction = ParameterDirection.Output 'Calling the sp'
Select Barcode, ItemCode, Size, Type From Product left join etc... 'My orig select Statement'
Using sqladapter to fill datatable
Datatable.Columns.Add("AvailSales").SetOrdinal(4) 'Adding column manually'
Then i looped through this datatable to get the barcode of each record
For i as integer= 0 to datatable.rows.count -1
cmd.Paramater("@in").Value = Datatable.rows(i).Item("Barcode")
Datatable.rows(i)("AvailSales") = cmd.Parameters("@out").Value
'Code for displaying result to datagrid'
If there's other better way to do this, that would be very helpful
Thanks again to G Waddell
Try this approach dude; in your Stored Procedure create temp tables (#Get_count and #Get_all)
CREATE PROC SP_NAME AS SELECT Code, (purchase - sales order + return - delivery) AS Count INTO #Get_count FROM Transaction Table (This if for every product type) SELECT Code, (purchase - sales order + return - delivery) AS All INTO #Get_all FROM Transaction Table (This if for every product regardless of type) SELECT Product.Barcode, Product.ItemCode, Product.Size, Product.Type, #Get_count.Count , #Get_all.All FROM Product INNER JOIN #Get_count ON Product.ItemCode = #Get_count.Code INNER JOIN #Get_all ON Product.ItemCode = #Get_all.Code DROP TABLE #Get_count DROP TABLE #Get_all
Please see my post vbScript - The Basics for more details on vbScript.
vbScript has a number of functions for converting from one ...
Im about to have my big brake and i'm gonna ask you about creating a music app like spotify? Do you know where to start from?